Hi,
I'd like to find the values of certain fields in multiple csv files stored in 1 directory
based upon an input search string.
An fgrep returns the complete record, I only want certain fields.
Thanks in advance for your help.
Perry
It can be done easily with AWK. Post some sample input and desired output, so we can give you some specifics.
Input files look like this:
"Billing Country","Invoice Type","Settlement Type","Bill-to Account Number","Control Number","Invoice Number","Invoice Date","Due Date","Bill
to Currency","Total Standard Charges","Total Discounts","Total Surcharges","Total Taxes","Total Invoice Due","Alternate Currency","Alternate C
urrency Exchange Rate","Air Waybill Number","Bill To","Shipper Reference 1","Shipper Reference 2","Shipper Reference 3","POD Date","POD Time",
"POD Name","Ship Date (formatted)","Ship Date","Service","Packaging","SvcPkg Label","Origin Station","Destination Station","Pieces","Actual We
ight","Weight Unit of Measure","Meter Number","Child Account Number","Master Air Waybill Number","Sender Company Name","Sender Contact Name","
Sender Address Line 1","Sender Address Line 2","Sender Address Line 3","Sender Address City","Sender Address State","Sender Address Postal","S
ender Address Country","Recipient Company Name","Recipient Contact Name","Recipient Address Line 1","Recipient Address Line 2","Recipient Addr
ess Line 3","Recipient Address City","Recipient Address State","Recipient Address Postal","Recipient Address Country","Air Waybill Total Amoun
t","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","A
ir Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air W
aybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybil
l Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Ch
arge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge
Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Labe
l","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","
Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air W
aybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount","Air Waybi
ll Charge Label","Air Waybill Charge Amount","Air Waybill Charge Label","Air Waybill Charge Amount"
"NL","Vracht","Standaard","158472147","933234679","933234679","28-Dec-2012","27-Jan-2013","EUR","24,965.60","-18,581.07","1,116.81","677.62","
7,811.05","","0.0","547485400080","Afzender","413254","","413254","","","","06-Dec-2012","20121206","01","01","PRIORITY","EIN","POX","1","1.0"
,"kg","","158472147","","YOLOMO B.V.","","BANANABURGSTRAAT 29","","","AMSTERDAM","","1006 -AM","NL","MELER TOLEDO","","MR GHOUR","","","V
IROFLAY","","78220","FR","15.78","Brandstof toeslag","1.94","FREIGHT","53.50","NEDERLANDSE BTW 19%","2.74","KORTING","-42.40","","","","","","
","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""
"NL","Vracht","Standaard","158472147","933234679","933234679","28-Dec-2012","27-Jan-2013","EUR","24,965.60","-18,581.07","1,116.81","677.62","
7,811.05","","0.0","547485404693","Afzender","413302","","413302","","","","10-Dec-2012","20121210","01","01","PRIORITY","EIN","POX","1","1.0"
,"kg","","158472147","","YOLOMO B.V.","","BANANABURGSTRAAT 29","","","AMSTERDAM","","1006 -AM","NL","TOTUILLE","MME LIN
E","TOUR DEXETAGE","","","COUROIE","","92400","FR","15.78","Brandstof toeslag","1.94","FREIGHT","53.50","NEDERLANDSE BTW 19%"
,"2.74","KORTING","-42.40","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","
","","",""
"NL","Vracht","Standaard","158472147","933234679","933234679","28-Dec-2012","27-Jan-2013","EUR","24,965.60","-18,581.07","1,116.81","677.62","
7,811.05","","0.0","547485413106","Afzender","619125","","619125","20121221","1029",".DABULEVICIU","14-Dec-2012","20121214","01","01","PRIORIT
Y","EIN","TAY","1","3.9","kg","","158472147","","YOLOMO B.V.","","BANANABURGSTRAAT 29","","","AMSTERDAM","","1006 -AM","NL","UAB "VERSRS
LUI" / COPY1","ATT. EDVNAS "COPY1 VE","RAUDONDVPL. 99","","","KAAS","","47184","LT","40.56","Brandstof toeslag","4.99","FREIGH
T","129.70","NEDERLANDSE BTW 19%","7.04","KORTING","-101.17","","","","","","","","","","","","","","","","","","","","","","","","","","","",
"","","","","","","","","","","","","","",""
"NL","Vracht","Standaard","158472147","933234679","933234679","28-Dec-2012","27-Jan-2013","EUR","24,965.60","-18,581.07","1,116.81","677.62","
7,811.05","","0.0","547485414352","Afzender","618964","","618964","20121220","0841","K.KRAJNIK","17-Dec-2012","20121217","01","01","PRIORITY",
"EIN","LJU","1","13.0","kg","","158472147","","YOLOMO B.V.","","BANANABURGSTRAAT 29","","","AMSTERDAM","","1006 -AM","NL","MED.O.",""
,"BRNCA 31","","","LJUBLJACE","","1231","SI","72.29","Brandstof toeslag","8.90","FREIGHT","231.10","NEDERLANDSE BTW 19%","12.
55","KORTING","-180.26","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","
","",""
"NL","Vracht","Standaard","158472147","933234679","933234679","28-Dec-2012","27-Jan-2013","EUR","24,965.60","-18,581.07","1,116.81","677.62","
7,811.05","","0.0","547485414433","Afzender","307080","","307080","20121222","1030","G.AIZKA BERASTEGUI","17-Dec-2012","20121217","01","01","P
RIORITY","EIN","BIO","1","0.5","kg","","158472147","","YOLOMO B.V.","","BANANABURGSTRAAT 29","","","AMSTERDAM","","1006 -AM","NL","GAIRA
SABASO","","TXIMKOA N4, 1D","","","GAANO","","48960","ES","15.78","Brandstof toeslag","1.94","FREIGHT","47.90","NEDER
LANDSE BTW 19%","2.74","KORTING","-36.80","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","
","","","","","","","",""
Desired output for search string 619215 (Shipper Refernce 1):
Invoice Number: 933234679
Invoice Date: 28-Dec-2012
POD Date: 20121221
POD Time: 1029
POD Name: DABULEVICIU
Ship date: 20121214
---------- Post updated at 01:02 PM ---------- Previous update was at 12:59 PM ----------
Actually after the header record I want to find the values for field 6, 7, 22, 23, 24 and 26 with a search with field 19 (this is a unique reference number).
All files have the same layout
Large numbers contain thousands-separator ",", so field separator needs to be "," If your awk version allows for that, try
awk ' NR==1 {split ($0, Hd); next}
$19==RefNo {print Hd[ 6]":", $6;
print Hd[ 7]":", $7;
print Hd[22]":", $22;
print Hd[23]":", $23;
print Hd[24]":", $24;
print Hd[26]":", $26 }
' FS="\",\"" RefNo=619125 file
Invoice Number: 933234679
Invoice Date: 28-Dec-2012
POD Date: 20121221
POD Time: 1029
POD Name: .DABULEVICIU
Ship Date: 20121214
If it does not, remove thousands-separator first using sed, and then use comma as FS:
$ sed 's/,\(...\.\)/\1/g' file |
awk -F, '{gsub ("\"","")}
NR==1 {split ($0, Hd); next}
$19==RefNo { print Hd[ 6]":", $6;
print Hd[ 7]":", $7;
print Hd[22]":", $22;
print Hd[23]":", $23;
print Hd[24]":", $24;
print Hd[26]":", $26 }
' RefNo=619125
Thanks Rudi, execatly what I need.
Best regards,
Perry
I may be a good idea to anonimize your sample, Perry...
Already did, now even more.
Thanx Scrutinizer