making a file a true csv file 1

[atjurhs]

Hi guys, I need a little help....

I have a file with columns of data that has varied amounts of spaces and comas in between each column of data. Some of the columns contain no data, just separated by multiple spaces and comas. Other columns do have data that may of may not be separated by multiple spaces or comas.

here's an example

1, 4242, 3.42323e+23, 0.1, 0, 0,5,294875, 8438393, 394,,,,,,,,
,0, ,0,,,, 0.487564, , ,0, 0,0, 87563,,,,,,,,, , 0 ,
,1, ,,,,,,,,,,,,,,,, 0, , , , 5,
,1, ,,,,,,5241,,,,, , , 0.4543e-3 , 0 , 111111111,
1, 1000,,,, 9576336e+10, 0.1, 0, 0, ,,, , 8438393, 001,

if I (by hand) delete all the spaces, and replace all occurrences of multiple comas (two or more) with a single coma, then all the columns line up correctly (I haven't gone through and counted up the spaces and comas in the above example) and I have row column data that looks like:

rc11,rc12,rc13,rc14,rc15, etc.
rc21,rc22,rc23,rc24,rc25, etc.
rc31,rc32,rc33,rc34,rc35, etc.
etc.
etc.
etc.

thanks soooo much, Tabitha

 

[Annihilannic]

This would do the equivalent of what you are doing by hand:

sed 's/ //g;s/,,*/,/g' inputfile.csv >outputfile.csv

The first search-and-replace replaces any space with nothing; the second one replaces a comma followed by any number of commas with a single comma.

Annihilannic
[small]tgmlify - code syntax highlighting for your tek-tips posts[/small]

 

[atjurhs]

Thank you

Tabitha!

 

[atjurhs]

yep, it's actually very easy in sed, I should have thought of that

 

[atjurhs]

in the outputfile that I get, some of the lines begin with a , and some don't. obviouswly this makes manipulating the data by columns tricky. so I added another sed search-and-replace statement that would delete a coma if it was at the beginning of a line:

 sed 's/ //g;s/,,*/,/g;s/^,//g' inputfile.csv > outputfile.csv

and that worked perfectly! note, use a carrot in the third statement, using a \ will delete all comas which doesn't make for a csv file.

Tabitha