mjhjmtu

I want to do a check the number of columns in a CSV file before processing it. The problem is that the delimiter (comma) also occurs in the text of some fields and because of that I cannot parse it correctly and I receive too many columns.

Eg:

~new file: 12345~,~125.5~,,,~ example (45), case (20)~,,

7 columns

1. ~new file: 12345~


2. ~125.5~


3. empty


4. empty


5. ~ example (45), case (20)~


6. empty


7. empty

The problem is the comma inside ~example (45), case (20)~ in 5^th column.

I tried to replace delimiter , with ; using sed but I had to do more than one iteration.

I would like a general rule that will match multiple cases with a more optimal approach.

I assume , is the columns delimiter, I would just run the command column:

echo "~new file: 12345~,~125.5~,,,~ example (45), case (20)~,," | column -t -s','

or

column -t -s',' file

output:

~new file: 12345~ ~125.5~ ~ example (45) case (20)~ 

Using awk you would do:

awk -F, ' gsub(/~[^~]*~/,""); print NF ' infile

for an input like:

~new file: 12345~,~125.5~,,,~ example (45), case (20)~,,
,~125.5~,,,~ example (45), case (20)~

It will return:

7
5

In gsub(/~[^~]*~/,""), we are replacing every pattern started from a ~ till the next ~ seen (like ~...~) with empty string; see below:

awk -F, ' gsub(/~[^~]*~/,""); print $0 ' infile
,,,,,,
,,,,

This assume that there is no inner ~ like ,~some~thing~, in your input.

then print NF will print the number of fields according to the specified filed separator -F .

This looks like a CSV file that is using comma as field delimiters and tilde as quoting character.

Using a proper CSV parser, like the one provided by the Text::CSV Perl module:

perl -MText::CSV -e 'print scalar(@Text::CSV->new(quote_char=>"~")->getline(*STDIN))' <file.csv

This would read the first line of the CSV file file.csv and print the number of columns in it. We instantiate a parser that understands that the quote character is a tilde before reading the first line with this parser. The getline() method on this parser would read a line from the given filehandle and return a reference to an array of data, one item per parsed column. The print scalar(...) is a fairly common way to print the length of an array in Perl.

Another way, using the CSVKit command line CSV parser toolkit:

csvstat -n -q '~' <file.csv | wc -l

or equivalently, using long options,

csvstat --names --quotechar '~' <file.csv | wc -l

This would likewise read the first line of the input file and return a listing of the headers (the first line of a CSV file usually contains column headers), one per line. The wc -l counts the number of lines returned.

When you later parse the CSV file, I suggest that you use one of these approaches, or look for a proper parser in the programming language that you are most used to. awk and sed may be used on simple CSV data, but in this case your data is using some of the CSV format features that these tools would have difficulties to cope with without taking great care.