Celeb Spill Daily

Home / news

How do I join two CSV files but only where 2nd file matches?

David Craig |

I have 2 CSV files:

Objects File:

IP,MASK,DESCRIPTION
10.10.3.94,255.255.255.255,Rob
10.10.3.95,255.255.255.255,Mark
10.10.3.96,255.255.255.255,John

Services File:

DESCRIPTION,OrgIP,Service
Rob,1.1.1.1,Purple
John,2.2.2.2,Green
Mark,3.3.3.3,Yellow

The objects file has 3000 lines in, the service file has around 500.

I want to create a new file that has the all lines from services with objects' fields added on where a match in the description is found. So the desired output would look like:

DESCRIPTION,OrgIP,Service,IP,MASK
Rob,1.1.1.1,Purple,10.10.3.94,255.255.255.255
John,2.2.2.2,Green,10.10.3.96,255.255.255.255
Mark,3.3.3.3,Yellow,10.10.3.95,255.255.255.255
2

4 Answers

Provided you have sqlite3 and python installed on your system, you could use this .

The relevant sql command would be:

./sqlet.py -d',' -A file1.txt -B file2.txt 'select A3,B2,B3,A1,A2 from A LEFT JOIN B ON A3=B1;' | sqlite3

This requires that you remove the headers from the two files. The sqlet script must be extracted in the same folder where those two files are or modified accordingly.

I tried it on your example extract. It works:

bruni@bruni-Inspiron-5547:~/Downloads$ cat file1.txt
10.10.3.94,255.255.255.255,Rob
10.10.3.95,255.255.255.255,Mark
10.10.3.96,255.255.255.255,John
bruni@bruni-Inspiron-5547:~/Downloads$ cat file2.txt
Rob,1.1.1.1,Purple
John,2.2.2.2,Green
Mark,3.3.3.3,Yellow
bruni@bruni-Inspiron-5547:~/Downloads$ ./sqlet.py -d',' -A file1.txt -B file2.txt 'select A3,B2,B3,A1,A2 from A LEFT JOIN B ON A3=B1;' | sqlite3
Rob,1.1.1.1,Purple,10.10.3.94,255.255.255.255
Mark,3.3.3.3,Yellow,10.10.3.95,255.255.255.255
John,2.2.2.2,Green,10.10.3.96,255.255.255.255
2

Using join:

join --header -t, -11 -23 -a1 <(awk 'NR > 1 {print | "sort -t, -k1"; next} 1' services) <(awk 'NR > 1 {print | "sort -t, -k3"; next} 1' objects)
join --header -t, -11 -23 -a1 <( awk ' NR > 1 { print | "sort -t, -k1"; next } 1 ' services
) <( awk ' NR > 1 { print | "sort -t, -k3"; next } 1 ' objects
)
  • --header: treats the first line in each file as field headers, printing them without trying to pair them
  • -t,: sets , as the input and output field separator
  • -11: joins on field #1 of services
  • -23: joins on field #3 of objects
  • -a1: also prints unpairable lines from services
  • <(awk 'NR > 1 {print | "sort -t, -k1"; next} 1' services): sorts services on column 1 excluding the header
  • <(awk 'NR > 1 {print | "sort -t, -k3"; next} 1' objects): sorts objects on column 3 excluding the header
% cat objects
IP,MASK,DESCRIPTION
10.10.3.94,255.255.255.255,Rob
10.10.3.95,255.255.255.255,Mark
10.10.3.96,255.255.255.255,John
% cat services
DESCRIPTION,OrgIP,Service
Rob,1.1.1.1,Purple
John,2.2.2.2,Green
Mark,3.3.3.3,Yellow
% join --header -t, -11 -23 -a1 <(awk 'NR > 1 {print | "sort -t, -k1"; next} 1' services) <(awk 'NR > 1 {print | "sort -t, -k3"; next} 1' objects)
DESCRIPTION,OrgIP,Service,IP,MASK
John,2.2.2.2,Green,10.10.3.96,255.255.255.255
Mark,3.3.3.3,Yellow,10.10.3.95,255.255.255.255
Rob,1.1.1.1,Purple,10.10.3.94,255.255.255.255
9

If the files are not too huge, how about awk?

$ awk -F, 'NR==FNR {a[$1]=$2 FS $3 FS $4; next} $3 in a {OFS=","; print $3,a[$3],$2}' services objects
DESCRIPTION,OrgIP,Service,IP,MASK
Rob,1.1.1.1,Purple,10.10.3.94,255.255.255.255
Mark,3.3.3.3,Yellow,10.10.3.95,255.255.255.255
John,2.2.2.2,Green,10.10.3.96,255.255.255.255
4

You can do:

join -t, -a2 -11 -23 <(head -1 f1.txt; tail -n +2 f1.txt | \ sort -t, -k1,1) <(head -1 f2.txt; tail -n +2 f2.txt | sort -t, -k3,3)

  • <() is process substitution syntax, bash will replace it with a file descriptor with the output of the command inside it as the content

  • head -1 f1.txt; tail -n +2 f2.txt | sort -t, -k1,1 will sort the first file on first field from second line to rest and the first line is added on top so that we can use it with join. Same goes for f2.txt with the field to sort according to is three.

  • join will just join on first field of f1.txt and third of f2.txt as common fields.

Example:

$ cat f1.txt
DESCRIPTION,OrgIP,Service
Rob,1.1.1.1,Purple
John,2.2.2.2,Green
Mark,3.3.3.3,Yellow
$ cat f2.txt
IP,MASK,DESCRIPTION
10.10.3.94,255.255.255.255,Rob
10.10.3.95,255.255.255.255,Mark
10.10.3.96,255.255.255.255,John
$ join -t, -11 -23 <(head -1 f1.txt; tail -n +2 f1.txt | sort -t, -k1,1) <(head -1 f2.txt; tail -n +2 f2.txt | sort -t, -k3,3)
DESCRIPTION,OrgIP,Service,IP,MASK
John,2.2.2.2,Green,10.10.3.96,255.255.255.255
Mark,3.3.3.3,Yellow,10.10.3.95,255.255.255.255
Rob,1.1.1.1,Purple,10.10.3.94,255.255.255.255
0

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

You Might Also Like