0

so i have two files

file1.csv (about 4000 rows)

1234 yxds
4352 fsfk
8765 fdgj
7874 hffe
9038 fjro
8297 fhjs

and file2.cvs (about 50 rows)

1234 "number" "date" "OS"
4352 "number" "date" "OS"
8765 "number" "date" "OS"
8297 "number" "date" "OS"

file 3 should look like this:

1234 yxds
4352 fsfk
8765 fdgj
8297 fhjs

So I only want the entries that exist in file2.csv but with column2 of file1.csv

Essentially I want to write a script compares file1.csv to a different file2.csv

So far I tried join and awk but with no success. This is what i tried so far :

join -t, -12 -o 1.1,2.1,2.2 file1.csv file2.csv > file 3.csv

and

awk 'NR==FNR{a[$1]=$2;next}{if ($1 in a && a[$1] != $2)print;}' FS="|" file1.csv file2.csv > file3.csv
  • just updated the post, thanks for the heads up! – Sotey Jun 11 at 10:27
  • 1
    Does the output have to be in the same order as the input files? And if so, which one? – Sparhawk Jun 11 at 10:29
  • Are both the file de-limited by |? – Inian Jun 11 at 10:30
  • the order doesn't matter, just that every entry in file2.csv matching an entry in file1.csv gets column 2 of file1.csv written in file3.csv – Sotey Jun 11 at 10:31
  • I edited a whole bunch of minor typos in your question, but are your commands really correct? i.e. > file 3.csv isn't going to work. – Sparhawk Jun 11 at 10:31
0

Make 2 files f1 and f2 with the required content and run the following (It will work if you want to include 8297 in the output)

sort -k1 f1 > ff1
sort -k1 f2 > ff2
join ff1 ff2 | cut -d ' ' -f1 > t1.txt
join ff1 ff2 | cut -d ' ' -f2 > t2.txt
paste t1.txt t2.txt > finaloutput.out
rm f1 f2 ff1 ff2

finaloutput.out will contain the required output

  • I wonder a bit why you don't use cut -f1,2 instead of using 2xcut+ paste. – pLumo Jun 11 at 10:22
  • I didn't know that multiple columns could be extracted this way.. Thanks @pLumo – vib_29 Jun 11 at 10:28
  • unfortunately all the files end up being empty – Sotey Jun 11 at 13:11
  • @Sotey edit the last line to this one rm ff1 ff2 – vib_29 Jun 11 at 13:29
  • no difference sadly – Sotey Jun 11 at 13:36
0

Use join on sorted files:

join -o 1.1,1.2 <(sort file1.csv) <(sort file2.csv) > file3.csv
  • -o 1.1,1.2 means output file1.field1 and file1.field2

Output:

1234 yxds
4352 fsfk
8297 fhjs
8765 fdgj
  • It keeps giving me an error: syntax error near unexpected token `(' It doesn't seem to get the included sort command. I'm working in terminal on mac, not linux, if that helps – Sotey Jun 11 at 10:23
  • yes it matters. <(...) is bash syntax, I put that because you added bash as a tag to your question. – pLumo Jun 11 at 10:31
  • Thanks for the clarification, I'm new to the forum – Sotey Jun 11 at 10:34
  • Even if i sort the files before, the output file ends up being empty – Sotey Jun 12 at 10:25
0

Using Miller (https://github.com/johnkerl/miller)

mlr --nidx join -j 1 --lp 2 -f input_01.csv then unsparsify then cut -f 1,22 input_02.csv

You will have

1234 yxds
4352 fsfk
8765 fdgj
8297 fhjs
  • 1
    Thanks!! i would prefer to do it without a 3rd party application, since I also want to share it with some of my users without them having to install something first – Sotey Jun 11 at 15:13
  • @Sotey I do not know what kind of users you have: install Miller is very easy and give you a tool with a lot of great features. I'm adding this comment because it seems that you haven't found a solution – aborruso Jun 12 at 7:43

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.