Bash converting date in a csv file with awk or other linux tool (csvcut)

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

Since you are using Ubuntu, you probably have GNU awk, which provides useful time and date functions:

echo "2017-03-02" | 
awk '{ gsub(/-/, " ", $1); t = mktime($1 " 0 0 0"); print strftime("%d-%m-%Y", t);}'

Method 3

bash-4.1$ cat a.txt
2017-02-03, random text,-425,-611524.54
2017-02-08, random text,-425,-611524.54
2017-02-07, random text,-425,-611524.54


bash-4.1$ awk -F, '{a="date -d "$1" +%Y%m%d"; while (a |getline line){$1=line;}print $0}' OFS=, a.txt
20170203, random text,-425,-611524.54
20170208, random text,-425,-611524.54
20170207, random text,-425,-611524.54

bash-4.1$ awk -F, '{a="date -d "$1" +%Y%m"; while (a |getline line){$1=line;}print $0}' OFS=, a.txt
201702, random text,-425,-611524.54
201702, random text,-425,-611524.54
201702, random text,-425,-611524.54

awk -F, 'NR==1{print;next}{a="date -d "$1" +%Y%m"; while (a |getline line){$1=line;}print $0}' OFS=, a.txt

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0