[9fans] csv files -> embarrasing

[9fans] csv files -> embarrasing

[Steve Simon]

Ok, I have spent half an hour trying to parse CSV files
and it's getting embarrasing, I could do it in C but I should
be able to use rc + sed + awk.

The problem is that some of my CSV files fields contain whitespace
and thus have double quotes around them.

I thought rc knows about %q quotes strings so I could use it to
do my parsing, but it fails, can this be done, or is C the answer?
seems a shame to resort to sledge hammers.

-Steve

cpu% cat file.csv
a,b,"c,d,e",f,g
p,q,r,s,t

cpu%
cpu% cat extract
#!/bin/rc

sed 's/"([^"]*)"/''\1''/g; s/,/ /g' $* |
	while (s=`{read})
		echo $s(1) $s(3) $s(4)


cpu% extract file.csv
a 'c d
p r s

Re: [9fans] csv files -> embarrasing

[quanstro]

i had this problem years ago processing original yahoo categories.
i think the solution is to use regexps to do a "lifting". first
translate the commas within "..." to, say, "☺".  then do your csv
operation and finally translate the "☺" back to ",".

this is totally untested, but something like this:

# should be f* and f*' 

fn fstar {
	{
	 	echo 'X ,x:"[^"]*": s:,:☺:g'
	  	 echo ,p
	} | sam -d $*
}

fn fstartick {
	{
	 	echo 'X ,x:"[^"]*": s:☺:,:g'
	  	 echo ,p
	} | sam -d $*
}

for(i in $files)
	fstar $i | extract | fstartick /fd/0

- erik

Re: [9fans] csv files -> embarrasing

[Russ Cox]

You can change CSV to tab separated in sam/acme with

,y/[^,]+|"[^"]+"/ s/,/[tab]/g

Replace [tab] with an actual tab.

Russ

Re: [9fans] csv files -> embarrasing

[lucio]

> Ok, I have spent half an hour trying to parse CSV files
> and it's getting embarrasing, I could do it in C but I should
> be able to use rc + sed + awk.

A reliable CSV reader would be very useful.  I discovered that CSV can
be awkward, but I didn't think that was enough justification for never
having found any such tool.

Is parsing CSV really so difficult?

++L

Re: [9fans] csv files -> embarrasing

[matt]

 > Is parsing CSV really so difficult?

depends on your CSV

1, "2", "3""3","4, 4", "5\",5"

which I interpret as (\n separated) :
1
2
3"3
4, 4
5",5


If you've ever worked with Excel CSV you know what CSV hell is like

Re: [9fans] csv files -> embarrasing

[Victor Nazarov]

Steve Simon wrote:

>Ok, I have spent half an hour trying to parse CSV files
>and it's getting embarrasing, I could do it in C but I should
>be able to use rc + sed + awk.
>
>The problem is that some of my CSV files fields contain whitespace
>and thus have double quotes around them.
>
>I thought rc knows about %q quotes strings so I could use it to
>do my parsing, but it fails, can this be done, or is C the answer?
>seems a shame to resort to sledge hammers.
>
>-Steve
>
>cpu% cat file.csv
>a,b,"c,d,e",f,g
>p,q,r,s,t
>
>cpu%
>cpu% cat extract
>#!/bin/rc
>
>sed 's/"([^"]*)"/''\1''/g; s/,/ /g' $* |
>	while (s=`{read})
>		echo $s(1) $s(3) $s(4)
>
>
>cpu% extract file.csv
>a 'c d
>p r s
>
>
>  
>
Thought about this case today. In native Plan9 the solution is quite easy.
Programs share environment and this is the answer:

sed 's/"([^"]*)"/''\1''/g; s/,/ /g' $* |
	while (s=`{read}) {
		echo 's=('$"s')' | rc
		echo $s(1) $s(3) $s(4)
        }

Re: [9fans] csv files -> embarrasing

[rog]

> sed 's/"([^"]*)"/''\1''/g; s/,/ /g' $* |
> 	while (s=`{read}) {
> 		echo 's=('$"s')' | rc
> 		echo $s(1) $s(3) $s(4)
>	}

unfortunately this doesn't work, for quite a few reasons.
1) the sed script doesn't deal with quoted double-quotes.
2) nor does it deal with single quotes.
2) the `{read} idiom tokenizes s, ignoring multiple spaces, so
that information is lost when putting them back together with $"s
3) the environment might be shared, but rc caches environment
variables, so the value of $s when passed to the second
echo is the same as that before the rc invocation.

i've also encountered newlines in values in csv files,
which won't help matters.
assuming that there aren't any newlines, an approximation
to a solution might be:

sed -e 's/''/''''/g' -e 's/"(([^"]|"")*)"/''\1''/g' -e 's/""/"/g' -e 's/.*/s=(&)/' |
	ifs='' while(e=`{read}){
		eval $e
		echo $s(1) $s(3) $s(4)
	}

i really don't like using eval in this way though. if you get it wrong,
you've got a nasty loophole.

Re: [9fans] csv files -> embarrasing

[Rogelio Serrano]

On 4/28/06, Steve Simon <steve@quintile.net> wrote:
> Ok, I have spent half an hour trying to parse CSV files
> and it's getting embarrasing, I could do it in C but I should
> be able to use rc + sed + awk.
>
> The problem is that some of my CSV files fields contain whitespace
> and thus have double quotes around them.
>
> I thought rc knows about %q quotes strings so I could use it to
> do my parsing, but it fails, can this be done, or is C the answer?
> seems a shame to resort to sledge hammers.
>
> -Steve
>
> cpu% cat file.csv
> a,b,"c,d,e",f,g
> p,q,r,s,t
>
> cpu%
> cpu% cat extract
> #!/bin/rc
>
> sed 's/"([^"]*)"/''\1''/g; s/,/ /g' $* |
>         while (s=`{read})
>                 echo $s(1) $s(3) $s(4)
>
>
> cpu% extract file.csv
> a 'c d
> p r s
>
>

I deal with csv files everyday at work and i just use custom plain c
parsers. Reinventing the wheel and all.

-- 
www.smsglobal.net SMS Global Ltd Short Message Service For Seafarers

Re: [9fans] csv files -> embarrasing

[erik quanstrom]

ooks slightly more painful than the previous case, but i think that
using my original trick, one can continue to add elements to the
lifing pipeline and move character-stuffed " and \" out of the way, too.

and didn't i learn that trick well from parsing data from yahoo categories.

- erik

On Sun Apr 30 05:38:13 CDT 2006, mattmobile@proweb.co.uk wrote:
>  > Is parsing CSV really so difficult?
>
> depends on your CSV
>
> 1, "2", "3""3","4, 4", "5\",5"
>
> which I interpret as (\n separated) :
> 1
> 2
> 3"3
> 4, 4
> 5",5
>
>
> If you've ever worked with Excel CSV you know what CSV hell is like