Convert fields in CSV to multiple rows retaining first colum

Get help with installation and running here.

Moderators: DataMystic Support, Moderators

Moz
Posts: 11
Joined: Sun Oct 09, 2005 8:15 pm

Convert fields in CSV to multiple rows retaining first colum

Postby Moz » Tue Aug 07, 2007 11:22 pm

Hi,

I have data like this:

43,12,13,27,31,54,76
44,43,152,
46,
47,84,128,151,190

The first column is ID1 and the remainders are each ID2 values for that one ID1. I want to change the data so it's in the following format:
ID1,ID2

e.g. the example data above would be:
43,12
43,13
43,27
43,31
43,54
43,76
44,43
44,152
46,
47,84
47,128
47,151
47,190

Any ideas how I can do this?

Many thanks for any help,

Moz

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Postby DataMystic Support » Wed Aug 08, 2007 12:39 pm

Easy - you need to capture the data to a global, and then repeat it on each line. Drop us an email and we can send you the filter.
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Postby DataMystic Support » Thu Aug 09, 2007 9:18 am

Emal sent. Here is the filter:

Restrict to each line in turn
|
|--EasyPattern [[ linestart, capture( longest 1+ digits) ],] with [$1]
| | [ ] Match case
| | [ ] Whole words only
| | [ ] Case sensitive replace
| | [ ] Prompt on replace
| | [ ] Skip prompt if identical
| | [ ] First only
| | [ ] Extract matches
| | Maximum text buffer size 4096
| |
| |--Capture to variable @recid
| |
| +--Remove all
|
|--EasyPattern [,] with [\r\n]
| [ ] Match case
| [ ] Whole words only
| [ ] Case sensitive replace
| [ ] Prompt on replace
| [ ] Skip prompt if identical
| [ ] First only
| [ ] Extract matches
| Maximum text buffer size 4096
|
+--Add left margin [@recid,]
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments


Return to “TextPipe Tips and Tricks, Questions and Support”

Who is online

Users browsing this forum: No registered users and 1 guest