You're using the system() function in the wrong way. Or rather, that's not what to use here as it doesn't return the output from the command, only the exit status (the output goes to the terminal).
Assuming date is GNU date, this is an awk script that will perform the date reformatting:
BEGIN { OFS = FS = "," }
$1 {
cmd = sprintf("date -d '%s' '+%%d-%%m-%%Y' 2>/dev/null", $1);
cmd | getline $1;
print;
close(cmd);
}
Running it:
$ awk -f script.awk data.csv
Bogført,Tekst,Beløb,Saldo
03-02-2017, random text,-425,-611524.54
The script will discard empty input lines. It creates a command string cmd that does the actual date conversion using GNU date. Errors from date will be thrown away (and $1 will remain unchanged).
To do it with cvssql (from csvkit):
$ sed '1,2d' data.csv | csvsql -H --query 'SELECT strftime("%d-%m-%Y", a), b, c, d FROM stdin' | sed '1d' >new_data.csv
The data in new_data.csv will be without the header row. To add it back:
$ cat <( head -n 1 data.csv ) new_data.csv >even_newer_data.csv
I'd use perl here:
perl -pe 's{\b(\d{1,2})(:\d\d:\d\d) ([AP])M\b}{
$1 + 12 * (($3 eq "P") - ($1 == 12)) . $2}ge'
That is add 12 to the hour part if PM (except for 12PM) and change 12AM to 0.
With awk, not doing the word-boundary part (so could give false positives on 123:21:99 AMERICA for instance) and assuming there's only one occurrence per line:
awk '
match($0, /[0-9]{1,2}:[0-9]{2}:[0-9]{2} [AP]M/) {
split(substr($0, RSTART, RLENGTH), parts, /[: ]/)
if (parts[4] == "PM" && parts[1] != 12) parts[1] += 12
if (parts[4] == "AM" && parts[1] == 12) parts[1] = 0
$0 = substr($0, 1, RSTART - 1) \
parts[1] ":" parts[2] ":" parts[3] \
substr($0, RSTART + RLENGTH)
}
{print}'
Best Answer
A pure awk solution (that doesn’t fork off a
datecommand):-F'|'breaks the input line apart at vertical bars into$1,$2,$3, etc…split($3, date_time, " ")breaks the date/time field into three pieces: the date, the time, and the AM/PM indicator. If there aren’t three pieces, issue an error message and skip the line.split(date_time[1], date, "/")splits the date into the month, the day, and the year.split(date_time[2], time, ":")splits the time into the hour, the minutes, and the seconds.sprintfreassembles the year, month, day, hour, minutes, and seconds, with leading zeroes, if necessary. Assigning this to$3rebuilds the input line with the reformatted date/time; we then print that.Feature: If the input has more than three fields; e.g.,
this script will preserve those extra field(s).
Usage: A few minor variations:
}'), put the name(s) of file(s) you want to process. You can (of course) use wildcards (e.g.,*.csv) here, in addition to or instead of filename(s).}', say<and a filename. (You can process only one file at a time this way.)#!/bin/sh. (Or, if you prefer, you can use#!/bin/bashor#!/usr/bin/env bash. A discussion of the differences between these different “she-bang” lines, and their relative merits and counter-indications, is beyond the scope of this question, but you can find plenty of discourse on the topic if you search.)}'), put"$@"(including the quotes).gman.chmod +x gman../gmanfollowed by either a list of filenames and/or wildcards, or by<and a single filename.