Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

awk on csv files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers awk on csv files
# 1  
Old 04-18-2019
awk on csv files
wk on csv files
Hi I have two csv files
1 ) keys.csv with data as below
Code:
XX1,offsite
XX2,offsite
XX8,none
XX3,offsite
XX4,onsite
XX7,none
XX5,onsite
XX6,onsite
XX15,none

2) source.csv file with data like

Code:
1,0123,ppp,XX1
2,0122,sss,XX2
3,1239,yyy,XX8
4,567,kkk,XX5
5,999,qqq,XX6
6,1234,sss,XX15
7,2456,ggg,XX3
8,635,ttt,XX4
9,987,www,XX7
10,154,sqt,XX7
11,1000,sss,XX15
12,1231,sss,XX15
13,1230,sss,XX15

output needed is after comparing files the 2nd field from first file should get appended to where ever the 1st field from file 1 matches with field 3 from second file
i.e sample output

Code:
1,0123,ppp,XX1,offsite
2,0122,sss,XX2,offsite
3,1239,yyy,XX8,none
4,567,kkk,XX5,onsite
5,999,qqq,XX6,onsite
6,1234,sss,XX15,none
7,2456,ggg,XX3,offsite
8,635,ttt,XX4,onsite
9,987,www,XX7,none
10,154,sqt,XX7,none
11,1000,sss,XX15,none
12,1231,sss,XX15,none
13,1230,sss,XX15,none

tried below it does not work any pointers on what could be wrong ?

Code:
awk -F ',' 'NR == FNR {f[$1] = $2;next} $3 in f { print $0","f[$1]}' keys.csv source.csv

# 2  
Old 04-18-2019
I think you're thinking of the wrong "joining" key.
try this:
Code:
awk -F, 'FNR==NR {f1[$1]=$2;next} $4 in f1 {print $0,f1[$4]}' OFS=, keys.csv source.csv

Last edited by vgersh99; 04-18-2019 at 11:53 AM..
These 2 Users Gave Thanks to vgersh99 For This Post:
Neo zozoo
# 3  
Old 04-18-2019
Thank you Smilie it worked ,my Bad Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Modify csv-files with awk

Hello everyone! I have thousands of csv files I have to import into a Database table. As usually the files aren't perfect. For example they have a different number of columns and some weird columns. The second problem is, that I have to add 3 parts of the filename into 3 rows in the... (6 Replies)
Discussion started by: elRonaldo
6 Replies

2. UNIX for Beginners Questions & Answers

awk assistance - Comparing 2 csv files

Hello all, I have searched high and low for a solution to this, many have come really close but not quite what I'm after. I have 2 files. One contains GUID's, for example: 8121E002-96FE-4C9C-BC5A-6AFF20DACECD 84468F30-F3B7-418B-81F0-0908E80792BF A second file, contains a path to the... (8 Replies)
Discussion started by: tirmUK
8 Replies

3. Shell Programming and Scripting

Filter using awk in CSV files

Hello Gentlemen, Finding difficulties to play with my Input files:confused: . Your guidance will certainly help as always. After converting to csv file from XLSM file, I am getting some extra ""(double quote) characters which I want to terminate inside shell script and process it further. ... (6 Replies)
Discussion started by: pradyumnajpn10
6 Replies

4. Shell Programming and Scripting

awk script to perform an action similar to vlookup between two csv files in UNIX

Hi, I am new to awk/unix and am trying to put together an awk script to perform an action similar to vlookup between the two csv files. Here are the contents of the two files: File 1: Date,ParentID,Number,Area,Volume,Dimensions 2014-01-01,ABC,247,83430.33,857.84,8110.76... (9 Replies)
Discussion started by: Prit Siv
9 Replies

5. Shell Programming and Scripting

Using AWK to match CSV files with duplicate patterns

Dear awk users, I am trying to use awk to match records across two moderately large CSV files. File1 is a pattern file with 173,200 lines, many of which are repeated. The order in which these lines are displayed is important, and I would like to preserve it. File2 is a data file with 456,000... (3 Replies)
Discussion started by: isuewing
3 Replies

6. Shell Programming and Scripting

Merge 2 csv files with awk

I have 2 files pipe delimted and want to merge them based on a key e.g file 1 123$aaa$yyy$zzz 345$xab$yzy$zyz 456$sss$ttt$foo 799$aaa$ggg$dee file 2 123$hhh 345$ddd 456$xxx 888$zzz so if the key is the first field, and the result should be the common key between file 1 and 2 (6 Replies)
Discussion started by: loloAix
6 Replies

7. Shell Programming and Scripting

how to give multiple csv files as input in awk

Hi All, I am new to shell scripting..My problem is i want to give multiple csv files as input to awk script and process the data into one file.. My input file is File1 File2 File3 Product Location Period SalesPrice A x 8/11/2010 ... (7 Replies)
Discussion started by: kvth
7 Replies

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

9. Shell Programming and Scripting

validation of data using filter (awk or other that works...) in csv files

Hello People I have the following file.csv: date,string,float,number,boolean 20080303,abc,1.5,123,Y 20080304,abc,1.2,345,N 20080229,nvh,1.4,098,Y 20080319,ugy,1.9,586,N 20080315,gyh,2.4,345,Y 20080316,erf,3.1,932,N I need to filter the date field where I have a data bigger than I... (1 Reply)
Discussion started by: Rafael.Buria
1 Replies

10. UNIX for Dummies Questions & Answers

csv files (with quoted commas) and awk

I have a file as follows: 1,"This is field 2",3,4,5 2,"This is field 2 it can contain one , comma",3,4,5 3,"This is field 2 it also, can, contain, more",3,4,5 4,"This is field 2 without extra commas",3,4,5 and i pass this through to awk: awk -F, ' { if (... (3 Replies)
Discussion started by: Cranie
3 Replies
Login or Register to Ask a Question