awk -F"," '{OFS=","; $1=system("date -d "$1" +%d-%m-%Y") ; print $0}' data.csv | head
I am trying to take the first column of a csv file and standardize the date format to %dd-%mm-%yyyy.
When I try the code above I get date: write error: Broken pipe.
But when I try the code below:
dd=$(csvcut -c 1 -e ISO-8859-1 -d "," data.csv | head -2 | sed -n 2p)
echo $dd
echo $(date -d $dd "+%d-%m-%Y")
I get the the output:
2017-02-03
03-02-2017
What am I doing wrong. Any hints? Thanks.
My system: Ubuntu 16.04.1 LTS
EDIT 2: Here is a sample file: http://grn.dk/sites/default/files/attachments/data.csv
EDIT:
sure CSV data file (input): data.csv (with many rows)
Bogført,Tekst,Beløb,Saldo
2017-02-03, random text,-425,-611524.54
output:
Bogført,Tekst,Beløb,Saldo
03-02-2017, random text,-425,-611524.54
but the date format can be in any other format. I am currently looking into standardizing dates for an csv import job. Thanks.
Best Answer
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 GNUdate
, this is anawk
script that will perform the date reformatting:Running it:
The script will discard empty input lines. It creates a command string
cmd
that does the actual date conversion using GNUdate
. Errors fromdate
will be thrown away (and$1
will remain unchanged).To do it with
cvssql
(fromcsvkit
):The data in
new_data.csv
will be without the header row. To add it back: