Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 784
  • Last Modified:

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!
0
weinrj
Asked:
weinrj
  • 5
  • 4
1 Solution
 
weinrjAuthor Commented:
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,
0
 
DonConsolioCommented:
cat /data/file.mac | sed 's/\r\r/\n/g' | sed 's/\r/|/g' >/data/file.csv
0
 
DonConsolioCommented:
cat mltest2 | sed 's/\r\r/\n/g' | sed 's/\r/,/g'
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
weinrjAuthor Commented:
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
0
 
DonConsolioCommented:
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
0
 
weinrjAuthor Commented:
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>
0
 
weinrjAuthor Commented:
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
....
0
 
DonConsolioCommented:
cat macfile.txt | sed 's/\r\r/\n/g' | sed 's/\r/,/g' >linuxfile.txt

the first sed replaces any two consecutive <CR> characters by a newline <\n>
now the rows are OK

the second sed replaces any remaining <CR> by a "," character
now columns are OK

rows ==> lines with newline at the end
columns ==> col1, col2, col3, col4, ...

what's the problem with the output file?
to me that looks exactly like the output you asked for.
0
 
weinrjAuthor Commented:
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now