[SOLVED] AWK / SED - Parsing a CSV file with comma delimiter, and some extra needs.

PenguinJr · 05-24-2011, 11:38 AM

Hi,

I'm requiring your help for a problem that got me busy for almost the whole day : I have an input file in CSV format with comma delimiters :

Quote:

A,1,John,Carpenter,27,,"Carpenter, John",+169457,,BDG
A,1,James,Hill,61,SRR,"Hill, James",,,"FFB, BFB, NSO"
A,4,Eva,Brown,31,,"Brown, Eva",000916,D'Aboville & D'Aboville,SNX

And here is what I'm trying to get :

Quote:

'A','1','John','Carpenter','27','','Carpenter, John','+169457','','BDG'
'A','1','James','Hill','61','SRR','Hill, James','','','FFB, BFB, NSO'
'A','4','Eva','Brown','31','','Brown, Eva','000916','D''Aboville & D''Aboville','SNX'

In other words, I'm trying to apply the following rules :
1) All fields separated by a comma (including blank ones) must be simple-quoted, unless this (these) comma(s) belongs to a double-quoted field.
2) All simple-quotes contained within a field must be doubled.
3) If a field is already encapsulated in double-quotes, replace them by simple quotes.

My main problem is that I don't know how to code in AWK or SED the fact to ignore comma(s) within double-quotes when parsing the fields. I'm pretty sure that once this step is done, I can do the rest.

Many thanks,
PenguinJr

Tinkster · 05-24-2011, 01:10 PM

While parsing CSV can be done in awk, it's not straight forward
or trivial; I'd recommend using perl, python or some language that
has modules built for that very purpose.

Cheers,
Tink

MTK358 · 05-24-2011, 01:17 PM

I also thought that it might be a better idea to use a "full" programming language for this, but I didn't want to take this thread off the zero reply list.

Code:

print "'"
for every character in the line
    is it a comma?
        print "','"
    or is it a double quote?
        skip forward until the next double quote, printing each character.
    or is it a single quote?
        print "''"
    it's neither of the above
        print the current character
print "'"

And by the way, they're "single" quotes, not "simple" quotes.

crts · 05-24-2011, 01:26 PM

Hi,

this worked with your sample data. Not sure if there is a better, shorter way to do it in awk.

Code:

#!/usr/bin/gawk -f
BEGIN { FS="," }
{	i=0
	c=","
	while(i++<NF) {
		if (i==NF) { c="" }
		gsub("'","''",$i)
		if ($i~/^"/) {
			sub("\"","'",$i)
			a=!a
		}
		if ($i~/"$/) {
			sub("\"","'",$i)
			a=!a
			printf "%s%s",$i,c
			continue
		}
		if (!a) {
			printf "'%s'%s",$i,c
			continue
		}			
		printf "%s%s",$i,c
	}
	printf "\n"
}

And the result:

Code:

$ cat file
A,1,John,Carpenter,27,,"Carpenter, John",+169457,,BDG
A,1,James,Hill,61,SRR,"Hill, James",,,"FFB, BFB, NSO"
A,4,Eva,Brown,31,,"Brown, Eva",000916,D'Aboville & D'Aboville,SNX 
$ 
$ ./awk.scr file
'A','1','John','Carpenter','27','','Carpenter, John','+169457','','BDG'
'A','1','James','Hill','61','SRR','Hill, James','','','FFB, BFB, NSO'
'A','4','Eva','Brown','31','','Brown, Eva','000916','D''Aboville & D''Aboville','SNX '
$

catkin · 05-24-2011, 01:57 PM

There's an awk script to parse a CSV file that might give some ideas in this LQ post.

PenguinJr · 05-24-2011, 02:53 PM

Thank you for all these accurate and fast answers, I'm already in the awk manual trying to figure out how crts script is working, and also checking catkin's link.
Regards,
PenguinJr

sundialsvcs · 05-24-2011, 03:01 PM

Feel free to do it the hard way, but that's what Perl is for. The Linux/Unix environments give you an extremely rich toolkit: use it well.

Birei · 05-24-2011, 05:37 PM

Hi,

Using 'Perl':

Code:

$ cat script.pl
use strict;
use warnings;
use autodie;

@ARGV == 1 or die "Usage: perl $0 input-file\n";
open my $fh, "<", $ARGV[0];
my (@f, $i);

while ( <$fh> ) {
	chomp;
	@f = split /,/;
	for ( 0 .. $#f ) {
		next unless exists $f[$_];
		$i = $_;
		if ( index( $f[$i], qw(") ) != -1 ) {
			while ( ++$i <= $#f and index( $f[$i], qw(") ) == -1 ) {
				$f[$_] = join ",", $f[$_], splice @f, $i, 1; 
			}
			$f[$_] = join ",", $f[$_], splice @f, ( $i > $#f ? $#f : $i ), 1; 
		}
		$f[$_] =~ s/'/$& x 2/ge;
		$f[$_] =~ tr/"//d;
		$_ = $i;
	}

	print +(join ",", map( { s/.*/'$&'/; $_ } @f) ), "\n";
}
close $fh;
$ perl script.pl infile
'A','1','John','Carpenter','27','','Carpenter, John','+169457','','BDG'
'A','1','James','Hill','61','SRR','Hill, James','','','FFB, BFB, NSO'
'A','4','Eva','Brown','31','','Brown, Eva','000916','D''Aboville & D''Aboville','SNX '

Regards,
Birei

atavus1 · 05-24-2011, 06:28 PM