2

I need some help on date conversion in-place on a CSV. Basically, I was capable to convert the column and save in a new file, but I was unable to save it on the original CSV.

I have a CSV with date format DD/MM/YYYY HH:MM and I want to convert to YYYY-MM-DD HH:MM, it's the first column of my CSV.

My CSV file has something like that:

29/01/2018 14:07,payable,37159871,,30521316
29/01/2018 14:07,payable,37159872,,30521316
29/01/2018 14:07,payable,37159870,,30521316
29/01/2018 14:07,payable,37159869,,30521316
29/01/2018 14:07,payable,37159868,,30521316

And I want to have something like that:

2018-01-29 14:07,payable,37159871,,30521316
2018-01-29 14:07,payable,37159872,,30521316
2018-01-29 14:07,payable,37159870,,30521316
2018-01-29 14:07,payable,37159869,,30521316
2018-01-29 14:07,payable,37159868,,30521316

What I was capable to do:

gawk -F, '{split($1, a, "/| "); print a[3]"-"a[2]"-"a[1]" "a[4]}' /path/to/file.csv > test_file

So now I want to know how can I save this back on my CSV file.

Max Forasteiro
  • 123

4 Answers4

4

The edit in this case is so simple I wouldn't bother with Awk.

With GNU Sed it's as simple as:

sed -rie 's_^(..)/(..)/(....)_\3-\2-\1_' file.csv

With BSD Sed, use:

sed -Ei '' -e 's_^(..)/(..)/(....)_\3-\2-\1_' file.csv

With POSIX tools only it's a bit uglier but not terrible:

printf '%s\n' '%s/^\(..\)\/\(..\)\/\(....\)/\3-\2-\1/' x | ex file.csv

Edit: Actually a closer look at the POSIX specs for ex reveals that (almost) any delimiter may be used, just as in Sed. So:

printf '%s\n' '%s_^\(..\)/\(..\)/\(....\)_\3-\2-\1_' x | ex file.csv

Slightly better.

Wildcard
  • 36,499
0

Given that awk has no built-in "in-place editing" option (usually implemented as a behind-the-scenes copy anyway), and ever since reading this answer, my preferred solution in this scenario is of the form:

cp input_file temp_file && command … temp_file > input_file && rm temp_file

For your situation:

cp /path/to/file.csv temp_file &&
gawk -F, '{split($1, a, "/| "); print a[3]"-"a[2]"-"a[1]" "a[4]}' < temp_file > /path/to/file.csv &&
rm temp_file

Note the && syntax which ties the commands together and executes subsequent commands only if the previous ones were successful.

Jeff Schaller
  • 67,283
  • 35
  • 116
  • 255
  • OP has commented that the script only gives the first column and there are many columns (much more than 4 I think) ... – Weijun Zhou Jan 29 '18 at 20:31
  • Thank you, I didn't catch that aspect of it; I was responding to the So now I want to know how can I save this back on my CSV file. part. – Jeff Schaller Jan 29 '18 at 20:32
0

Since you're using GNU awk:

gawk -i inplace -F, ...

and if you want to keep a backup of the original

gawk -i inplace -v INPLACE_SUFFIX=".bak" -F, ...

https://www.gnu.org/software/gawk/manual/html_node/Extension-Sample-Inplace.html#Extension-Sample-Inplace

glenn jackman
  • 85,964
0

I have achieved the same by using below awk command

input.csv

2018-01-29 14:07,payable,37159871,,30521316
2018-01-29 14:07,payable,37159872,,30521316
2018-01-29 14:07,payable,37159870,,30521316
2018-01-29 14:07,payable,37159869,,30521316
2018-01-29 14:07,payable,37159868,,30521316

command

awk '{gsub("/","-",$0);print $0}' input.csv| awk -F "-" '{print $3,$2,$1}'  | awk -F " " '{print $1"-"$(NF -1)"-"$NF,$2}'

output

2018-01-29 14:07,payable,37159871,,30521316
2018-01-29 14:07,payable,37159872,,30521316
2018-01-29 14:07,payable,37159870,,30521316
2018-01-29 14:07,payable,37159869,,30521316
2018-01-29 14:07,payable,37159868,,30521316
Praveen Kumar BS
  • 5,211
  • It looks to me like they've already managed to use awk to change the format; what about the actual question at the end: So now I want to know how can I save this back on my CSV file. ? – Jeff Schaller Feb 20 '18 at 19:01