Link to home
Start Free TrialLog in
Avatar of weinrj
weinrj

asked on

using tr or sed to make csv

I got a file that was created on a mac so it has \r (CR) on each line, and two CRs after all the data ends.  To be more clear, each column to be in the csv is seperated by the CR and each row is seperated by two CRs.  Right now i am using tr w/o success.  tr '\r' ',' < input > ouput.  but that will still et comma at end and i cant handle the two carriage returns.

this is sample data:
12/31/2004
user@domain.com
how google.com
name John Smith
phone 212-555-1234
fax 212-555-4321
company Foobar Enterprises
address1 123  Main St.
address2
city New York
state NY
zip 10001
country USA
email user@domain.com
download
literature Yes
call Yes
notes I like to know more about the product

i guess a small bash script would be fruitful but i am awful at regexp :-(

thanks so much!
Avatar of weinrj
weinrj

ASKER

note that some fields may not have output.
and since this was generated from a webform (no access to php/mysql, yet), i plan on putting this data into access.
so, the field names need to bve converted into the first row and then removed alltogether (awk $0, $1 i suppose)

so that the field names wont be in the actual data, and i can tell access to use the first row as field names.

thanks so much,
Avatar of DonConsolio
cat /data/file.mac | sed 's/\r\r/\n/g' | sed 's/\r/|/g' >/data/file.csv
cat mltest2 | sed 's/\r\r/\n/g' | sed 's/\r/,/g'
Avatar of weinrj

ASKER

okay this put commas in it, but each row still doesnt get processed.

each column is on its on line, so it needs to be pushed back up to its own row,

each row is after two CRs so they need to go on its own line, so the rows are seperated by rows, not a blank line.  this should fix my problem.  thanks
no idea what you want to tell with your comment.
"each row still doesnt get processed" ?
"rows are seperated by rows" ?

please re-explain or give an example
Avatar of weinrj

ASKER

this is sample data:

12/31/2004<CR>
user@domain.com<CR>
how google.com<CR>
name John Smith<CR>
phone 212-555-1234<CR>
fax 212-555-4321<CR>
company Foobar Enterprises<CR>
address1 123  Main St.<CR>
address2<CR>
city New York<CR>
state NY<CR>
zip 10001<CR>
country USA<CR>
email user@domain.com<CR>
download<CR>
literature Yes<CR>
call Yes<CR>
notes I like to know more about the product<CR><CR>
1/1/2005<CR>
user@domain.com<CR>
how yahooe.com<CR>
name Jane Smith<CR>
phone 212-555-4321<CR>
fax 212-555-1234<CR>
company Foobar Enterprises<CR>
address1 123  Main St.<CR>
address2 Apt 3C<CR>
city New York<CR>
state NY<CR>
zip 10001<CR>
country USA<CR>
email user@domain.com<CR>
download Yes<CR>
literature No<CR>
call Yes<CR>
notes I like to know more about the product<CR><CR>
Avatar of weinrj

ASKER

esentiually output should be like
12/31/2004,user@domain.com,how google.com,..,notes i want more info
1/5/2005,user@domain.co.uk,..,notes need more info
....
ASKER CERTIFIED SOLUTION
Avatar of DonConsolio
DonConsolio
Flag of Austria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of weinrj

ASKER

at least in vim, i get each column as a row still now with comma at end.

well perhaps my original data is corrupted, it seems like this does what i need anywasy...thnx