12

I have a CSV delimited by commas and I want to delimit it by newlines instead.

Input:

a, b, c

Output:

a
b
c

I've written Java parsers that do this stuff, but couldn't this be done with vim or some other tool?

sed isn't working for me:

#!/bin/sh

# Start
cat > infile.csv << __EOF__
a, b, c
__EOF__
cat infile.csv
sed 's/, /\n/g' infile.csv > outfile.csv

cat outfile.csv
1
  • 2
    This script works as expected on my Ubuntu system
    – user591
    Mar 19, 2011 at 18:49

5 Answers 5

21

Seems like the other answers achieve what you want, and a scriptable tool seems the most appropriate choice.

But you asked about vim, so here's how you do it there:

%s/, /\r/g

That is, replace every comma+space with a carriage return. This will then be interpreted as the appropriate line ending character for the file. (You can check this by searching for \r -- it won't be found).

8

Similar to Iain's answer, you can also use tr:

$ echo a,b,c | tr ',' '\n'
a
b
c

Both answers assume that the CSV is simple (that is, all commas are field separators). If you have something like a,"b,c",d where b,c is a single field, then things get more difficult

6
  • @Mrozek: how do I use tr with the input and output file?
    – simpatico
    Mar 19, 2011 at 17:55
  • @simpatico cat input | tr ',' '\n' > output Mar 19, 2011 at 18:40
  • 1
    tr ',' '\n' < input > output avoids the UUOC.
    – kojiro
    Mar 19, 2011 at 23:33
  • 3
    @kojiro Yes, but I do not and never will care about that :). I even do cat foo | grep bar; I suspect the UUOC fanatics burn me in effigy every night Mar 19, 2011 at 23:51
  • 1
    @fred Nobody's arguing that avoiding the extra command isn't beneficial, it's whether or not you actually care. If I'm writing a script that's going to be used by lots of other people, I'll make an effort to do it right. If I'm running a command that's going to cat a 50 line file one time and pass it to another process, I really don't care how many cats are involved. The 2 seconds I could've saved by skipping the cat are voided by the 10 seconds it takes me to check that command's man page to remember if it takes a filename argument and what switch it is Mar 20, 2011 at 18:17
2

The use of \n in a s replacement text in sed is allowed, but not mandated, by POSIX. GNU sed does it, but there are implementations that output \n literally.

You can use any POSIX-compliant awk. Set the input field separator FS to a regular expression and the output field separator ORS to a string (with the usual backslash escapes). The assignment $1=$ is needed to rebuild the line to use the different field separator.

awk -vFS=', *' -vOFS='\n' '{$1=$1; print}'

(This assumes that your input contains plain comma-and-whitespace-separated values, without any quoting. If there is quoting, you need to move to a real CSV parser in a language such as Perl or Python.)

2

If your file is delimited by ', ' (commas followed by space) then

sed 's/, /\n/g' filename.csv >newfile

will do the job. If its delimited by ',' (commas without spaces) then

sed 's/,/\n/g' filename.csv >newfile

will work.

or change the \n to \o12 if your flavour of sed doesn't like it.

5
  • this replaced it with space and not new lines. Output: a b c
    – simpatico
    Mar 19, 2011 at 14:52
  • @simpatico: Ok - I 'see' the problem now, your example output wasn't formatted correctly.
    – user591
    Mar 19, 2011 at 15:01
  • @lain: this doens't work with \n . Did you try it? the \ disappears while the n is appended. a, b --> an b
    – simpatico
    Mar 19, 2011 at 17:57
  • @simpatico: I did test it and it works ok.
    – user591
    Mar 19, 2011 at 18:04
  • @Iain GNU sed and sed (some of which are strictly POSIX-compliant) need not necessarily be the same...And I am pretty sure the functionality you tested is guaranteed on GNU sed only. With the others, it may - or may not - work. Like playing poker. :-) May 9, 2015 at 2:26
0

Using Raku (formerly known as Perl_6)

perl6 -pe 's:g/ "," \s /\n/;' 

#OR

perl6 -ne '.split(", ").join("\n").put;'  

Also, if you're concerned about embedded newlines, commas-within-quotes, etc., use Raku's Text::CSV module:

raku -MText::CSV -e '.join("\n").put for csv(in => lines, sep => ", ");'

Sample Input:

a, b, c

Sample Output (all code above):

a
b
c

https://unix.stackexchange.com/a/701805/227738
https://raku.org

You must log in to answer this question.

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