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

3
  • can you give the sample input data and the expected output
    – Kamaraj
    Feb 14, 2017 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.
    – Greg Nowak
    Feb 14, 2017 at 6:14
  • added with better formatting at the end of the post.
    – Greg Nowak
    Feb 14, 2017 at 6:21

3 Answers 3

2

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
4
  • 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.
    – Greg Nowak
    Feb 14, 2017 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, 2017 at 15:12
  • @GrzegorzNowak You get that error from csvsql if you forget the -H option.
    – Kusalananda
    Feb 14, 2017 at 15:12
  • Thanks it works like a charm! I did not forget the -H option, but nevermind the csvsql.
    – Greg Nowak
    Feb 14, 2017 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
7
  • 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
    – Greg Nowak
    Feb 14, 2017 at 7:09
  • added 1 more answer for ignoring the conversion for 1st line
    – Kamaraj
    Feb 14, 2017 at 7:50
  • Did you try my csv file? Thanks.
    – Greg Nowak
    Feb 14, 2017 at 8:22
  • did you check my last answer ? NR==1
    – Kamaraj
    Feb 14, 2017 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 `
    – Greg Nowak
    Feb 14, 2017 at 8:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .