awk read column csv and search in other csv


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk read column csv and search in other csv
# 1  
Old 06-30-2012
awk read column csv and search in other csv

hi, someone to know how can i read a specific column of csv file and search the value in other csv columns if exist the value in the second csv copy entire row with all field in a new csv file. i suppose that its possible using awk but i m not expertise thanks in advance
# 2  
Old 06-30-2012
Perhaps you could provide some sample input and corresponding output. That would help a lot.
# 3  
Old 06-30-2012
If I read your description correctly, you wish to take a value from column n, and if it appears in any other column in the row, to print that row. For instance:

Code:
1,43,4,2,4,3,43,69,yes

This row would be printed if the value in column 2 were found (which it is) in any other column.

If this is correct, then this awk should work:

Code:
awk  -F ,  ' split( "," $0 ",", a, "," $2 "," ) > 2'  input-file >output-file

Change the value in red to be the column number (1 based) for the column you wish to use the value from.

It's possible that you want to look for the value from column n in another file, not in the same row of file1. If that is the case, please indicate that there are two files involved and it would help, as elixir_sinari has said, to have a sample of input and desired output.
# 4  
Old 06-30-2012
thanks for reply, yes eg.


field1 field2 field3 field4 field5
1 file 01 011 022 045 078
02 087 045 034 789
7 3 44 55 004


2 file 006 011 045 0678 056
056 023 065 43 734
3 876 980 311 888
1 3 65 22 23

3 file 01 011 022 045 078

7 3 44 55 004

in file 1 if value of column field 2 is found in column field 2 of file 2 write entire row of file 2 in file 3.

sorry for bad english.
thanks again
# 5  
Old 07-01-2012
Code:
awk 'FNR==NR{a[$2]=$0;next} $2 in a{print a[$2]}' file1 file2 > file3

# 6  
Old 07-01-2012
thanks a lot i try your script and more later i post response thank you very much

forza italia
# 7  
Old 07-01-2012
Quote:
Originally Posted by elixir_sinari
Code:
awk 'FNR==NR{a[$2]=$0;next} $2 in a{print a[$2]}' file1 file2 > file3

This will print the last matching record from file 1, while I think the desire is to have the record from file 2 printed. Small tweek to do that instead.

Code:
awk 'FNR==NR{a[$2]=1; next} $2 in a ' file1 file2 > file3

There is also the chance that the desire is to print the line from file 2 only if the corresponding line from file 1 has a matching value. The code above will print any line from file 2 that has a value in field 2 that matches any line in file 1 with the same value in column 2. If the desire is to print records from file 2 that match both the value in the field, and the same record number, then something like this should work:

Code:
awk -F , -v f1=file-1 -v f2=file-2 '
    BEGIN {
        col = 2                     # desired field to match -- change if needed
        while( (getline<f1) > 0 )
        {
            f12 = $(col);
            if( (getline <f2) > 0 && $(col) == f12 )
                print;
        }
    }
' >file-3

This code assumes the fields are comma separated as mentioned in the original post.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

Example: I have files in below format file 1: zxc,133,joe@example.com cst,222,xyz@example1.com File 2 Contains: hxd hcd jws zxc cst File 1 has 50000 lines and file 2 has around 30000 lines : Expected Output has to be : hxd hcd jws (5 Replies)
Discussion started by: TestPractice
5 Replies

2. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

3. Shell Programming and Scripting

Read in 2-column CSV, output many files based on field

Is there a way to read in a two-columned CSV file, and based on the fields in 1st column, output many different files? The input/output looks something like: input.csv: call Call Mom. call Call T-Mobile. go Go home. go Go to school. go Go to gas station. play Play music. play Play... (4 Replies)
Discussion started by: pxalpine
4 Replies

4. 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

5. Shell Programming and Scripting

CSV to SQL insert: Awk for strings with multiple lines in csv

Hi Fellows, I have been struggling to fix an issue in csv records to compose sql statements and have been really losing sleep over it. Here is the problem: I have csv files in the following pipe-delimited format: Column1|Column2|Column3|Column4|NEWLINE Address Type|some descriptive... (4 Replies)
Discussion started by: khayal
4 Replies

6. Shell Programming and Scripting

awk: Transpose csv row to column.

Hello, am I new to awk, and I am tryint to: INPUT FILE: "73423555","73423556","73423557","73423558","73423559" OUTPUT FILE: 73423555 73423556 73423557 73423558 73423559 My useless code so far: #!/bin/awk -F ',' BEGIN { i=0; } (8 Replies)
Discussion started by: drbiloukos
8 Replies

7. UNIX for Dummies Questions & Answers

Writing awk script to read csv files and split them

Hi Here is my script that calls my awk script #!/bin/bash set -x dir="/var/local/dsx/csv" testfile="$testfile" while getopts " f: " option do case $option in f ) testfile="$OPTARG";; esac; done ./scriptFile --testfile=$testfile >> $dir/$testfile.csv It calls my awk... (1 Reply)
Discussion started by: ladyAnne
1 Replies

8. Shell Programming and Scripting

Read CSV column value based on column name

Hi All, I am newbie to Unix I ve got assignment to work in unix can you please help me in this regard There is a sample CSV file "Username", "Password" "John1", "Scot1" "John2", "Scot2" "John3", "Scot3" "John4", "Scot4" If i give the column name as Password and row number as 4 the... (3 Replies)
Discussion started by: JohnGG
3 Replies

9. UNIX for Dummies Questions & Answers

read a line from a csv file and convert a column to all caps

Hello experts, I am trying to read a line from a csv file that contains '.doc' and print the second column in all caps. e.g. My csv file contains: Test.doc|This is a Test|test1|tes,t2|test-3 Test2.pdf|This is a Second Test| test1|tes,t2|t-est3 while read line do echo "$line" |... (3 Replies)
Discussion started by: orahi001
3 Replies

10. Shell Programming and Scripting

use awk to read variable length csv

Any help to read the contents of a variable length csv ....??(using awk) The csv mite look like this : anjali,ram,rahul,mohini,sam,.... and so on ... I need to pick up each name.. Thanks in advance SD (3 Replies)
Discussion started by: shweta_d
3 Replies
Login or Register to Ask a Question