Count the maximum character length for all the data fields in a simplified csv file and output to txt

Question

Given a simplified CSV (max one line per row) with many data fields (>50), how can I count the maximum character length for each data field and then export all the counts to a txt file? BTW, I want to ignore the first line of the file which contains the column headings.

For example, given the input

These,are,the,column_headings_which_may_be_very_long_but_they_don't_count
abcdefghij,abcdefghijk,abcdefghijkl,abc
aardvark,bat,cat,dog
ant,bee,cow,abcdefghijklm

The end result could be something like the following, where the first column indicates the data fields in the original file and the second column indicates the maximum length of the field:

1 | 10
2 | 11
3 | 12
4 | 13

i.e., the length of the longest value in column 1 is 10 (abcdefghij), the length of the longest value in column 2 is 11 (abcdefghijk), etc.

I have researched on the site a little bit and found couple ways that can count maximum length in a fairly straightforward manner when a certain data field is specified. For example, use cut and wc commands to count maximum length of the second field in the file:

cut -d, -f2 test.csv | wc -L  

But how can I take the command and loop it over to all the data fields and then output?

Answer 1

If I understand your question correctly, this will do what you want:

awk -F, 'NR!=1 { if (max_NF < NF) max_NF = NF;
                 for (i=1; i<=NF; i++) if (max[i] < length($i)) max[i] = length($i) }
         END   { for (i=1; i<=max_NF; i++) printf "%-2d | %d\n", i, max[i] }'

Answer 2

I don't see your link for the sample file but you can do this by using awk command. if can specify what ever delimiter you may have and exactly what field you need to count.

    awk '{ FS = "," } ; { if(NR!=1) gsub(/"/, "", $2) ; print NR "|" length($2) } ' test.csv

You can redirect this output to any file you want.