I have daily files that come in via FTP with incorrect dates in the first column of the file. I have figured out how to deduct one day to derive the correct date and print this to a new file. The main problem is that my script works in unix using gnu but not on solaris.
data contained in file:
End Date,Name,Amount
02/07/2014,data1, data2
02/02/2014,data1, data2
02/06/2014,data1, data2
02/06/2014,data1, data2
02/06/2014,data1, data2
02/10/2014,data1, data2
02/12/2014,data1, data2
02/20/2014,data1, data2
02/20/2014,data1, data2
02/21/2014,data1, data2
02/28/2014,data1, data2
03/03/2014,data1, data2
Script:
awk 'BEGIN{FS=OFS=","}
NR==1 {print}
NR>1 {
("date -d \""$1" -1 day\" +%m/%d/%Y")|getline newline
$1=newline
print
}' wrongdates.csv > correctdates.csv
I have managed to move some of the script over to nawk on the solaris box but it is now complaining that 'date -d' is not supported and when ever I try to change this I get 'date: bad conversion'.
Furthermore the above does not take into account weekends when altering the dates with in the file as I only care about business days and I am trying to introduce if and else statements. as per the below
nawk 'BEGIN{FS=OFS=","} NR==1 {print};NR>1 {if (date "$1" "+%u"==1) ("date -d \""$1" -1 day\" +%m/%d/%Y")| getline newline; $1=newline; {print}; else ("date \""$1" -3 day\" +%m/%d/%Y")| getline newline; $1=newline; print}' StateStreetPositions20140228file20140228.csv > file2.csv
I seem to be getting no where with the syntax of my if and else statements and the last date in my sample 03/03/2014 should be converted to 02/28/2014 and 02/28/2014 should become 02/27/2014.