I have a large csv file with these format file 1:


I have another txt file with one column of list of id (header id). file 2:


I want another file that has only data limited to the list of id in file 2

output file:


3 Answers 3


If the files have the common lines in the same order,

join -t, -j1 file1.txt file2.txt


See man join for more information. Basically, join the two files, skipping "unpairable lines". The delimiter is a comma -t,, and join on field 1 -j1.

  • the file 1 is large csv file and is not sorted. file 2 is just list of ids not the total line.
    – motor 30
    Oct 23, 2019 at 23:54
  • @motor30 Please add this information to the question. Is it okay to reorder the files, i.e. join -t, -j1 <(sort file1.txt) <(sort file2.txt)?
    – Sparhawk
    Oct 24, 2019 at 0:16
  • it only give empty file bash-4.2$ join -t, -j1 <(sort file1.txt) <(sort file2.txt)> 3.csv please note that the file 1 is a csv file "file1.csv", I just change it to txt but the same
    – motor 30
    Oct 24, 2019 at 0:34
  • @motor30 Sorry I don't totally understand. In my commands, file1.txt and file2.txt are just dummy file names. You should replace them with the actual names of your files.
    – Sparhawk
    Oct 24, 2019 at 1:03

Using csvjoin from the Python-based csvkit

$ csvjoin -Ic id file\ 1 file\ 2

The -I flag disables type inference so that fields like 008.8 are treated as strings rather than as numerics (which may cause re-formating). This method appears to handle DOS/Windows style line endings transparently.

Alternative quick'n'dirty join using awk:

awk -F, 'NR==FNR {a[$1]; next} $1 in a' file\ 2 file\ 1

If your file has DOS/Windows line endings (CRLF in place of plain LF) then you can try

awk -vRS='\r\n' -F, 'NR==FNR {a[$1]; next} $1 in a' file\ 2 file\ 1

or convert your file first using dos2unix, tr, sed etc.

  • I am working on Linux and it doesn't seem to have this option...I was hoping for awk or grep
    – motor 30
    Oct 23, 2019 at 23:56
  • @motor30 many Linuxes package csvkit now I think (it's certainly available on Debian based systems) however I've added a suggestion with awk Oct 24, 2019 at 0:01
  • awk -F, 'NR==FNR {a[$1]; next} $1 in a' file2.csv file1.csv > 2.csv somehow it only make an empty csv file
    – motor 30
    Oct 24, 2019 at 0:20
  • @motor30 do your CSV files have Windows line endings (CRLF rather than plain LF)? if so, try adding -vRS='\r\n' to the awk options Oct 24, 2019 at 1:26
  • could you please re-write the complete line, thanks
    – motor 30
    Oct 24, 2019 at 14:34

With grep and sed:

You could add a ^ to the beginning of each line and a , to the end of each line of file2 and use grep:

grep -f <(sed 's/^/^/;s/$/,/' file2) file1

Option -f reads the patterns from a file (in our example not from a real file, but from a process substitution <(…)).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .