awk date identification from input file

Question

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.

Answer 1

If I understand correctly, you have a file called wrongdatefile20140228.csv and you need to create a new file called correct_date20140228.csv? Assuming bash shell:

$ ls *.csv
wrongdatefile20140228.csv

Assign filename to variable $fn and use Parameter Expansion

$ fn=wrongdatefile20140228.csv
$ awk '...' "$fn" > "${fn/#wrongdatefile/correct_date}"

Result:

$ ls *.csv
correct_date20140228.csv    wrongdatefile20140228.csv

To automate this for all files wrongdatefile*.csv in current directory, skipping previously processed files:

for fn in wrongdatefile*.csv; do 
    newfn="${fn/#wrongdatefile/correct_date}"
    [ -e "$newfn" ] || awk '...' "$fn" > "$newfn"
done