7

In order to be able to import some data into a certain tool, I have to transform a CSV file from this format

"data","data","data data","data","123"

into this format

data;data;data data;data;123

The columns never contain any ", ; or , but there can be spaces. Currently I use the following

sed -e 's/","/;/g' -e 's/"//g' input.csv > output.csv

Although this works fine I wonder if this can be done more elegantly, i.e.

  • Is sed the right (standard Unix) tool for the job?
  • Would it be possible to merge both expressions into one?

Thanks for your input!

3 Answers 3

6
( tr , ';' | tr -d '"' ) < input.csv > output.csv

I'd use Perl

perl -pe 'tr/,"/;/d' input.csv > output.csv

-- but this specific task isn't beyond sed. You cannot merge the two expressions.

3
  • Thanks for your answer, IMHO two nice solutions. Could you explain the . in the one using tr? It is not the same as [:punct:], right? man tr does not help me. It seems to be a bit of a matter of taste which answer is the best. If the authors of the other answers do not object, I'll set this as the accepted answer, because it looks very elegant to me and the community rated it the highest so far.
    – middus
    Oct 11, 2009 at 10:35
  • i don't mind. i'm partial to the perl version myself. perl's tr rocks. Oct 11, 2009 at 10:56
  • Sorry -- that should be ,
    – ayrnieu
    Oct 11, 2009 at 20:48
5

Which you prefer (perl, sed, awk) is up to you; they'll all get the job done. Since you asked for sed, and the others are posted, here ya go. This is a simpler form of your regex's and works with your example line:

$ sed -e 's/"//g; s/,/;/g' infile.csv > outfile.csv

Note you can join the two expressions with a semicolon after each substitution. Tested with GNU sed v4.1.5.

Here's your original expressions joined:

$ sed -e 's/","/;/g; s/"//g' infile.csv > outfile.csv

I'm reasonably sure it's possible to merge the two substitutions. Not sure what it would be offhand, and I'm pretty sure the result will be much less readable than the script at the top. If I come up with something (or someone else weighs in in the comments) I'll add it here.

4
  • "You can join the two substitions" - you, you can't. You've taken two expressions and replaced them two expressions.
    – ayrnieu
    Oct 10, 2009 at 18:00
  • his original was '-e "foo" -e "bar"', i joined them into '-e "foo; bar"'. the -e is the expression i'm referring to, and assumed he was referring to. you might be right -- i've misinterpreted what he's asking for -- but you are also misreading my statement. Oct 10, 2009 at 18:13
  • clarified. i hope. :) Oct 10, 2009 at 18:20
  • That's cool, I did not know that you could just join expressions like that. Thanks for your answer!
    – middus
    Oct 11, 2009 at 10:16
4

Since you're dealing with records, awk makes more sense. That said, it's not really good at CSV, since the field delimiters are somewhat variable. But if you're certain that all fields are surrounded by doublequotes, this will work:

awk -F'","' 'BEGIN {OFS=";"} { gsub(/(^")|("$)/, ""); $1=$1; print }'

This sets awk's input field separator to "","" (including the inner set of doublequotes). This almost works, except you have to deal with the leading and trailing doublequotes, which are stripped with the gsub function. The $1=$1 forces it to recompile the record with the new output field separator, which was defined as ; in the BEGIN block. Then print prints out the whole record.

This is a little tidier:

awk -F '(^")|(",")|("$)' 'BEGIN {OFS=";"} { $1=$1; print }'

It sets the input field separator to a regular expression that includes the doublequotes at the beginning and end of the record, but it also causes it to print out an empty beginning and trailing field. You can easily get rid of the trailing field:

awk -F '(^")|(",")|("$)' 'BEGIN {OFS=";"} { NF=NF-1; $1=$1; print }'

NF is the number of fields, and reducing it by one lops off the last field. But I can't think of a way to chop off the first field.

If you know that the input always has five fields, though, you could do this:

awk -F '(^")|(",")|("$)' 'BEGIN {OFS=";"} { print $2,$3,$4,$5,$6 }'

Notice this gets rid of the $1=$1 construct, which we only need if we're printing the (implied) $0.

All that said, I'd probably end up using perl and one of the many available CSV modules on CPAN.

3
  • Okay, this looks a bit more complex than the other solutions and is not too readable. If I came across this in one year's time I probably would have to wonder what it does. However, it's nice to see that several different tools (awk, sed...) are suitable for the task. Thanks for your detailed answer. I'll take it as an entry point to look into awk for other problems.
    – middus
    Oct 11, 2009 at 10:22
  • it looks worse than it is. once you start learning a little awk it gets easier to read. :) Oct 11, 2009 at 13:27
  • It's more complex because it's smarter, trying to deal with records as records instead of with strings that happen to look like CSV, as strings. This suffers from much less of a 'complexity wall' - a point where a small addition to the problem description yields an enormous change in the solution (e.g., throwing the entire solution away and coming up with another from scratch.)
    – ayrnieu
    Oct 11, 2009 at 20:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .