Scripting to split a single CSV row into multiple

Question

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()

Answer 1

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

Answer 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

Answer 3

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

Answer 4

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

Answer 5

#!/usr/bin/perl -n

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

Answer 6

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