2

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}' file20140228.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.

2
  • 1
    Could you use Perl?
    – grebneke
    Commented Mar 4, 2014 at 13:22
  • Don't accept that your SysAdmin (or yourself?) have installed a Solaris box and then made sure to not to install the GNU tools. What a nice guy he/she is?. No need to trip up yourself. We have +500 Solaris hosts where I work and of course they have by default the standard GNU tools installed. We never run into these issues. :-) Commented Mar 6, 2014 at 16:27

1 Answer 1

3

date -d is GNU specific. Here, you're running one shell and one date command per line, that's terribly inefficient. Do the whole thing in perl:

perl -MPOSIX -pe 's{^(\d\d)/(\d\d)/(\d{4})}{
  $t = mktime(0, 12, 0, $2, $1 - 1, $3 - 1900);
  @t = localtime $t;
  $t -= 86400 * ($t[6] <= 1 ? $t[6] + 2 : 1);
  strftime("%m/%d/%Y", localtime $t)}e'

Or with Time::Piece if available:

perl -MTime::Piece -pe 's{^\d\d/\d\d/\d{4}}{
  $t = Time::Piece->strptime("$& 12", "%m/%d/%Y %H");
  $d = $t->day_of_week;
  $t -= 86400 * ($d <= 1 ? $d + 2 : 1);
  $t->strftime("%m/%d/%Y")}e'
2
  • So I have made a few changes and am now trying to integrate this into a script ready for cron but am getting errors when I execute the below (* is file date/timestamp which I wish to keep): 'for fn in filename*.csv; do newfn="${fn/#filename/newfilename}” [-e "$newfn"] || perl -MPOSIX -pe 's{^(\d{1,2})/(\d{1,2})/(\d{4})}{$t = mktime(0, 12, 0, $2, $1 - 1, $3 - 1900); @t = localtime $t; $t -= 86400 * ($t[6] <= 1 ? $t[6] + 2 : 1); strftime("%m/%d/%Y", localtime $t)}e' $fn > $newfn done'
    – user61818
    Commented Mar 4, 2014 at 17:13
  • @user61818, newfn=newfilename${fn#filename} and use #! /usr/xpg4/bin/sh, not /bin/sh if Solaris 10 or before. Commented Mar 4, 2014 at 17:44

You must log in to answer this question.

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