Merge 2 csv files with awk


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge 2 csv files with awk
# 1  
Old 12-15-2010
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
Code:
123$aaa$yyy$zzz
345$xab$yzy$zyz
456$sss$ttt$foo
799$aaa$ggg$dee

file 2
Code:
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

i.e. result will be
Code:
123$aaa$yyy$hhh$zzz
345$xab$yzy$ddd$zyz
456$sss$ttt$xxx$foo

I've found this code with awk:
Code:
awk 'NR==FNR{x[$1]=$2;next}$1 in x&&$2=x[$1]' OFS="$" FS="$" file2 file1

but I can't insert at the right place Smilie

Please help me Smilie

Moderator's Comments:
Mod Comment Please use [code] and [/code]tags when posting code, data or logs etc. to preserve formatting and enhance readability, thanks.

Last edited by loloAix; 12-15-2010 at 11:04 AM.. Reason: code tags
# 2  
Old 12-15-2010
I asume there is a typo or I misunderstood the logic. From your description the output should look like:
Code:
$> join -t\$ -1 1 -2 1 -o 1.1,2.2,1.2,1.3,1.4 f1 f2
123$hhh$xxx$yyy$zzz
345$ddd$xab$yzy$zyz
456$xxx$sss$ttt$foo

In your output example, the row starting with 345 suddenly only has 4 fields instead of 5 but I can't see by what criteria this happens. For the line starting with 456, I can't see where the rule
Quote:
so if the key is the first field, and the result should be file 1 with field 2 from file 2 based on the key
is still correct. There is suddenly ddd from the row before in it and the order doesn't seem to be correct anymore.
# 3  
Old 12-15-2010
The logic is : the second column of the second file must be inserted before the last element of the first file each time the key correspond.

Sorry for my unperfection Smilie
# 4  
Old 12-15-2010
So this will work for you?
Code:
$> join -t\$ -1 1 -2 1 -o 1.1,1.2,1.3,2.2,1.4 f1 f2
123$aaa$yyy$hhh$zzz
345$xab$yzy$ddd$zyz
456$sss$ttt$xxx$foo

# 5  
Old 12-15-2010
no ... nothing is displayed ...
I'm under HP-Solaris ... This should be the same syntax, isn't it ?

If all my elements are like this (in both files):

"123"$"xxx"$"yyy"$"zzz"
"345"$"xab"$"yzy"$"zyz"
"456"$"sss"$"ttt"$foo"

Should I use
Code:
-t\"$"

or just
Code:
-t\$

?

Last edited by loloAix; 12-15-2010 at 12:35 PM..
# 6  
Old 12-15-2010
This does not really make a difference - I tried it on my Debian Linux box and on an AIX box:
Code:
$> join -t"$" -1 1 -2 1 -o 1.1,1.2,1.3,2.2,1.4 f1 f2
"123"$"aaa"$"yyy"$"hhh"$"zzz"
"345"$"xab"$"yzy"$"ddd"$"zyz"
"456"$"sss"$"ttt"$"xxx"$"foo"

And AIX is usually more picky.
The join command should be clear so you can reproduce or alter it yourself? -1 defines the key in file1 (f1) and -2 they key to take from the 2nd file. The digits following -o like 1.3 means from 1st file to display 3rd field.
# 7  
Old 12-16-2010
Yo,

The prob comes from the ".

If I remove it from files it's working ....

---------- Post updated 16-12-10 at 04:31 AM ---------- Previous update was 15-12-10 at 11:34 AM ----------

ok, you give up Smilie

Thanks a lot for your precious help SmilieSmilieSmilieSmilie

---------- Post updated at 04:32 AM ---------- Previous update was at 04:31 AM ----------

Last question about join command.

Is it possible to join with several column from both files ? Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Merge the three csv files as one according to first coloumn.

I have three files with similar pattern i need to merge all the coloumns side by side from all three files according to the first coloumn example as shown below I mentioned 5 coloumns only in example but i have around 15 coloumns in each file. file1: Name,Samples,Error,95RT,90RT... (4 Replies)
Discussion started by: Raghuram717
4 Replies

2. Shell Programming and Scripting

Compare and merge two big CSV files

Hi all, i need help. I have two csv files with a huge amount of data. I need the first column of the first file, to be compared with the data of the second, to have at the end a file with the data not present in the second file. Example File1: (only one column) profile_id 57036226... (11 Replies)
Discussion started by: SirMannu
11 Replies

3. Shell Programming and Scripting

I am trying to merge all csv files from source path into 1 file

I am trying to merge all csv files from source path into one single csv file in target. but getting error message: hadoop fs -cat /user/hive/warehouse/stage.db/PK_CLOUD_CHARGE/TCH-charge_*.csv > /user/hive/warehouse/stage.db/PK_CLOUD_CHARGE/final/TCH_pb_charge.csv getting error message:... (0 Replies)
Discussion started by: cplusplus1
0 Replies

4. UNIX for Dummies Questions & Answers

Merge two csv files using column name

Hi all, I have two separate csv files(comma delimited) file 1 and file 2. File 1 contains PAN,NAME,Salary AAAAA5467D,Raj,50000 AAFAC5467D,Ram,60000 BDCFA5677D,Kumar,90000 File 2 contains PAN,NAME,Dept,Salary ASDFG6756T,Karthik,ABC,450000 QWERT8765Y,JAX,CDR,780000... (5 Replies)
Discussion started by: Nivas
5 Replies

5. Shell Programming and Scripting

Merge CSV files

I have lot of csv file collected from script like below : Name of files (some examples) there are thousands of it: 192.168.0.123_251_18796_1433144473.csv 192.168.0.123_251_18796_1433144772.csv 192.168.0.123_251_18796_1433145073.csv 192.168.0.123_251_18796_1433145372.csvContent of each... (5 Replies)
Discussion started by: rk4k
5 Replies

6. UNIX for Dummies Questions & Answers

Need help combining txt files w/ multiple lines into csv single cell - also need data merge

:confused:Hello -- i just joined the forums. I am a complete noob -- only about 1 week into learning how to program anything... and starting with linux. I am working in Linux terminal. I have a folder with a bunch of txt files. Each file has several lines of html code. I want to combine... (2 Replies)
Discussion started by: jetsetter
2 Replies

7. Shell Programming and Scripting

Merge *.csv files, each in separate sheets

Does anyone know how to Merge *.csv files, each in seperate sheets? (7 Replies)
Discussion started by: frhling
7 Replies

8. Shell Programming and Scripting

Merge CSV files and create a column with the filename from the original file

Hello everyone!! I am not completely new to shell script but I havent been able to find the answer to my problem and I'm sure there are some smart brains here up for the challenge :D. I have several CSV files that I need to combine into one, but I also need to know where each row came from.... (7 Replies)
Discussion started by: fransanchezoria
7 Replies

9. Shell Programming and Scripting

Merge 2 CSV files using sed

Help in writing a script using sed which updates fileOne with the contents from fileTwo Example: Contents of fileOne 1,111111 2,897823 3,235473 4,222222 Contents of fileTwo 1,111111,A,1,2 4,222222,A,2,2 5,374632,A,3,2 6,374654,A,4,2 Final File should be: 1,111111,A,1,2... (9 Replies)
Discussion started by: NewToSed
9 Replies

10. Shell Programming and Scripting

Merge files of differrent size with one field common in both files using awk

hi, i am facing a problem in merging two files using awk, the problem is as stated below, file1: A|B|C|D|E|F|G|H|I|1 M|N|O|P|Q|R|S|T|U|2 AA|BB|CC|DD|EE|FF|GG|HH|II|1 .... .... .... file2 : 1|Mn|op|qr (2 Replies)
Discussion started by: shashi1982
2 Replies
Login or Register to Ask a Question