1

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.

5
  • Please supply example input and desired output files
    – grebneke
    Mar 3, 2014 at 12:13
  • not looking to change the contents of the file, this has been accomplished. file recieved is named wrongfile20140222.csv and date changes everyday. Looking to name the output file correct_date20140222.csv script will be a cron job an do not want to constantly have to change the script.
    – user61818
    Mar 3, 2014 at 12:53
  • 1
    Then please supply example date-filenames. If the script is not relevant, why post it? Please clarify.
    – grebneke
    Mar 3, 2014 at 13:09
  • added the data, not sure how to attache file, have corrected the script.
    – user61818
    Mar 3, 2014 at 13:25
  • Still not sure I understand, but see answer below if that is what you want.
    – grebneke
    Mar 3, 2014 at 13:30

1 Answer 1

2

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
3
  • Hi yes that is sort of what I am looking for, idealy would not have to ls
    – user61818
    Mar 3, 2014 at 14:04
  • @user61818 - You don't have to ls if you use the last for-loop, it will automatically handle any unprocessed wrongdatefile*.csv in current directory.
    – grebneke
    Mar 3, 2014 at 14:13
  • perfect, thats excellent, many thanks for your assistance.
    – user61818
    Mar 3, 2014 at 14:34

You must log in to answer this question.

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