1

I have multiple CSV files with many records. The total number of rows should be 134. I have many files, and each rows has its own number of columns (from 15 to 200). I need to sort them according to their number of columns.

I was able to count a file's columns using:

$ awk -F"," '{print NF}' file # 1.csv

... which gives something like:

134
134
134
5
25
133
...

Now, I would like to add these numbers to each row, so that I can later sort my rows according to it. How can I add this information at the beginning of each, and sort?

I'd also like to split the files with value=134 into 1 file other by their respective count.

small INPUT file example (this is 3 rows):

2,"A.B.C.D",50,"SDf3oa701-ab73-a0pcs90","7012218969217-1413752517-32448","SDf3oa701-ab73-a0pcs90","SIP",,"<99999@sipgw5003.com>;tag=70122","<8888888@X.Y.Z.W>",17,0,"00:01:57.827 GMT Oct 20 2014","00:00:00.000 UTC Jan 01 1970","00:01:57.870 GMT Oct 20 2014",3,"sp3",1904,"sp3",1904,"realm_IN","realmTERM_OUT",,,,"::",0,"::",0,,"::",0,"::",0,0,0,0,0,0,0,0,0,0,0,,,,"::",0,"::",0,,"::",0,"::",0,0,0,0,0,0,0,0,0,0,0,,,,"::",0,"::",0,,"::",0,"::",0,0,0,0,0,0,0,0,0,0,0,,,,"::",0,"::",0,,"::",0,"::",0,0,0,0,0,0,0,0,0,0,0,,,"Sw-buildabcd","GMT-03:00",0,"8888888@X.Y.Z.W",,,,,,"X.Y.Z.W:50","A.S.D.F:50","A.S.D.F:50","A.S.D.F:50",,1,2,1,404,"8888888@A.S.D.F",,,4493101
2,"A.B.C.D",50,,,,4493105
2,"A.B.C.D",50,,"88888@B.D.S.E",,,4493106
  • Wow, I spent more time than I thought fixing this question... Next time, please make sure you read our How to ask before posting a question. By the way, I was unable to *clarify" your last sentence. – John WH Smith Nov 25 '14 at 11:25
  • Please edit your question and give us an example of your input file and the output you would like from that example. The solutions will depend on the format of your input files. Most importantly what you have to delimit fields. Are columns separated by spaces? Tabs? Commas? – terdon Nov 25 '14 at 11:28
  • Thanks. So, should row2 be considered to have 4 fields or 7? Can you have commas within a field? What is the output you would like to have from that input? – terdon Nov 25 '14 at 11:40
  • @terdon row2 & row3 should have 134 fields , same as row1 i dont want to add 0 values at this stage, rather i want to sort them by number of fields – Hussein Kray Nov 25 '14 at 11:44
  • @HusseinKray So in your example they all have the same number of fields and won't need sorting ? – user78605 Nov 25 '14 at 11:49
2

Think this is what you want
Add -F, for comma separated.
E.g awk -F, '$(NF+1)=NF' file

Add Number to end of rows

 awk '$(NF+1)=NF' file

Input

1
1 2 3
1 2
1 2 3 4 5 6
a b

Output

1 1
1 2 3 3
1 2 2
1 2 3 4 5 6 6
a b 2

Sort Rows

 awk '{a[NF]=a[NF]?a[NF]"\n"$0:$0;x=x<NF?NF:x}END{for(i=1;i<=x;i++)if(i in a)print a[i]}'

Input

1
1 2 3
1 2
1 2 3 4 5 6
a b

Output

1
1 2
a b
1 2 3
1 2 3 4 5 6

Print to different files

Using field length of 4 for example, change to 134 or whatever you want for yours

 awk '{print > (NF>=4?"LargeFile.txt":"SmallFile.txt")}' file 

Input

1
1 2 3
1 2
1 2 3 4 5 6
a b

Output

LargeFile.txt

 1 2 3 4 5 6

SmallFile.txt

1
1 2 3
1 2
a b
  • that would work , but i have a comma separated values (CSV files) – Hussein Kray Nov 25 '14 at 11:28
  • 1
    @HusseinKray that's why you need to show your input. Also, do you want the lines from all CSVs do be mixed? Separate? Sorted? You need to show us an example of both your input and your desired output. – terdon Nov 25 '14 at 11:29
  • @HusseinKray Add -F, after awk – user78605 Nov 25 '14 at 11:31
  • @Jidder awk isn't a native CSV parser : there may be commas in quoted fields. The OP may need a more robust command line tool for processing CSV files. – John WH Smith Nov 25 '14 at 11:33
  • @JohnWHSmith OP seems happy with the output for fields in his post, but yes if it has embedded commas then depending on which version of awk they are using all of this may be useless. – user78605 Nov 25 '14 at 11:37
2

Similar to @terdon's answer, but with sed:

{ seq -s, 10; seq -s, 5; seq -s, 15; } | 
tee - -

That's my infile - it looks like:

1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5
1,2,3,4,5
1,2,3,4,5
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

I can then do:

sed 'h;s/[^,]*//g;G;s/\n/ /' | sort -t\  -nk1,1

...which gets...

,,,, 1,2,3,4,5
,,,, 1,2,3,4,5
,,,, 1,2,3,4,5
,,,,,,,,, 1,2,3,4,5,6,7,8,9,10
,,,,,,,,, 1,2,3,4,5,6,7,8,9,10
,,,,,,,,, 1,2,3,4,5,6,7,8,9,10
,,,,,,,,,,,,,, 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
,,,,,,,,,,,,,, 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
,,,,,,,,,,,,,, 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

The numbers are not there, but the count is sure enough, I guess. To remove the leading commas I can just do:

PIPELINE | sed 's/,* //'

...which gets...

1,2,3,4,5
1,2,3,4,5
1,2,3,4,5
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

Perhaps not the most auspicious answer thus far, but the primary reason I decided to write this was that you mention you want to write lines with 134 comma-separated entries to another file. This, as it happens, is a simple matter with sed. For example - lets say I wanted to write the lines with 10 fields from the above sequence to a file2:

PIPELINE | sed '/^\([^,]*,[^,]*\)\{9\}$/w file2'
cat file2

OUTPUT

1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10

I use \{9\} above because it specifies 9 instances of the pattern - which makes 9 delimiters to 10 delimited fields. Ranges are simply handled as well:

PIPELINE | sed '/^\([^,]*,[^,]*\)\{4,9\}$/w file2'
cat file2

OUTPUT

1,2,3,4,5
1,2,3,4,5
1,2,3,4,5
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10
1,2,3,4,5,6,7,8,9,10
1

This will add the number of (comma separated) fields to the beginning of each line, print the line and then sort everything:

awk -F"," '{print NF,$0}' *csv | sort -nk1,1

The -n is numerical sort and the -k1,1 ensures that it is only sorted on the first field. To remove the number of fields after sorting, use:

awk -F"," 'print NF,$0' *csv | sort -nk1,1 | cut -d ' ' -f 2- 

NOTE: This will break very easily depending on your actual data. Can you have commas within fields? Can you have fields spanning multiple lines? This is a very naive approach and can't deal with any of that.

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.