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.

http://stackoverflow.com/questions/1560393/bash-shell-scripting-csv-parsing — Anders Lindahl, Oct 08 '12 at 14:05
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, Oct 08 '12 at 14:11

score 2 · Answer 1 · answered Oct 08 '12 at 14:18

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 08 '12 at 14:18

Jonathan Leffler

730,956
141
904
1,278

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. – Sriharsha Kalluru Oct 08 '12 at 14:23
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). – Jonathan Leffler Oct 08 '12 at 14:34
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. – Sriharsha Kalluru Oct 08 '12 at 14:35
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 Oct 08 '12 at 14:38
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"; }' – Jonathan Leffler Oct 08 '12 at 14:45
The substitutions could probably be simplified to: `gsub( "\"", "" )` – William Pursell Oct 08 '12 at 14:49
It is giving the complete column 2 as mentioned, But what exactly I am looking for is , Lets say if I have a output `Field1 Field2` and if I have the awk command as `awk '{print $2}'` then it will give me output as Field2, The same way I should get the output as `example.test.com \n example2.test.com` for the file file /etc/hosts and it should give the output as `nameserver dns.test.com \n search test.com` for /etc/resolv.conf. The input will be given from the excel sheet in a csv format and the multiple lines will be given in a single cell and out script also should treat them as single. – Sriharsha Kalluru Oct 08 '12 at 15:11

score 2 · Answer 2 · answered Oct 08 '12 at 14:57

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 08 '12 at 14:57

William Pursell

204,365
48
270
300

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! – William Pursell Apr 23 '13 at 14:18

Read a CSV file in bash

2 Answers2