I currently 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. However, as the files come in every day the file name will change and I want to cron the script.
My question is how do I get my script to identify the date appended on the end of the in file file and append to the output file?
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
Script:
awk 'BEGIN{FS=OFS=","}
NR==1 {print}
NR>1 {
("date -d \""$1" -1 day\" +%m/%d/%Y")|getline newline
$1=newline
print
}' wrongdates{date1}.csv > correctdates{date1}.csv
'Date1' format is usually 20140228 or %Y%m%d
****further to the above I have discovered that this only works on my unix box and not on solaris.
I have managed to move it 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}' StateStreetPositions20140228.csv
I seem to be getting no ware with the syntax of my if and else statements.
