AWK help to empty date column and prior colummn when date is less than current date in CSV file

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

AWK help to empty date column and prior colummn when date is less than current date in CSV file

sasi
Hello everyone,
I am new to AWK and have requirement.Please help me in solving below issue.

I have a CSV file(Comma delimited) with  8th,10th,12th columns as date columns. The dates are in YYYYMMDD format and the file is bit bigger in size (May be 3 to 4 mb).

The requirement is when the date value is less than current date , that date column and preceding column to date(the precding column will have amount) needs to become empty . support if 8th column date is less than current date then 8th and preceding 7th column should become blank.  if 10th column date value is less than current date then 10 and 9th column value should become empty. if 12th column date is less than current date then 12th and preceding 11th



Original Sample File :

ABCDEFGHIJ,BBbbbb4,,sU,ASX,AUD,1.77500000,20130817,1.76000000,20130813,1.77500000,20130814,1.775,1.775,ASX,1.73000000
ABCDEFGH12,BBcccc1,,sZ,NZC,NZD,1.26000000,20130814,1.25000000,20130817,1.26000000,20130815,1.26,1.26,NZC,1.26000000
ABCDEFGH13,BBdddd4,,sU,ASX,AUD,.17500000,20130815,.17000000,20130816,.17500000,20130817,.175,.175,ASX,.17000000


Assume current date is 20130817



The output should be:


ABCDEFGHIJ,BBbbbb4,,sU,ASX,AUD,1.77500000,20130817,,,,,1.775,1.775,ASX,1.73000000
ABCDEFGH12,BBcccc1,,sZ,NZC,NZD,,,1.25000000,20130817,,,1.26,1.26,NZC,1.26000000
ABCDEFGH13,BBdddd4,,sU,ASX,AUD,,,,,.17500000,20130817,.175,.175,ASX,.17000000

Please help in resolving this issue  ..
Thanks alot...
Reply | Threaded
Open this post in threaded view
|

Re: AWK help to empty date column and prior colummn when date is less than current date in CSV file

Guru
Administrator
Sorry for the late response:

awk -F, -v dt="20130817" '{if($8<dt){$7="";$8="";}if($10<dt){$9="";$10="";}if($12<dt){$11="";$12="";}}1' OFS=, file
Reply | Threaded
Open this post in threaded view
|

Re: AWK help to empty date column and prior colummn when date is less than current date in CSV file

sasi
Thank you Guru!!.. Command looks correct.. but when i cxecute the command as it is, i am getting below error..

awk: syntax error near line 1
awk: bailing out near line 1


Could you please check and let me know?.. also in the hard coded date value i need to pass a variable
by reading the date from system...

in initiallization i will declare like

   td_dt=`date +%Y%m%d`     # Hear i will get the current date and i need to pass this date in awk command.

Please suggest about the error  and how to pass this date in awk..


thank you very much!!
Reply | Threaded
Open this post in threaded view
|

Re: AWK help to empty date column and prior colummn when date is less than current date in CSV file

Guru
Administrator
You can pass input like this:

$ td_dt=`date +%Y%m%d`
$ awk -F, -v dt="$td_dt" '{if($8<dt){$7="";$8="";}if($10<dt){$9="";$10="";}if($12<dt){$11="";$12="";}}1' OFS=, file

Regarding the error, I think you are using Solaris. If so, you nawk instead of awk.
Reply | Threaded
Open this post in threaded view
|

Re: AWK help to empty date column and prior colummn when date is less than current date in CSV file

sasi
Guru!! awesome... with nawk it is working.. when i was googling luckily i found your website posted question.. Thank you so much for the quality response..I will let u know in case if i get any issue in furthur testing...