2

I need a script that can take a CSV file with that has a column of semicolon-delimited attributes, and break it into multiple rows to normalize that multi-valued attribute? Here is the scenario:

Current:

John,Doe,"Foo1;Bar1;Foo2;Bar2"

Target:

John,Doe,Foo1 
John,Doe,Bar1 
John,Doe,Foo2 
John,Doe,Bar2

Edit (Answered): Avinash's answer was easy, sufficient and, as I noted in the comments, only required a few changes. The following update (apologize in advance for my poor scripting), updated Avinash's answer to work with Python 2.4 and accommodate N number of columns. Disclaimer: this will still only work if the last column is the multi-valued one, but a few loops and if/elses could resolve that if anyone wanted.

#!/usr/bin/python3
import csv
import sys
fil = sys.argv[1]
f = open(fil)
try:
    reader = csv.reader(f)
    for i in reader:
        l = []
        for x in i:
            if ';'  in x:
                m = x.split(';')
                l.append(m)
                splitCol = len(l)-1
            else:
                l.append(x)
        for j in l[splitCol]:
            strng = ''
            for colCount in range(len(l)):
                if colCount != splitCol and colCount == 0:
                     strng = strng+''.join(i[colCount])
                elif colCount != splitCol and colCount != 0:
                     strng = strng+','+''.join(i[colCount])
                elif colCount == splitCol and colCount == 0:
                     strng = strng+j
                else:
                     strng = strng+','+j
            print(strng)
finally:
    f.close()
  • Are all the rows that simple (no embedded , or newlines in quoted fields?) – Anthon Feb 12 '15 at 6:38
  • The rows unfortunately are not that simple, but I was really just looking for a quick boilerplate template so I could get past the initial syntax learning curve. In my full scenario there are embedded commas, the multi-valued column can be anywhere and theoretically there could be multiple multi-valued columns (though I will work to prevent that outside of this). This is actually a quick/dirty workaround for what should be eventually be an ETL tool, so it need not be fancy. – ps2005 Feb 16 '15 at 15:25
  • The accepted answer (there are similar solutions in Perl and Ruby) is a good choice then. I have seen too often that during production the CSV data was not as simple as expected (or as the sample) and such libraries handle that transparently for you. – Anthon Feb 16 '15 at 18:42
2

Through python3.

#!/usr/bin/python3
import csv
import sys
fil = sys.argv[1]
with open(fil) as f:
    reader = csv.reader(f)
    for i in reader:
        l = []
        for x in i:
            if ';'  in x:
                m = x.split(';')
                l.append(m)
            else:
                l.append(x)

        for j in l[2]:
            print(l[0]+','+l[1]+','+j)

Save the above script in a file and name it as script.py. Run this script by,

python3 script.py inputfile
  • Thank you! This worked well. I had to make some changes because I was using Python 2.4 and I added a few changes to accommodate n number of columns. – ps2005 Feb 16 '15 at 14:46
2

If you want something quick'n'dirty, and know that your data has exactly three fields as shown, then maybe something like

awk -F, -vOFS=, '{split(substr($3,2,length($3)-2),a,";"); for (i in a) print $1,$2,a[i];}' file.csv
1

One more pythonic form

#!/usr/bin/env python3

string= 'John,Doe,"Foo1;Bar1;Foo2;Bar2"'
result = [ string.split('"')[0] + x for x in string.split('"')[1].split(";")]
for i in result:
    print(i)

Output:

$ ./test.py 
John,Doe,Foo1
John,Doe,Bar1
John,Doe,Foo2
John,Doe,Bar2
  • This won't work if any of the quoted CSV fields contain escaped double quotes ("") – Anthon Feb 15 '15 at 11:17
0

Here is some sed you can use to do it:

sed 'H; 1h; g; x; s/^\n//
/^\([^"]*\)"\([^"]*".*\)/!  d
        s//\1/; x; s//\2/; :t
/\n\{0,1\}\([^;,"]*\)[";]\(.*\)/{ H
        s//\2/; x; s//\1/p
        s/\(.*,\).*/\1/; x
        /^[^,"]*,\(.*\)/!bt
};s//\1/;x;s///;G;x;s///;x;D

I know - it looks long. But - for so long as sed's memory buffers don't give way under pressure - this should handle any kind of text within your fields given a few (as I think) realistic caveats:

  1. The "quoted portions cannot contain escaped quotes
  2. The last field must be a "quoted field and "quotes must evenly pair.
  3. The ;delimited fields cannot contain commas, quotes, or ;semicolons
  4. There must be at least one comma-separated field leading each "quoted portion.

Here's an example:

sed ... <<\INPUT
John,Doe,"Foo1;Ba

r1;Foo2;Bar2",Jane,Doe,"Foo3;Bar3;Foo4;Bar4",All,Work,And,

No,Play,Makes,Jack,"A Superhero;
A

Very

Dull

Boy"
INPUT

If you fill in the ... above with the sed script at the top of this answer you should get the following output:

John,Doe,Foo1
John,Doe,Ba

r1
John,Doe,Foo2
John,Doe,Bar2
Jane,Doe,Foo3
Jane,Doe,Bar3
Jane,Doe,Foo4
Jane,Doe,Bar4
All,Work,And,

No,Play,Makes,Jack,A Superhero
All,Work,And,

No,Play,Makes,Jack,
A

Very

Dull

Boy
0
#!/usr/bin/perl -n

if(/(.*?,)"(.*?;*?)"(.*)/){ my ($a,$b,$c)=($1,$2,$3) ;
       for( split(/\s*;\s*/,$b )){
          print "$a$_$c\n"
       }
}
0

Here is another solution using Python 2:

#!/usr/bin/env python2
input_string = 'John,Doe,"Foo1;Bar1;Foo2;Bar2"'
input_list = input_string.split('"')
common_input = input_list[0]
changed_input = input_list[1].split(';')
for i in changed_input:
    print common_input + i

Output:

John,Doe,Foo1
John,Doe,Bar1
John,Doe,Foo2
John,Doe,Bar2
  • This doesn't work if the quoted field contains any escaped quotes ("") – Anthon Feb 15 '15 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.