parse a line of data to multiple rows

[mintz87]

how do i parse out the data inside the double quotes so it will result in seperate rows of data? here are 3 rows od data for example. 1st row does not have double quotes so there is not anything to do but the next 2 rows of data does.

Data:
Alyeska (Trans Alaska Pipeline),2,1,AK
Basin,3,1,"NM, TX, OK"
Buckeye,14,2,"IL, IN, MI, OH, PA, NJ, NY, CT, MA"

Final Results:
Alyeska (Trans Alaska Pipeline),2,1,AK
Basin,3,1,NM
Basin,3,1,TX
Basin,3,1,OK
Buckeye,14,2,IL
Buckeye,14,2,IN
Buckeye,14,2,MI
Buckeye,14,2,OH
Buckeye,14,2,PA
Buckeye,14,2,NJ
Buckeye,14,2,NY
Buckeye,14,2,CT
Buckeye,14,2,MA

thanks

[ben_josephs]

Something like this, perhaps:

Find what: ^(([^,]*,){3})"?([^,]*), *([^"]*)"?$
Replace with: \1\3\n\1\4

[X] Regular expression

Replace All -- do this repeatedly until it beeps

This assumes you are using Posix regular expression syntax:

Configure | Preferences | Editor

[X] Use POSIX regular expression syntax

[mintz87]

thank you for your response.

i get an error: Cannot find regular expression.

i changed the option to use Posix and regular expression is checked.

also could you decipher the expression for me? i'm having a problem understanding it so i can trouble shoot why it's not working for me.

thanks

[ben_josephs]

Is your data exactly as you reported it? Is there any white space at the beginnings or ends of your lines? Did a space get added to the end of the regular expression when you copied it from this page?

[mintz87]

here is another sample, no spaces at the beginning or end of the line. but when i highlight the data there is an extra space at the end of each line, possible hidden carriage return??

Alpine,1,1,AK
Alyeska (Trans Alaska Pipeline),2,1,AK
Basin,3,1,"NM,TX,OK"
Buckeye,14,2,"IL,IN,MI,OH,PA,NJ,NY,CT,MA"
Calnev,15,2,"CA,AZ,NV"
Capline,4,1,"LA,MS,TN,KY,IL"


thanks

[ben_josephs]

TextPad searches treat all line ending character sequences as equal.

My suggestion changes your example to

Code: Select all

Alpine,1,1,AK
Alyeska (Trans Alaska Pipeline),2,1,AK
Basin,3,1,NM
Basin,3,1,TX
Basin,3,1,OK
Buckeye,14,2,IL
Buckeye,14,2,IN
Buckeye,14,2,MI
Buckeye,14,2,OH
Buckeye,14,2,PA
Buckeye,14,2,NJ
Buckeye,14,2,NY
Buckeye,14,2,CT
Buckeye,14,2,MA
Calnev,15,2,CA
Calnev,15,2,AZ
Calnev,15,2,NV
Capline,4,1,LA
Capline,4,1,MS
Capline,4,1,TN
Capline,4,1,KY
Capline,4,1,IL

Please try again, following the instructions carefully, and ensuring there is no space at the end of the search expression.

[mintz87]

i opened a new textpad and copied the data into it and everything worked. not sure why the other textpad would not work.

Thanks again!!

[mintz87]

could you explain out what this is doing? i went thru the help and forums but i'm still a little lost.

Find what: ^(([^,]*,){3})"?([^,]*), *([^"]*)"?$
Replace with: \1\3\n\1\4

thanks in advance

[ben_josephs]

Does it still not work for you? It should. Check that your text is precisely as you say it is and that you've followed the instructions precisely as given.

^(([^,]*,){3})"?([^,]*), *([^"]*)"?$ matches

Code: Select all

^           the beginning of a line

(           start of captured text number 1
  (         start of captured text number 2 (but we don't use it)
    [^,]*   any text on a line not containing commas (see below)
    ,       a comma
  )         end of captured text number 2
  {3}       ... 3 times
)           end of captured text number 1

"?          an optional double quote

(           start of captured text number 3
  [^,]*     any text on a line not containing commas (see below)
)           end of captured text number 3
,           a comma
 *          [there's a space before the *] a (possibly empty) sequence of spaces

(           start of captured text number 4
  [^"]*     any text on a line not containing double quotes
)           end of captured text number 4

"?          an optional double quote

$           the end of a line

where [^,]* matches:

Code: Select all

[^,]        any character except newline or comma
*           ... any (possibly zero) number of times

We stick the matched text back together, adjusted as required:
\1\3\n\1\4 is composed of:

Code: Select all

\1    captured text number 1
\3    captured text number 3
\n    a newline
\1    captured text number 1
\4    captured text number 4

[mintz87]

it's still a little confusing, but i will look at it some more.

it did work after i opened a new textpad and copited the data into it.

thanks again