How to perform row wise operations on a csv file?

Question

So I have two files file.csv and file2.txt.

file1.csv is of the following nature:

aaa,bbb,ccc,ddd
aab,bba,ccd,eee
fff,ggg,hhh,iii
jjj,kkk,lll,mmm

and file2.txt is of the following nature:

aaa
jjj

So I wrote a piece of code, which finds if the contents of file2.txt exist in the first column of file1.csv and if the exist then delete the entire row.

Here is the command:

grep -ivf file2.txt file1.csv>output.csv

so after running this command i get the following outptut:

aab,bba,ccd,eee
fff,ggg,hhh,iii

what I am looking for is an alternate solution to this. I want to open file1.csv read the first column row by row(preferably load it into an array) compare it with file2.txt every time. My actual script si big and i dont want to create any temp files in between so ia m looking to load the contents of the first column of the file1 into an array then compare it with file2.txt and at the end dump the entire array into a csv file.

Answer 1

Note: this is a shell command-line, but using additional tool.

Since CSV is kind of like a database table, tools that let you treat CSVs like database tables and let you perform SQL queries against them can be useful, if you happen to want to do an operation that can be expressed as a SQL query. One such tool is fsql. Assuming you have a header row on your CSV files:

file1.csv:

col1,col2,col3,col4
aaa,bbb,ccc,ddd
aab,bba,ccd,eee
fff,ggg,hhh,iii
jjj,kkk,lll,mmm

file2.txt:

col1
aaa
jjj

Then:

% fsql --add-csv file1.csv --add-csv file2.txt \
    "SELECT file1.* FROM file1 LEFT JOIN file2 ON file1.col1=file2.col WHERE file2.col1 IS NULL"

will result in:

col0,col1,col2,col3
aab,bba,ccd,eee
fff,ggg,hhh,iii

Pro's: you can perform more complex operations without having to deal with complicated cut or bash arrays. Con's: not the fastest solution.

Answer 2

Stricto sensu, about what you asked:

I want to open file1.csv read the first column row by row(preferably load it into an array) compare it with file2.txt every time

You can perform it, this way:

#!/bin/bash

FILE1="/tmp/file1.csv"
FILE2="/tmp/file2.txt"

# Reads each row, one after the other.
while IFS= read -r row; do
  # Creates an array for the row.
  IFS=',' read -r -a rowAsArray <<< "$row"

  # Extracts the first column of the row.
  firstColumn="${rowAsArray[0]}"

  # Checks if this column exists in the filter file.
  # If it is NOT the case, prints the row which is kept.
  [ $( grep -ce "^$firstColumn$" "$FILE2" ) -eq 0 ] && echo "${rowAsArray[1]},${rowAsArray[3]}"

done < "$FILE1"

Edit:

• I added comments to explain the script
• in this version, you have the array like you want

• at the end you can adapt which columns you want to show

  • for all of them, simply use "${rowAsArray[@]}"
  • for specific ones, like in my instance, use the corresponding index, and add which you want "${rowAsArray[2]}" ...