Convert a column in-place on CSV

Question

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.

If you have a sufficiently new version of `gawk` you may be able to use the [in-place extension](https://www.gnu.org/software/gawk/manual/html_node/Extension-Sample-Inplace.html#Extension-Sample-Inplace) — steeldriver, Jan 29 '18 at 20:04
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
Append `$2, $3, $4` to your awk command, set `OFS=,` if necessary. If the file isn't too big (it doesn't seem to be), `sponge` may save you some trouble in writing back to the original file. — Weijun Zhou, Jan 29 '18 at 20:17
the file above its just an example... it has 15k lines and more columns... — Max Forasteiro, Jan 29 '18 at 20:20
Sorry, do you want the separator in the output file for the first field to be "-" or "/"? Your script is inconsistent with your example. — Weijun Zhou, Jan 29 '18 at 20:29

Wildcard · Accepted Answer · 2018-01-29T21:03:07.593

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.

score 0 · Answer 2 · answered Jan 29 '18 at 20:30

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.

answered Jan 29 '18 at 20:30

Jeff Schaller

66,199
35
114
250

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

score 0 · Answer 3 · answered Jan 29 '18 at 20:37

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

answered Jan 29 '18 at 20:37

glenn jackman

84,176
15
116
168

1

Beware [using gawk -i inplace like that introduces a security vulnerability](/q/749645) unless you modify `$AWKPATH` not to include `.` or make sure you run that command from within a working directory where nobody could create a file called `inplace` or `inplace.awk`. – Stéphane Chazelas Jun 24 '23 at 21:15

score 0 · Answer 4 · answered Jan 30 '18 at 18:31

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

Convert a column in-place on CSV

4 Answers4