1

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?

  • Can you give some sample input? – cuonglm Sep 15 '14 at 16:45
  • Sure. You can download the sample file via this link: drive.google.com/file/d/0B3-WolEQUW6yM3NZUGhHaVFmdFk/… the sample file has 10 columns. thanks – QY Luo Sep 15 '14 at 17:01
  • I had a problem with your link. Can you please just post a small example in your question? – G-Man Sep 15 '14 at 17:24
  • sorry guys, just fixed the link. please try again. thx! – QY Luo Sep 15 '14 at 17:26
  • guys, since HalosGhost edited the question with a perfect input example, I am going to take the link down. thank. – QY Luo Sep 15 '14 at 20:42
2

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] }'
  • this works! G-man, you are the man. thx – QY Luo Sep 15 '14 at 18:04
  • @StéphaneChazelas: (1) “Note that you've got no guarantee of order with for (i in max) …” Good point. (2) “That would also cover for columns with only empty fields.” Huh? My current solution handles columns with only empty fields. – G-Man Sep 15 '14 at 20:22
  • @StéphaneChazelas: I applied your suggestion, however there is nothing returned. Here is the code, am I making any mistake? awk -F, 'NR!=1 { 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] }' 2014-07-08.csv – QY Luo Sep 15 '14 at 21:09
  • @QYLuo: You need to add code to set max_NF; see my edit. – G-Man Sep 15 '14 at 21:16
  • regarding empty columns, yes, sorry, I had overlooked the fact that accessing max[i] instantiates the array element. Note that length(max) will give you max_NF in GNU awk (but GNU awk only). – Stéphane Chazelas Sep 15 '14 at 21:43
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.