3
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.

  • can you give the sample input data and the expected output – Kamaraj Feb 14 '17 at 4:27
  • 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. – Grzegorz Nowak Feb 14 '17 at 6:14
  • added with better formatting at the end of the post. – Grzegorz Nowak Feb 14 '17 at 6:21
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
  • Great thank you. I can make the awk script work, but I will need to be able to get the separator from my bash script, it is stored in a variable. Is there a hint to get it into the awk script or run the awk script from my main bash script? The csvsql command gives me an error: (sqlite3.OperationalError) no such column: a [SQL: 'SELECT strftime("%d-%m-%Y", a), b, c, d FROM stdin'] I even tried to rename the columns in the data.csv file. Thanks. – Grzegorz Nowak Feb 14 '17 at 14:47
  • @GrzegorzNowak Change the BEGIN block to BEGIN { OFS = FS } and run the script with awk -F "$separator" -f script.awk data.csv. – Kusalananda Feb 14 '17 at 15:12
  • @GrzegorzNowak You get that error from csvsql if you forget the -H option. – Kusalananda Feb 14 '17 at 15:12
  • Thanks it works like a charm! I did not forget the -H option, but nevermind the csvsql. – Grzegorz Nowak Feb 14 '17 at 15:51
1

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);}'
1
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
  • This looks great, but somehow it does not work as intended. Because my output does not transform all the dates. Maybe something is wrong with my csv file? grn.dk/sites/default/files/attachments/data.csv My output is: awk -F, '{a="date -d "$1" +%d-%m-%Y"; while (a |getline line){$1=line;}print $0}' OFS=, data.csv date: invalid date ‘Bogført’ Bogført,Tekst,Beløb,Saldo 03-02-2017,text1,-425,-611524.54 2017-02-03,"text2",-213.61,-611099.54 2017-02-03,text3,-116,-610885.93 02-02-2017,text4,-111.25,-610769.93 – Grzegorz Nowak Feb 14 '17 at 7:09
  • added 1 more answer for ignoring the conversion for 1st line – Kamaraj Feb 14 '17 at 7:50
  • Did you try my csv file? Thanks. – Grzegorz Nowak Feb 14 '17 at 8:22
  • did you check my last answer ? NR==1 – Kamaraj Feb 14 '17 at 8:32
  • Thank you. Yes it skips the first line, but the real problem is that two of the dates in the csv-file are not converted. ` awk -F, 'NR==1{print;next}{a="date -d "$1" +%d-%m-%Y"; while (a |getline line){$1=line;}print $0}' OFS=, data.csv ` – Grzegorz Nowak Feb 14 '17 at 8:46

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.