Read a CSV file in bash

Question

I have a requirement to read the CSV file in shell, Well I am ok with the CSV file having single line in a cell. But if we have multiple lines in cell of CSV file then I am unable to delimit the the CSV file.

Filename            Lines
/etc/hosts          example.test.com
                    example2.test.com
/etc/resolv.conf    nameserver dns.test.com
                    search test.com

I will take input from the user in a CSV file and have to add the given lines to the mentioned files. Here there are multiple lines in each cell of a CSV file and If I try to cat the file it is giving in a different order.

[user2@mon ~]$ cat test2.csv
"Filename","Lines"
"/etc/hosts","example.test.com"
,"example2.test.com"
"/etc/resolv.conf","nameserver dns.test.com"
,"search test.com"

Is there any way we can read the multiple lines from that file and number of lines is not same in all the time.

The data file is given; what do you want to do with it in bash? Reformat it as shown on standard output? Or stash the data in a bash array? Or something else? — Jonathan Leffler
– Jonathan Leffler, Commented Oct 8, 2012 at 14:11

Jonathan Leffler · Accepted Answer · 2012-10-08 14:18:12Z

2

This might be what you're after:

awk -F, '{ sub(/^"/, "", $1); sub(/"$/, "", $1);
           sub(/^"/, "", $2); sub(/"$/, "", $2);
           printf "%-20s  %s\n", $1, $2;
         }'

It may well be possible to compress the substitute operations if you spend more time manual bashing. This is fragile as a solution (most solutions not using code specialized for dealing with CSV format are fragile); it fails horribly if a comma appears inside any of the quote-enclosed fields.

Applied to your data, it yields:

Filename              Lines
/etc/hosts            example.test.com
                      example2.test.com
/etc/resolv.conf      nameserver dns.test.com
                      search test.com

Other possible tools to manipulate CSV format data reliably include:

Perl plus Text::CSV module
csvfix.

If this is not what you are looking for, please clarify the question.

answered Oct 8, 2012 at 14:18

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sriharsha Kalluru Over a year ago

Thanks for the input and it is really helpful and input will have almost all the special characters.. Is there any way from other programming languages we can achieve that one may be like perl. Is there any way we can directly call the Cell number of a CSV file would be really helpful.

Jonathan Leffler Over a year ago

Most CSV files do not own a cell phone, so you can't call them on it :D I'm quite not sure what you're after. However, if you're looking for line 78, column 4 of a CSV file that can contain arbitrary data including commas within quoted fields etc, then you need a specialized CSV-aware module. I've listed 2 options in the answer. Of the two, I would probably use Perl but I've been using Perl for about 20 years, so I'm very used to it. The csvfix program is written in C++ and can do many very useful tricks; I'm not sure whether it would manage row 79, column 4 though (but it might well do so).

Sriharsha Kalluru Over a year ago

Thanks Jonathan, Would you please let me know how we can do this one with perl. In my view Cell Number is nothing but the coloumn number.

Jonathan Leffler Over a year ago

Please be concrete; what exactly do you want? All the values in column 2, including the header line, printed to standard output, with the quotes around the field value, if any, removed?

Jonathan Leffler Over a year ago

This Perl 'one-liner' will print column 2 of even the most complex CSV formatted file:

perl -MText::CSV -e '$csv=Text::CSV->new({binary=>1})or die;while(my$row=$csv->getline(STDIN)){print"$row->[1]\n"; }' <data

. Tested on your data; produced the output of column 2.

|

William Pursell · Accepted Answer · 2012-10-08 14:57:45Z

2

Assuming your input is as basic as your example, you might be able to get away with simply doing:

sed 's/^,/ ,/' test2.csv | tr -d \" | column -s, -t

answered Oct 8, 2012 at 14:57

William Pursell

214k49 gold badges279 silver badges317 bronze badges

1 Comment

William Pursell Over a year ago

What's with the downvote? Clearly, this solution makes no claim at robustness or reliability, and downvoting a 6 month old solution without a comment is just plain rude!

Collectives™ on Stack Overflow

Read a CSV file in bash

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related