I have a couple of CSVs'. file1 has a large number of URLs and file2 also has a URL field which have to be matched with file1. Now file1 has a couple of columns I need to add to file2. I have managed to add the header and also able to find the common URLs between the two and get the 2 extra columns into file2. (New columns Issues + Results)

sed -i ' 1 s/.*/&|||Issues|||Results/'

adds the header and the extra columns and the awk script gives me the common URLs. temp.csv now contains matching URLs with appended Issues and reports.

awk 'BEGIN { OFS="\|\|\|"} NR==FNR{a[$1]=$4"|||"$5; next} $2 in a {print $0 OFS a[$2]}' FS=[','] February_2019_completed.csv FS='[|]{3}' cleaned.csv > temp.csv

I need to do this comparison with 5 more files and get all the lines appended with the 2 columns mentioned above. I am trying to merge the new temp.csv file into the cleaned.csv with the lines contaning extra columns into the original cleaned.csv file so that I have the same number of rows again. That can then be compared again with each of the 5 above. The delimiter is three pipes (|||). How do I approach this problem?

  • Have you tried man join? – Marcel Feb 15 at 9:11
  • tried join -i -j2 -t[\|\|\|] --nocheck-order cleaned.csv temp.csv – Mallik Kumar Feb 15 at 9:20

Your Answer

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

Browse other questions tagged or ask your own question.