CSV: awk change column value

Question

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?

This is important - awk is not shell so do not expect shell syntax to work in an awk script any more than you'd expect shell syntax to work in a C or Java (or any other language) program. edit your question to show concise, testable sample input and expected output so we can help you. — Ed Morton
– Ed Morton, Commented Jun 12, 2017 at 19:56

Ed Morton · Accepted Answer · 2017-06-12 20:04:18Z

2

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.

answered Jun 12, 2017 at 20:04

Ed Morton

209k18 gold badges90 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zhe Chen · Accepted Answer · 2017-06-12 20:06:55Z

-1

you can use the python csv module to read the file, datatime module to convert

import datetime
pythonTime = datetime.datetime.strptime("2016032100", "%Y%m%d%H")
print pythonTime.strftime("%Y-%m-%d %H:%M:%S")

answered Jun 12, 2017 at 20:06

Zhe Chen

142 bronze badges

1 Comment

Marco Over a year ago

Sorry, but I only can use shell. But Ed Mortons solution works perfect.

Collectives™ on Stack Overflow

CSV: awk change column value

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related