17

I am trying to move 7th column of my csv file to the end by using

awk -F '{print $1,$2,$3,$4,$5,$6,$8,$9,$10,$11,$7}',OFS= "$file"

where $file is a .csv file in a directory. However, the output is

awk:                          ^ syntax error

Does anyone know how to fix this error?

1
  • 7
    When showing awk errors, you need to show the entire thing. The ^ indicates the specific part of the command where the error was encountered.
    – terdon
    Jul 5 '16 at 13:19
14

The -F option needs an argument (field separator): -F, for example.

The end of the awk script must be separated with a (space char) with the rest of the parameters.

If the field separator is , and you wish to keep it, and if the number of column is constant and lower than or equal to 11, give a try to this:

awk -F, '{print $1,$2,$3,$4,$5,$6,$8,$9,$10,$11,$7}' OFS=, "$file"

If your field separator is a semicolon don't forget to set it in quotes like so

awk -f';' '{print $1,$2,$3,$4,$5,$6,$8,$9,$10,$11,$7}' OFS=';' "$file"
3
  • 8
    @anuribs very few programs allow that. The standard way is command file > newfile && mv newfile file. That said, newer version of GNU awk to support this: gawk -i inplace '{blah blah}' file.
    – terdon
    Jul 5 '16 at 13:51
  • 1
    alternatively, instead of mv newfile file you can use cat newfile > file ; rm -f newfile - this preserves the inode and permissions of file.
    – cas
    Jul 6 '16 at 1:15
  • and it's generally a good idea to use mktemp rather than hard-coding temporary filenames into scripts. e.g. tf=$(mktemp) ; command file > "$tf" ; cat "$tf" > file ; rm -f "$tf"
    – cas
    Jul 6 '16 at 1:17
9

Shorter solution would be

awk -F',+' -v OFS=, '{$(NF+1)=$7; $7=""; $0=$0; $1=$1}1' file

I'm not sure if ,+ will work in all awk versions, but works at least in GNU awk, also with -compatibility mode.

Explanation:

  • $(NF+1)=$7: first we add 7th field to the end of the line (could be $12=$7 in this case)
  • $7="": in next step 7th field is erased (but surrounding delimiters stay)
  • to remove delimiters we need to re-set whole record (via $0=$0) treating multiple commas as field separator (this is done via -F',+', here + means one or more times), and also rearrange current record via $1=$1 to force rebuilding the line using previously set output field separator (set by an option -v OFS=,)
  • after all shuffling is done we are ready to print the result with 1

Example input:

1,2,3,4,5,6,7,8,9,10,11

output

1,2,3,4,5,6,8,9,10,11,7
3
  • What if other columns are blank? But, yes, FS is a regular expression in POSIX (if it is multiple characters), so ,+ should work.
    – Random832
    Jul 5 '16 at 16:30
  • (1) I understand that making the seventh column of input data “disappear”, and not just set it to null, is a tricky part of this problem.  But, as Random832 says, your solution clobbers blank columns (for example, all,ball,call,,,fallall,ball,call,fall).  (2) $(NF+1)=$7 is a clever approach.  IMHO, $0 = $0 OFS $7 is a little clearer, only a couple of characters longer, and it seems to do the same thing.  Can you think of a situation in which $0 = $0 OFS $7 doesn’t do the same as your code? Jul 5 '16 at 19:46
  • @Random832 @G-Man yes, some edge cases like blank fields, blank lines or NF<7 should be treated separately or one should rearrange the code. This is just an idea, not "complete solution" for all general cases, that should be clear. $0=$0 OFS $7 is probably identical to $(NF+1)=$7, but only with the rest of the code unchanged, not in general.
    – jimmij
    Jul 5 '16 at 20:31
5

If you're printing with OFS=, so with no separator between the fields, you can simply save the value of $7 in a variable, set $7 to empty and print the line and the variable directly. You don't need to specify all the fields:

$ cat file
1,2,3,4,5,6,7,8
$ awk -F, -vOFS= '{k=$7; $7=""; print $0,k}' file 
12345687
3

You probably mean:

awk -F, -v OFS='' '{print $1,$2,$3,$4,$5,$6,$8,$9,$10,$11,$7}' "$file"
3
  • You know that awk never sees the single quotes in OFS='', right? You may as well just type OFS=; it's exactly the same.
    – Wildcard
    Jul 5 '16 at 23:34
  • 1
    Yes, I realize that. However, I dislike dangling assignments. Jul 6 '16 at 4:37
  • @anuribs See stackoverflow.com/questions/16529716/…
    – Barmar
    Jul 6 '16 at 17:32
3

You did not specifically say you wanted to use awk, and you did say you wanted to use in-place editing like provided by sed -i, so here is a sed -i variant. Usually awk is better for working with columns, but this is one case where I prefer sed, because it naturally handles arbitrary numbers of columns.

MOVECOL=7
N=$((MOVECOL-1))
sed -r -e "s/^(([^,]*,){$N})([^,]*),(.*)/\1\4,\3/" -i test.csv

Explanation:

  • -r selects extended regexps so we avoid lots of backslashes
  • first group is $N repetitions of comma-terminated strings, in other words the columns before the one we want to move, with a final comma
  • second group is the $N-th repeat, we forget about it
  • third group is the column we want to move, without the final comma
  • fourth group is composed of all the columns after the one we want to move, with no comma before
  • we replace with first group, last group, and the column we extracted, inserting the comma as needed.

Of course this will not work with files that hide commas in quotes (or worse, escape them), but awk won't handle that either without some serious acrobatics. If you have that problem you'd be better off with perl module Text:CSV or the python module csv.

0
2

A couple of awk variants (assuming your file is inside the variable $file)

  • Here you can cycle for all the coloumn, print with the field separator (OFS), and print the record terminator (ORS) at the end of the line.

    awk  -F',' -v OFS=,                                \
    '{for(i=1;i<=NF;i++) if (i!=7) printf "%s",$i OFS; \
    printf "%s",$7;printf ORS}' "$file"
    
  • Here with using a regex and the gensub() function

    gawk -F',+' -v OFS=, '{$0=gensub(/\s*\S+/,"",7) OFS $7}1' "$file"
    

    killing the 7th field and printing it at the end of the line.

    • $0 is the whole record
    • $n is the nth record
    • NF is the Number of Fields of the current line
    • OFS the output filed separator
    • ORS the output record terminator
    • 1 is the trick to say to awk true and print the default ($0).

Update...

I almost forget, it's possible to shift all the columns following the 7th one.

awk  -F',' -v OFS=, '{tmp=$7; for(i=7;i<=NF;i++) $i=$(i+1); $NF=tmp}1 ' "$file"
2
  • (1) Arguably, OFS $7 would be more robust than "," $7. (2) I believe that ", " $7 is wrong, insofar as the question indicates that the OP doesn’t want spaces after the commas.  (And, if the input data had spaces after the commas, then $7 would already begin with a space, and you’d be adding an extra one.) Jul 5 '16 at 19:49
  • @G-Man It was mainly to propose some ideas, some variants. Thanks, for the spot, I agree about OFS $7, not only more robust, but even more general ( "haste makes waste")
    – Hastur
    Jul 5 '16 at 21:44

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.