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.

  • Please supply example input and desired output files – grebneke Mar 3 '14 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 '14 at 12:53
  • 1
    Then please supply example date-filenames. If the script is not relevant, why post it? Please clarify. – grebneke Mar 3 '14 at 13:09
  • added the data, not sure how to attache file, have corrected the script. – user61818 Mar 3 '14 at 13:25
  • Still not sure I understand, but see answer below if that is what you want. – grebneke Mar 3 '14 at 13:30
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
  • Hi yes that is sort of what I am looking for, idealy would not have to ls – user61818 Mar 3 '14 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 '14 at 14:13
  • perfect, thats excellent, many thanks for your assistance. – user61818 Mar 3 '14 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.