Help with simple CSV parsing

nilleso · 09-20-2010, 08:56 PM

Hello. I have a well formated source file that needs some simple parsing but I'm not sure of the best/easiest method.

example source,

Quote:

1,22,333,44,5555,6,77777777777777777777,8,9999,00000000000000,111111
1,22,333,44,5555,6,77777777777777777777,8,9999,00000000000000,111111
1,22,333,44,55555,6,77777777777777777,8,9999,0000000000000000,1111111
1,22,333,44,555,6,777777777777777777,8,9999,00000000000000,111111

I'd like print fields:

Quote:

$1,$2,$3,$4,$5,$7(but replace all characters after the first 8 with "X"),$9,$10(but only the first 5 characters,$11

really appreciate your help!!
thanks

dugan · 09-20-2010, 09:36 PM

It's easiest to script it in Python.

http://docs.python.org/library/csv.html

nilleso · 09-20-2010, 09:48 PM

I wish I knew python. Can you offer any example code to solve this scenario?
thanks

dugan · 09-21-2010, 08:41 PM

The link I posted includes example code.

kurumi · 09-21-2010, 09:16 PM

Code:

$ ruby -F"," -ane '$F.delete_at(5);$F[5][7..-1]=$F[5][7..-1].gsub(/./,"X");$F[8]=$F[8][0,5];puts $F.join(",")' file
1,22,333,44,5555,7777777XXXXXXXXXXXXX,8,9999,00000,111111
1,22,333,44,5555,7777777XXXXXXXXXXXXX,8,9999,00000,111111
1,22,333,44,55555,7777777XXXXXXXXXX,8,9999,00000,1111111
1,22,333,44,555,7777777XXXXXXXXXXX,8,9999,00000,111111

14moose · 09-21-2010, 09:44 PM

Q: which scripting language(s) do you currently feel comfortable with?

Bash? Perl? Something else entirely?

nilleso · 09-22-2010, 09:27 AM

@kurumi - thanks a lot. Unfortunately I do not have ruby installed on the target machine and do not have the option of installing. I will try to check out your example on an alternate machine though and consider moving the data files across. appreciate the example!!

nilleso · 09-22-2010, 09:32 AM

Hi 14moose
I can hack together a bash/sed/awk/tr script... but having difficulty with this scenario of:
1- print entire comma delimited lines, while
2- reducing number of characters on some of the fields, and
3- replacing some of the characters in specific fields with x's

appreciate any suggestions or alternatives!! thanks

Kenhelm · 09-22-2010, 10:52 AM

Using GNU sed:

Code:

echo '
1,22,333,44,5555,6,77777777777777777777,8,9999,00000000000000,111111
1,22,333,44,5555,6,77777777777777777777,8,9999,00000000000000,111111
1,22,333,44,55555,6,77777777777777777,8,9999,0000000000000000,1111111
1,22,333,44,555,6,777777777777777777,8,9999,00000000000000,111111' |
sed -r 's/^(([^,]*,){5})[^,]*,([^,]*,)[^,]*,/\1\3/
s/^(([^,]*,){7}[^,]{5})[^,]*/\1/
:a s/^(([^,]*,){5}[^,]{8}X*)[^X,]/\1X/; ta'

1,22,333,44,5555,77777777XXXXXXXXXXXX,9999,00000,111111
1,22,333,44,5555,77777777XXXXXXXXXXXX,9999,00000,111111
1,22,333,44,55555,77777777XXXXXXXXX,9999,00000,1111111
1,22,333,44,555,77777777XXXXXXXXXX,9999,00000,111111

The sed lines:
1. Remove $6 and $8.
2. Shorten $10 (now $8) to 5 characters.
3. Replace all characters after the first 8 in $7 (now $6) with "X".

ghostdog74 · 09-22-2010, 06:48 PM

Quote:

Originally Posted by nilleso

but I'm not sure of the best/easiest method.

if you have access to CSV parsers like those Python/Perl/Ruby provides, then try to use them. They can parser csv files easily and can take care of issues like embedded commas etc. If you don't have access to such languages or download stuff, your best bet (more appropriate) would normal *nix tools like awk/gawk and shell programming (sed excluded ,not because it can't do the job, but more because you will end up a messy chunk of unreadable regex).

Code:

#!/bin/bash

awk -F"," '{
 $6=$8=""
 a=substr($7,7)
 gsub(/./,"X",a)
 $7=substr($7,0,7) a
 $10=substr($10,0,5)
}1' OFS="," file

nilleso · 09-22-2010, 11:23 PM

Thank you everyone for the great suggestions. ghostdog74's awk construct works well but I plan to check out the alternatives also.

Thanks again
cheers