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.

  • No simple way if you use awk, just rename it later. – Weijun Zhou Jan 29 '18 at 20:01
  • 3
    If you have a sufficiently new version of gawk you may be able to use the in-place extension – steeldriver Jan 29 '18 at 20:04
  • 1
    or see sponge in moreutils – thrig Jan 29 '18 at 20:08
  • run mv test_file /path/to/file.csv, to overwrite the old file with the new file afterwards. – nath Jan 29 '18 at 20:08
  • The test_file only contain the first column of the CSV file... – Max Forasteiro Jan 29 '18 at 20:14
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.

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.

  • 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

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
  • 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

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.