UNIX Command to Match columns from two csv files


 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers UNIX Command to Match columns from two csv files
# 1  
Old 08-08-2019
UNIX Command to Match columns from two csv files

I am joining two CSV files based on 'Server_Name' column, 1st column of first file and 2nd column of second file.
If matches, output 1st and 2nd column from first file, 3rd,4th,5th,6th columns from second file.

I am expecting output CSV file as below.

Could you please send me help me with command, I am trying below command, something is not working. Please help

Code:
awk -F, 'BEGIN {FS = OFS=","} NR==FNR {h[$2] = $1; next} $1  {print h[$1],$1,$2,$3,$4,$5}' ServerInfo.csv ConfigFormat.csv  > outfile.csv


File1 : ServerInfo.csv

Server_Name,Temp_Space,Temp_Space_Used,RAM_Memory,RAM_Memory_Available
LONDON,7.9G,3%,47G,25G
JAPAN,7.9G,4%,47G,28G
KENYA,7.9G,2%,47G,21G
POLAND,7.9G,4%,47G,25G

File2: ConfigFormat.csv

REGION,Server_Name,Temp_Space,Temp_Space_Used,RAM_Memory,RAM_Memory_Available
DEV,LONDON,,,,
TEST,JAPAN,,,,
PREPROD,KENYA,,,,
PROD,POLAND,,,,


Output File expected

REGION,Server_Name,Temp_Space,Temp_Space_Used,RAM_Memory,RAM_Memory_Available
DEV,LONDON,7.9G,3%,47G,25G
TEST,JAPAN,7.9G,4%,47G,28G
PREPROD,KENYA,7.9G,2%,47G,21G
PROD,POLAND,7.9G,4%,47G,25G


Thank you,
Anand
# 2  
Old 08-08-2019
thank you
# 3  
Old 08-08-2019
Code:
awk -F, 'NR==FNR {h[$1]=$0;next} h[$2] {print $1, h[$2]}' ServerInfo.csv OFS=, ConfigFormat.csv > outfile.csv


Last edited by rdrtx1; 08-08-2019 at 03:33 PM.. Reason: updated to remove BEGIN section.
# 4  
Old 08-08-2019
Thank you,
I am getting empty output file.
# 5  
Old 08-08-2019
Any other commands can we get output? Please help
# 6  
Old 08-08-2019
Did you consider the join command, made for exactly this case? Like
Code:
join -1 1 -2 2 -t, ServerInfo.csv ConfigFormat.csv

# 7  
Old 08-08-2019
Both input CSV files contain column names in first row. I tried join command, getting below error

join: file 2 is not in sorted order
join: file 1 is not in sorted order
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Data match 2 files based on first 2 columns matching only and join if match

Hi, i have 2 files , the data i need to match is in masterfile and i need to pull out column 3 from master if column 1 and 2 match and output entire row to new file I have tried with join and awk and i keep getting blank outputs or same file is there an easier way than what i am... (4 Replies)
Discussion started by: axis88
4 Replies

2. UNIX for Dummies Questions & Answers

Match the columns between two files and output

Hi Help, I have two files namely a.txt and b.txt a.txt looks like a.txt 1 2 2 1 3 3 2 4 4 4 5 6 6 7 7 b.txt looks like, b.txt 1 2 1 1 3 2 2 4 3 3 4 4 4 5 5 (2 Replies)
Discussion started by: Indra2011
2 Replies

3. Shell Programming and Scripting

Import 2 columns from 8 .csv files into pandas df (side by side) and write a new csv

I have 8 .csv files with 16 columns and "n" rows with no Header. I want to parse each of these .csv and get column and put the data into a new.csv. Once this is done, the new.csv should have 16 columns (2 from each input.csv) and "n" rows. Now, I want to just take the average of Column from... (3 Replies)
Discussion started by: Zam_1234
3 Replies

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

5. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

6. Shell Programming and Scripting

Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns. I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows'... (5 Replies)
Discussion started by: bkane3
5 Replies

7. Shell Programming and Scripting

Comparing Select Columns from two CSV files in UNIX and create a third file based on comparision

Hi , I want to compare first 3 columns of File A and File B and create a new file File C which will have all rows from File B and will include rows that are present in File A and not in File B based on First 3 column comparison. Thanks in advance for your help. File A A,B,C,45,46... (2 Replies)
Discussion started by: ady_koolz
2 Replies

8. Shell Programming and Scripting

Match the columns between 2 files

I have two files I want to match ids in the 5th column of the file 1 with the first column of the file 2 and get the description for the matched ids as shown in the output sno nm no nm2 ID 1 cc 574372 yyyi |6810|51234| 2 bb 119721 nmjk |6810|51234|51179| ... (4 Replies)
Discussion started by: raj_k
4 Replies

9. Shell Programming and Scripting

Match columns several files

Hey fellas! Here come my problem. I appreciate if you have a look at it. I have several files with following structure: file_1:1 21 4 45 file_2:2 31 4 153 6 341 and so on... and I have a 'reference' file look like this: File_ref:A 1 B 2 C 3 (5 Replies)
Discussion started by: @man
5 Replies

10. UNIX for Advanced & Expert Users

Unix Bash: substitute columns in .csv using other .csv columns

Hi All, I have two .csv's input.csv having values as (7 columns) ABC,A19907103,ABC DEV YUNG,2.17,1000,2157,07/07/2006 XYZ,H00213850,MM TRUP HILL,38.38,580,23308,31/08/2010 output.csv having (25 columns) A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y... (4 Replies)
Discussion started by: abhivyas
4 Replies
Login or Register to Ask a Question