How to read a .csv file with shell command?

Question

I have a .csv file which I need to extract values from. It is formatted like this :

First line of the file (no data)

1;Jack;Daniels;Madrid;484016;

2;Alice;Morgan;London;564127;

etc...

I would need a shell command that read all lines of a specific column within a .csv, compare each with a string and return a value whenever it finds a matching line. In Java i would define it something like :

> boolean findMatchInCSV(String valueToFind, int colNumber, String
> colSeparator)

The separator between columns may indeed change that is why I would like a something quite generic if possible :)

But I need it as a shell command, is that possible ?

Thanks

score 3 · Answer 1 · answered Jun 23 '17 at 12:52

I would need a shell command that read all lines

cat 1.csv                         # read the file

of a specific column within a .csv

cat 1.csv | cut -f5 -d';'         # keep only the field #5 (use ';' as separator)

compare each with a string

# keep only the row where the value of the field is exactly 'foo'
cat 1.csv | cut -f5 -d';' | grep '^foo$'

return a value whenever it finds a matching line.

This last one request is unclear.

The code above displays the searched string (foo) once for each row where it is the value of column #5 (start counting from 1). The columns are separated by ;.

Unfortunately, it doesn't handle quoted strings. If the value in any field contains the separator (;), the CSV format allows enclosing the field value into double quotes (") to prevent the separator character be interpreted as a separator (forcing its literal value).

Thanks, with your explanation I now understand all the steps, the command is way easier than I thought it would be :) Thank you — LostReality, Jun 23 '17 at 12:54

score 1 · Answer 2 · answered Jun 23 '17 at 12:51

1

I assume you're looking for something like

FILE=data.csv
VALUE="$1"
COLNUM=$2
IFS="$3"

while read -r -a myArray
do
    if "$myArray[$COLNUM]"=="$VALUE"; then
        exit 0
    fi
done < tail -n +2 $FILE

exit 1

answered Jun 23 '17 at 12:51

yinnonsanders

1,831
11
28

Michael · Accepted Answer · 2017-06-23T12:48:11.733

0

grep "my_string" file |awk -F ";" '{print $5}'

or

awk -F ";" '/my_string/ {print $5}' file

For 2nd column:

awk -F ";" '$2 ~ /my_string/ {print $5}' file

For exact matching:

awk -F ";" '$2 == "my_string" {print $5}' file

edited Jun 23 '17 at 12:48

answered Jun 23 '17 at 12:37

Michael

5,095
2
13
35

Thanks a lot for those solutions :) – LostReality Jun 23 '17 at 12:51

How to read a .csv file with shell command?

3 Answers3