How to read multiple values from a column of a .csv file in shell script

Question

this is sample csv file :

name,annotations,description
drgreghouse,princeton,"doctor,head"
sheldon,tbbt,"physicist,actor"
chandler,friends,"actor,comedian"

I am trying something like this but it is reading only first values

INPUT="$(pwd)/data.csv"
IFS=','
sed 1d $INPUT |while read name annotations description; do
    echo "$name $annotations $description"
done

O/p -

drgreghouse princeton "doctor
sheldon tbbt "physicist
chandler friends "actor

Expected O/p

drgreghouse princeton doctor,head
sheldon tbbt physicist,actor
chandler friends actor,comedian

@Kunalkishor, or do you want to skip only headers in Input_file and keep rest of the lines in your file? — RavinderSingh13, Sep 23 '20 at 06:55
`IFS` means input field separator. This is the character which gets used by `read` to distinguish the columns. — ceving, Sep 23 '20 at 07:06
@tripleee: I didn't see OP mentioning tab in output. Anyway if that's needed then `BEGIN{FS=OFS="\t"}` is considered better way. — anubhava, Sep 23 '20 at 07:41

tripleee · Answer 1 · 2020-10-14T08:26:55.997

sed and the shell have no concept of CSV files. If you want to process quoted fields in CSV, you have to handle the quoted fields yourself, or switch to a tool which handles them for you.

If your data has no complex quoting, you can replace every non-quoted comma with a different delimiter which doesn't occur in the data (try | maybe) and take it from there. If you just want to convert to space-delimited, try this.

sed -e 1d -e 's/"\([^"]*\)",\|\([^[",]*\),/\1\2 /g' data.csv

Demo: https://ideone.com/sg9crO

To use a different delimiter, change the space after \1\2 to that delimiter. But again, please understand that this quick and dirty regex hack cannot handle the full range of CSV's quoting rules.

As an aside, you don't need $(pwd) to refer to the current directory; relative file names are always resolved relative to your current working directory already.

How to read multiple values from a column of a .csv file in shell script

1 Answers1