I have a *.csv file that has some text in the following format:

a, b, c 
d, e, f
g, h, i

And I would like to convert this into an *.xls file with three columns. Is it possible to do it with "sed"?

Edit: This is just an example. What I'm interested in is replacing a symbol with a column shift (if this is not impossible). Next, I want to manipulate a text that looks like this:

A single column


to two column format

a     [kg/m]

I'm not sure it is possible, though! But I know that "sed" can do a lot of tricks to replace and modify *.csv files

  • 1
    Do your fields have commas inside them? Like foo, "foo, bar", 42 – blissini Nov 17 '16 at 19:00
  • It is a plain text with comma in between. The *.csv extension was experimental. – Physther Nov 17 '16 at 19:02
  • 2
    Why you don't open the CSV with excel and then save it as XLS? – blissini Nov 17 '16 at 19:04
  • Just did that. What I have now is a one-column format with text separated by comma in each row. a, b, c in one column d, e, f in the same column etc. – Physther Nov 17 '16 at 19:06
  • Which version of Excel are you running? – blissini Nov 17 '16 at 19:07

I don't know if understand correctly the scond part of your request:

A single column a[kg/m] to two column format a [kg/m]

But this will convert your csv into a tab separated file and with the extra column before [Kg/m]:

sed -e 's/,/\t/g' -e 's:\(\[Kg/m\]\):\t\1:g' afile.csv > newfile.csv


$ cat afile.csv

$ sed -e 's/,/\t/g' -e 's=\(\[Kg/m\]\)=\t\1=g' afile.csv > newfile.csv

$ cat newfile.csv
a       [Kg/m]  b       [Kg/m]  c
d       [Kg/m]  e       [Kg/m]  f
g       [Kg/m]  h       [Kg/m]  i

There are two scripts in this sed command:

  • -e 's/,/\t/g' replaces ',' into <tab>
  • -e 's=\(\[Kg/m\]\)=\t\1=g' replaces any [Kg/m] for <tab>[Kg/m]

Now, without replacing into tabs, you can add an extra column to your csv file with this:

$ sed -e 's=\(\[Kg/m\]\)=,\1=g' afile.csv > newfile.csv
$ cat newfile.csv
  • That's exactly what I wanted! Thank you so much! – Physther Nov 18 '16 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.