CODEWITHSUNDEEP
linuxjoincsv
Ran
Ran

Reputation: 3533

Linux - join 2 CSV files

I have 2 CSV files:

file_1 columns: id,user_id,message_id,rate
file_2 columns: id,type,timestamp

The relation between the files is that file_1.message_id = files_2.id.

I want to create a 3rd file that will have the following columns:

file_1.id,file_1.user_id,file_1.message_id,file_1.rate,file_2.timestamp

Any ideas on how to do this in Linux?

Upvotes: 13

Views: 9123

Answers (4)

dogbane
dogbane

Reputation: 274898

You can use the join command like this:

join -t, -1 3 -2 1 -o 1.1 1.2 1.3 1.4 2.3 <(sort -t, -k 3,3 file1) <(sort file2)

It first sorts the files (file1 is sorted by the 3rd field) and then joins them using the 3rd field of file1 and the 1st field of file2. It then outputs the fields you need.

Upvotes: 11

jaypal singh
jaypal singh

Reputation: 77185

With awk you can try something like this - 

awk -F, 'NR==FNR{a[$3]=$0;next} ($1 in a){print a[$1]","$3 > "file_3"}' file_1 file_2

Test:

[jaypal:~/Temp] cat file_1     # Contents of File_1
id,user_id,message_id,rate
1,3334,424,44

[jaypal:~/Temp] cat file_2     # Contents of File_2
id,type,timestamp
424,rr,22222

[jaypal:~/Temp] awk -F, 'NR==FNR{a[$3]=$0;next} ($1 in a){print a[$1]","$3 > "file_3"}' file_1 file_2

[jaypal:~/Temp] cat file_3     # Contents of File_3 made by the script
1,3334,424,44,22222

Upvotes: 0

Benoit
Benoit

Reputation: 79243

Seems to be a job for SQLite. Using the SQLite shell:

 create table f1(id,user_id,message_id,rate);
 create table f2(id,type,timestamp);

 .separator ,
 .import 'file_1.txt' f1
 .import 'file_2.txt' f2

 CREATE INDEX i1 ON f1(message_id ASC); -- optional
 CREATE INDEX i2 ON f2(id ASC);         -- optional

 .output 'output.txt'
 .separator ,

 SELECT f1.id, f1.user_id, f1.message_id, f1.rate, f2.timestamp
   FROM f1
   JOIN f2 ON f2.id = f1.message_id;

 .output stdout
 .q

Note that if there is a single error in the number of commas in a single line the import stage will fail. You can prevent the rest of the script from running with .bail on at the script beginning.

If you want unmatched ids you can try:

SELECT f1.* FROM f1 LEFT JOIN f2 on f2.id = f1.message_id WHERE f2.id IS NULL

Which will select every row from f1 for which no corresponding row in f2 has been found.

Upvotes: 4

ugoren
ugoren

Reputation: 16449

You can try this:
1. Change all lines to start with the key:

awk -F',' { print $3 " file1 " $1 " " $2 " " $4 } < file1 >  temp
awk -F',' { print $1 " file2 " $2 " " $3 }        < file2 >> temp

Now the lines look like:

message_id file1 id user_id rate
id file2 type timestamp

  1. Sort temp by the first two columns. Now related lines are adjacent, with file1 first

    sort -k 1,1 -k 2,2 < temp > temp2

  2. Run awk to read the lines. In file1 lines save the fields, in file2 lines print them.

Upvotes: 0

Related Questions