Marco Marco -4 years ago 133
Bash Question

CSV: awk change column value

I try to parse a csv file and need to change some values for further processing.

The csv file contains a date col (column number 4), which contains values like this:
2016032100
2016032318
etc.

I want to change all date values in the csv file from
2016032102 to this:
2016-03-21 02:00:00

I tried the following:

echo "$(awk -F';' -v OFS=';' '$4=${4:0:4}-${4:4:2}-${4:6:2} ${4:8:2}:00:00"' $FILE)" > $FILE


But this of course does not work.

I also tried to put the conversion into a function, but this also does not work.

Do you have any idea?

Answer Source

Never do:

echo "$(command $FILE)" > $FILE

for any value of "command" as the shell could cheerfully empty $FILE before it calls "command" to read it. Do this instead:

command "$file" > tmp && mv tmp "$file"

I also quoted the variable and changed it from upper to lower case as both things are also important (google it or read any shell book).

Now back to your specific question, this is what your code seems to be trying to do:

awk 'BEGIN{FS=OFS=";"} {$4=substr($4,1,4)"-"substr($4,5,2)"-"substr($4,7,2)" "substr($4,9,2)":00:00"} 1' "$file"

Whether or not that does what you want will depend on the rest of the contents of your CSV which we haven't seen yet but which isn't actually a Comma-Separated-Value (CSV) file.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download