Find values in multiple csv files


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Find values in multiple csv files
# 1  
Find values in multiple csv files

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
# 2  
It can be done easily with AWK. Post some sample input and desired output, so we can give you some specifics.
# 3  
Input files look like this:
Code:
"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):
Code:
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

Last edited by biscayne; 01-04-2013 at 10:49 AM..
# 4  
Large numbers contain thousands-separator ",", so field separator needs to be ","If your awk version allows for that, try
Code:
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:
Code:
$ 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

# 5  
Thanks Rudi, execatly what I need.
Best regards,
Perry
# 6  
I may be a good idea to anonimize your sample, Perry...
# 7  
Already did, now even more.
Thanx Scrutinizer
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #341
Difficulty: Easy
Steve Jobs invented the first Apple Computer.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Export Oracle multiple tables to multiple csv files using UNIX shell scripting

Hello All, just wanted to export multiple tables from oracle sql using unix shell script to csv file and the below code is exporting only the first table. Can you please suggest why? or any better idea? export FILE="/abc/autom/file/geo_JOB.csv" Export= `sqlplus -s dev01/password@dEV3... (16 Replies)
Discussion started by: Hope
16 Replies

2. UNIX for Beginners Questions & Answers

Check for null values in a columns. I have dozen of CSV files in a directory.

Hi Folks, I'm trying to write a simple file sanity check script. I have a directory with dozen CSV files containing id,edname,firstname,lastname,suffix,email. I like to write a awk script to check if first field contain a number and is not empty. and fields number 3,4 & 6 are not empty and... (3 Replies)
Discussion started by: dc34684
3 Replies

3. Shell Programming and Scripting

How to generate a csv files by separating the values from the input file based on position?

Hi All, I need help for doing the following. I have a input file like: aaaaaaaaaabbbbbbbbbbbbbbbbbbbb cccbbbbbaaaaaadddddaaaabbbbbbb now I am trying to generate a output csv file where i will have for e.g. 0-3 chars of each line as the first column in the csv, 4-10 chars of the line as... (3 Replies)
Discussion started by: babom
3 Replies

4. UNIX Desktop Questions & Answers

How do you [e]grep for multiple values within multiple files?

Hi I'm sure there's a way to do this, but I ran out of caffeine/talent before getting the answer in a long winded alternate way (don't ask ;) ) The task I was trying to do was scan a directory of files and show only files that contained 3 values: I940 5433309 2181 I tried many variations... (4 Replies)
Discussion started by: callumw
4 Replies

5. Shell Programming and Scripting

Field validations in multiple files CSV

Hi, I am regular reader of this forum. My advanced thanks to everyone. Below given are the sample files INDATA (Main data) Fild1fld2fld3….. Fild1fld2fld3….. Fild1fld2fld3….. Fild1fld2fld3….. Fild1fld2fld3….. . . N records (140000) eg GRPDATA (Reference file) (2 Replies)
Discussion started by: hyperion.krish
2 Replies

6. Shell Programming and Scripting

Copying multiple csv files

Hi, I have mutiple csv files at server1 at /apps/test/data. I needed a script that would copy these csv files from server1 at /usr/data, put them in server2,archive the earlier files that were present in server2 before removing those already present. Kindly help. (2 Replies)
Discussion started by: Alok Ranjan
2 Replies

7. Shell Programming and Scripting

Duplicate rows in CSV files based on values

I am new to this forum and this is my first post. I am looking at an old post with exactly the same name. Can not paste URL because I do not have 5 posts My requirement is exactly opposite. I want to get rid of duplicate rows and try to append the values of columns in those rows ... (10 Replies)
Discussion started by: vbhonde11
10 Replies

8. Shell Programming and Scripting

Need to compare values on two CSV files

:( Hello, Having a problem with reading two files using awk/nawk, am new to both them. I need to compare field values between two csv files and arrange for an appropriate output if both the values are equal or not for each feild. $cat File1.csv... (4 Replies)
Discussion started by: pgop
4 Replies

9. Shell Programming and Scripting

Duplicate rows in CSV files based on values

I want to duplicate a row if found two or more values in a particular column for corresponding row which is delimitted by comma. Input abc,line one,value1 abc,line two, value1, value2 abc,line three,value1 needs to converted to abc,line one,value1 abc,line two, value1 abc,line... (8 Replies)
Discussion started by: Incrediblian
8 Replies

10. Shell Programming and Scripting

Need to compare two csv files values and write into another csv file

Hi all, Am new to scripting. So i just need your ideas to help me out. Here goes my requirement. I have two csv files 1.csv 2.csv abc,1.24 abc,1 def,2.13 def,1 I need to compare the first column of 1.csv with 2.csv and if matches then need to compare... (2 Replies)
Discussion started by: chinnahyd
2 Replies

Featured Tech Videos