Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

CSV File with Multiple Search Parameter

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting CSV File with Multiple Search Parameter
# 8  
Old 03-09-2017
For Don's script, it is always returning the first invoice numbers instead of respective invoice number.

So for below dataset

Code:
"C1",990001,"L1","HERO","MOTORCYCLE","ASIA-PACIFIC","BEIJING"
"C2","CLUTCH","HYUNDAI",03032017
"C2","BRAKES","EUROPARTS",03032017
"C2","TYRES","MICHELIN",03032017

"C1",990002,"L2","HERO","CAR","EU1","BUDAPEST"
"C2","CLUTCH","FORD",03032017
"C2","BRAKES","EUROPARTS",03032017
"C2","TYRES","PIRELLI",03032017

I am getting below.


Code:
 ./script.sh FORD PIRELLI MICHELIN
 990001  FORD
 990001 PIRELLI
 990001 MICHELIN

# 9  
Old 03-09-2017
This is what I get:
Code:
990001 MICHELIN
990002 PIRELLI
990002 FORD

I suspect your input file having a non-standard structure like DOS line terminators (<CR>, \r, 0x0D) for instance which leads to the record separator to fail and read everything into one record.
This User Gave Thanks to RudiC For This Post:
chetanojha
# 10  
Old 03-09-2017
Hi,
For don's script.. no matter which string I passed i am getting info about only first invoice as shown bellows
Code:
 ./script.sh PIRELLI HYUNDAI

Output is as below

Code:
 
 990001 PIRELLI
 990001 HYUNDAY

---------- Post updated at 09:52 AM ---------- Previous update was at 09:29 AM ----------

Aha.. yeh.. I think I got the difference.

When I am using below example.txt file - it works.

Code:
"C1",990001,"L1","HERO","MOTORCYCLE","ASIA-PACIFIC","BEIJING"
"C2","CLUTCH","HYUNDAI",03032017
"C2","BRAKES","EUROPARTS",03032017
"C2","TYRES","MICHELIN",03032017

"C1",990002,"L2","HERO","CAR","EU1","BUDAPEST"
"C2","CLUTCH","FORD",03032017
"C2","BRAKES","EUROPARTS",03032017
"C2","TYRES","PIRELLI",03032017

But when I am using below - it doesn't

Code:
"C1",990001,"L1","HERO","MOTORCYCLE","ASIA-PACIFIC","BEIJING"
"C2","CLUTCH","HYUNDAI",03032017
"C2","BRAKES","EUROPARTS",03032017
"C2","TYRES","MICHELIN",03032017
"C1",990002,"L2","HERO","CAR","EU1","BUDAPEST"
"C2","CLUTCH","FORD",03032017
"C2","BRAKES","EUROPARTS",03032017
"C2","TYRES","PIRELLI",03032017

The one without extra empty line is the requirement. Any changes which I can do in the script to cater to the requirement?

Also, I need to pass multiple files together to pull the data out? How can I do it?
# 11  
Old 03-09-2017
Quote:
Originally Posted by chetanojha
Hi,
For don's script.. no matter which string I passed i am getting info about only first invoice as shown bellows
Code:
 ./script.sh PIRELLI HYUNDAI

Output is as below

Code:
 
 990001 PIRELLI
 990001 HYUNDAY

I agree with RudiC that the End-Of-Line (EOL) characters in your data file are, most likely, non-standard. If you are on Unix/Linux, they should be "\n".
Can you show the EOL characters in your data file?
This User Gave Thanks to durden_tyler For This Post:
chetanojha
# 12  
Old 03-09-2017
As always it pays off to do your posting VERY carefully! You see the difficulties that arise by just dropping an empty line!

Adapting Don Cragun's proposal to your new structures, try
Code:
awk -F, -v ITM="$*" '
BEGIN           {for (n=split (ITM, TMP, " "); n>0; n--) keys[TMP[n]]
                }

$1 == "\"C1\""  {INV = $2
                 next
                }

                {for (key in keys) if (index ($0, key)) print INV, key
                }
' file1 file1
990001 MICHELIN
990002 FORD
990002 PIRELLI
990001 MICHELIN
990002 FORD
990002 PIRELLI

Please be aware that the dash (for stdin) is gone, and I added more than one file in the input stream.
This User Gave Thanks to RudiC For This Post:
chetanojha
# 13  
Old 03-09-2017
modifying Don's approach with the new data file input format:
Code:
#!/usr/bin/bash

printf '%s\n' "$@" |
awk -F, '
NR == FNR {
    keys[$0]
    next
}
$2 ~ "^[0-9][0-9]*$" { inv=$2;next}
{
      for(key in keys)
        if(index($0, key))
            print inv, key
}' - dataset

This User Gave Thanks to vgersh99 For This Post:
chetanojha
# 14  
Old 03-09-2017
Yup, my bad. The blank line was the issue.
An alternative solution using Perl invoked from a Bash shell script is as follows:

Code:
 $
$ cat -n dataset
     1  "C1",990001,"L1","HERO","MOTORCYCLE","ASIA-PACIFIC","BEIJING"
     2  "C2","CLUTCH","HYUNDAI",03032017
     3  "C2","BRAKES","EUROPARTS",03032017
     4  "C2","TYRES","MICHELIN",03032017
     5  "C1",990002,"L2","HERO","CAR","EU1","BUDAPEST"
     6  "C2","CLUTCH","FORD",03032017
     7  "C2","BRAKES","EUROPARTS",03032017
     8  "C2","TYRES","PIRELLI",03032017
$
$ cat -n invoice.sh
     1  #!/usr/bin/bash
     2  perl -e 'map{$x{$_}++} split(/ /,$ARGV[0]);
     3           open(FH, "<", $ARGV[1]);
     4           while (<FH>) {
     5               chomp (@token = split(/,/));
     6               $id = $token[1] if $token[0] =~ /C1/;
     7               map {s/"//g; print "$id\t$_\n" if defined $x{$_}} @token }
     8           close(FH);
     9          ' "$*" dataset
    10
$
$ ./invoice.sh HERO BUDAPEST HYUNDAI MICHELIN PIRELLI
990001  HERO
990001  HYUNDAI
990001  MICHELIN
990002  HERO
990002  BUDAPEST
990002  PIRELLI
$
$ ./invoice.sh MICHELIN PIRELLI FORD
990001  MICHELIN
990002  FORD
990002  PIRELLI
$
$ ./invoice.sh PIRELLI HYUNDAI
990001  HYUNDAI
990002  PIRELLI
$
$

Or as a self-contained Perl program for the same "dataset" file as above:

Code:
 $
$ cat -n invoice.pl
     1  #!/usr/bin/perl -w
     2  use strict;
     3  my $file = "dataset";
     4  my (%x, @token, $id);
     5  foreach (@ARGV) { $x{$_}++ }
     6  open(FH, "<", $file) or die "Can't open $file: $!";
     7  while (<FH>) {
     8      chomp (@token = split(/,/));
     9      $id = $token[1] if $token[0] =~ /C1/;
    10      foreach (@token) {
    11          s/"//g;
    12          print "$id\t$_\n" if defined $x{$_};
    13      }
    14  }
    15  close(FH) or die "Can't close $file: $!";
    16
$
$ perl invoice.pl HERO BUDAPEST HYUNDAI MICHELIN PIRELLI
990001  HERO
990001  HYUNDAI
990001  MICHELIN
990002  HERO
990002  BUDAPEST
990002  PIRELLI
$
$ perl invoice.pl MICHELIN PIRELLI FORD
990001  MICHELIN
990002  FORD
990002  PIRELLI
$
$ perl invoice.pl PIRELLI HYUNDAI
990001  HYUNDAI
990002  PIRELLI
$
$

This User Gave Thanks to durden_tyler For This Post:
chetanojha
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Combine and complete multiple CSV files based on 1 parameter

I have to create a new CSV file based on the value listed on the 3rd column from different CSV files. This is what I need: 1. I should substitute the first column from each file, excluding the headers, with the file name InputXX. 2. Then, I need to look for rows with 0 on the third column in... (7 Replies)
Discussion started by: Xterra
7 Replies

2. Shell Programming and Scripting

Replace multiple file by passing parameter value

Hello All, I want to change date part in file name to yesterday date in the file name. example file name file-12122017-06-30-41.dat want file-12112017-06-30-41.dat I am doing like below. Below it is not changing the filename. Actually it is not parsing the $today and $yesterday value in... (1 Reply)
Discussion started by: looney
1 Replies

3. Shell Programming and Scripting

Multiple Parameter search script - OLD Post

Dear Forum Member, Hope you are well. I am sending this email to your reply to one of my post in the forum. I raised an help request from all of you regarding an issue I was facing. The original thread is here ... (3 Replies)
Discussion started by: chetanojha
3 Replies

4. Shell Programming and Scripting

Multiple file and single parameter file

Hi Team, In our project we have written below 2 scripts like Script1: Shell script start & END Begin Audit process - uses teradata bteq END Audit Process Script 2: Environemtal variable file different Now Client ask to change this requirement and need below files: Script1:... (1 Reply)
Discussion started by: tusharzaware1
1 Replies

5. Shell Programming and Scripting

Read in search strings from text file, search for string in second text file and output to CSV

Hi guys, I have a text file named file1.txt that is formatted like this: 001 , ID , 20000 002 , Name , Brandon 003 , Phone_Number , 616-234-1999 004 , SSNumber , 234-23-234 005 , Model , Toyota 007 , Engine ,V8 008 , GPS , OFF and I have file2.txt formatted like this: ... (2 Replies)
Discussion started by: An0mander
2 Replies

6. UNIX for Dummies Questions & Answers

Search file and print everything except multiple search terms

I'm trying to find a way to search a range of similar words in a file. I tried using sed but can't get it right:sed 's/\(ca01\)*//'It only removes "ca01" but leaves the rest of the word. I still want the rest of the information on the lines just not these specific words listed below. Any... (3 Replies)
Discussion started by: seekryts15
3 Replies

7. Shell Programming and Scripting

Perl search csv fileA where two strings exist on another csv fileB

Hi I have two csv files, with the following formats: FileA.log: Application, This occured blah Application, That occured blah Application, Also this AnotherLog, Bob did this AnotherLog, Dave did that FileB.log: Uk, London, Application, datetime, LaterDateTime, Today it had'nt... (8 Replies)
Discussion started by: PerlNewbRP
8 Replies

8. Shell Programming and Scripting

search and replace in csv file

I have csv file where I want the second column has to be replaced with value 1. Source file 919568760477,1,2011-07-11T22:34:27.000+05:30, 919557735692,2,2011-07-11T22:36:16.000+05:30, 917417384969,2,2011-07-11T22:33:26.000+05:30, Final file ... (30 Replies)
Discussion started by: dondilip
30 Replies

9. Shell Programming and Scripting

Appending a parameter value to a .csv file???

Hi I have a date which I get as parameter. I want it to be added as first column in all the rows in a csv file. I have tried the below code but no success. date=$1 awk -F"," '{print $date","$0}' z3.csv > z4.csv Could you tell the correct code for the above req.? How to use code... (1 Reply)
Discussion started by: msp2244
1 Replies

10. Shell Programming and Scripting

Handle Configuration File with same name of Parameter in multiple Sections

Hi I have a config file with multiple section and a parameter with the same name in each section. I need to read each parameter for distinct section. Parameter = 1 .... Parameter = 2 .... Parameter = 4 .... Tried this: grep -m1 '^*ProcessorsNumber' ServiceBrokerFramework.cfg |... (7 Replies)
Discussion started by: potro
7 Replies
Login or Register to Ask a Question