Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

How can I do the following to a CSV file using sed or awk?

  • Delete a column
  • Duplicate a column
  • Move a column

I have a big table with over 200 rows, and I'm not that familiar with sed.

share|improve this question
1  
Cross posted on AskUbuntu –  enzotib Dec 16 '11 at 6:37
    
@enzotib can you post the link? –  maxmackie Dec 16 '11 at 7:01
    
@MaxMackie askubuntu.com/questions/88142/…. I can't get ahold of a mod there at this hour, so I flagged it asking them to migrate if they're willing; it already has an accepted answer so I'm not sure if they will –  Michael Mrozek Dec 16 '11 at 7:10
    
@MichaelMrozek, hmmm what usually happens in these situations? Do we simply keep the duplicates? –  maxmackie Dec 16 '11 at 7:11
1  
Unless you need to run on a system that only has basic tools available, see Is there a robust command line tool for processing csv files? –  Gilles Dec 18 '11 at 0:40

4 Answers 4

up vote 5 down vote accepted

Aside from how to cut and re-arrange the fields (covered in the other answers), there is the issue of quirky CSV fields.

If your data falls into this "quirky" category, a bit of pre and post filtering can take care of it. The filters shown below require the characters \x01,\x02,\x03,\x04 to not appear anywhere in your data.

Here are the filters wrapped around a simple awk field dump.

Note: field-five has an invalid/incomplete "quoted field" layout, but it is benign at the end of a row (depending on the CSV parser). But, of course, it would cause problematic unexpedted results if it were to be swapped away from its current end-of-row position.

Update; user121196 has pointed out a bug when a comma precedes a trailing quote. Here is the fix.

The data

cat <<'EOF' >file
field one,"fie,ld,two",field"three","field,\",four","field,five
"15111 N. Hayden Rd., Ste 160,",""
EOF

The code

sed -r 's/^/,/; s/\\"/\x01/g; s/,"([^"]*)"/,\x02\1\x03/g; s/,"/,\x02/; :MC; s/\x02([^\x03]*),([^\x03]*)/\x02\1\x04\2/g; tMC; s/^,// ' file |
  awk -F, '{ for(i=1; i<=NF; i++) printf "%s\n", $i; print NL}' |
    sed -r 's/\x01/\\"/g; s/(\x02|\x03)/"/g; s/\x04/,/g' 

The output:

field one
"fie,ld,two"
field"three"
"field,\",four"
"field,five

"15111 N. Hayden Rd., Ste 160,"
""

Here is the pre filter, expanded with comments.
The post filter is just a reversal of \x01.\x02,\x03,\x04

sed -r '
    s/^/,/                # add a leading comma delimiter
    s/\\"/\x01/g          # obfuscate escaped quotation-mark (\")
    s/,"([^"]*)"/,\x02\1\x03/g    # obfuscate quotation-marks
    s/,"/,\x02/           # when no trailing quote on last field  
    :MC                   # obfuscate commas embedded in quotes
    s/\x02([^\x03]*),([^\x03]*)/\x02\1\x04\2/g
    tMC
    s/^,//                # remove spurious leading delimiter
'
share|improve this answer
    
how would you delete the nth column based on this filter? –  user121196 Dec 3 '12 at 10:50
    
@user121196 - As mentioned in its opening sentence, this answer shows a way to make the CSV data more consistent.. eg. by termporarily replacing a quote-embedded comma with a neutral token character... and then reverting it back into a comma after the move/cut/delete. Again, as mentioned, the move/cut/delete step is replaced by a simple awk field-dump. –  Peter.O Dec 3 '12 at 11:39
1  
it fails for this case: "15111 N. Hayden Rd., Ste 160,","" –  user121196 Dec 4 '12 at 9:22
    
@user121196: Thanks for pointing that out. I have updated the answer with a fix. –  Peter.O Dec 4 '12 at 15:30

This depends on whether your CSV file uses commas only for delimiters, or if you have madness like:

field one,"field,two",field three

This assumes you're using a simple CSV file:

Removing a column

You can get rid of a single column many ways; I used column 2 as an example. The easiest way is probably to use cut, which lets you specify a delimiter -d and which fields you want to print -f; this tells it to split on commas and output field 1, and fields 3 through the end:

$ cut -d, -f1,3- /path/to/your/file

If you actually need to use sed, you can write a regular expression that matches the first n-1 fields, the nth field, and the rest, and skip outputting the nth (here n is 2, so the first group is matched 1 time: \{1\}):

$ sed 's/\(\([^,]\+,\)\{1\}\)[^,]\+,\(.*\)/\1\3/' /path/to/your/file

There are a number of ways to do this in awk, none of them particularly elegant. You can use a for loop, but dealing with the trailing comma is a pain; ignoring that it'd be something like:

$ awk -F, '{for(i=1; i<=NF; i++) if(i != 2) printf "%s,", $i; print NL}' /path/to/your/file

I find it easier to output field 1 and then use substr to pull off everything after field 2:

$ awk -F, '{print $1 "," substr($0, length($1)+length($2)+3)}' /path/to/your/file

This is annoying for columns further along though

Duplicating a column

In sed this is essentially the same expression as before, but you also capture the target column and include that group multiple times in the replacement:

$ sed 's/\(\([^,]\+,\)\{1\}\)\([^,]\+,\)\(.*\)/\1\3\3\4/' /path/to/your/file

In awk the for loop way it'd be something like (again ignoring the trailing comma):

$ awk -F, '{
for(i=1; i<=NF; i++) {
    if(i == 2) printf "%s,", $i;
    printf "%s,", $i
}
print NL
}' /path/to/your/file

The substr way:

$ awk -F, '{print $1 "," $2 "," substr($0, length($1)+2)}' /path/to/your/file

(tcdyl came up with a better method in his answer)

Moving a column

I think the sed solution follows naturally from the others, but it starts to get ridiculously long

share|improve this answer
    
That's a loaded answer! +1 :) –  jaypal singh Dec 16 '11 at 4:12
    
Ridiculously long? Pah! –  Gilles Dec 18 '11 at 0:41

awk is your best bet. awk prints fields by number, so...

awk 'BEGIN { FS=","; OFS=","; } {print $1,$2,$3}' file

To remove a column, not print it:

 awk 'BEGIN { FS=","; OFS=","; } {print $1,$3}' file

To change the order:

awk 'BEGIN { FS=","; OFS=","; } {print $3,$1,$2}' file

Re-direct to an output file.

awk 'BEGIN { FS=","; OFS=","; } {print $3,$1,$2}' file > output.file

awk can format the output as well.

Awk format output

share|improve this answer
    
Since it's CSV, you'll also need BEGIN { FS=","; OFS=","; }. –  Jeremy Kerr Dec 16 '11 at 2:13
    
Thank you Jeremy, I added in the syntax –  bodhi.zazen Dec 16 '11 at 2:17
    
I think even FS = OFS = "," will work. –  deshmukh Dec 16 '11 at 6:36

Given a space-delimited file in the following format:

1 2 3 4 5

You can remove field 2 with awk like so:

awk '{ sub($2,""); print}' file

which returns

1  3 4 5

Replace column 2 with column n where appropriate.

To duplicate column 2,

awk '{ col = $2 " " $2; $2 = col; print }' file

which returns

1 2 2 3 4 5

To switch column 2 and 3,

awk '{temp = $2; $2 = $3; $3 = temp; print}'

which returns

1 3 2 4 5

awk is generally very good at dealing with the concept of fields. If you're dealing with a CSV, and not a space-delimited file, you can simply use

awk -F,

to define your field as a comma, instead of a space (which is the default). There are a number of good awk resources online, one of which I list as a source below.

Source for #3

share|improve this answer
    
I don't know much about awk, but it seems to output space-separated even if the field separator is , (the field-separator just controls how it handles input) –  Michael Mrozek Dec 16 '11 at 2:59
    
@MichaelMrozek: yes, it is OFS awk variable that controls output field separator. –  enzotib Dec 16 '11 at 8:22
    
Yes, and as I mention in my answer, you can pass the -F option to awk to change the delimiter (e.g. -F,) –  tcdyl Dec 16 '11 at 20:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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