Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

making a file a true csv file

making a file a true csv file

making a file a true csv file

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.

thanks soooo much, Tabitha

RE: making a file a true csv file

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.

tgmlify - code syntax highlighting for your tek-tips posts

RE: making a file a true csv file

Thank you


RE: making a file a true csv file

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

RE: making a file a true csv file

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close