AWK Split large CSV file with headers and print output files based on column value

Question

I have a CSV file of around 800 mb which I need to split up using AWK. The file has a column with ID's in them which I want to use to split the file on. I'm familiar/know how to accomplish this with Perl but not with AWK since I've only used it a few times. (In perl I would use the Text::CSV module but I don't have the option in this case)

I found this answer: https://stackoverflow.com/a/16795137 which is basically what I want but with a small alteration. It has to contain an if statement so it will only print if the column I want to split it on is a digit. This is necessary because the file column can shift sometimes and I want to send the non-digit lines to a seperate file (junk.csv).

I'm using the windows cmd version for testing right now but I'll eventually run it on linux. (Below original code)

awk -F, "NR==1{hdr=$0;next}!($3 in files){files[$3]=1;print hdr\"\n\">$3\".csv\"}{print>$3\".csv\"}" test.csv

And my intention is this:

awk -F";" "{if ($3 ~ /^[0-9]+$/){"NR==1{hdr=$0;next}!($3 in files){files[$3]=1;print hdr>$3\".csv\"}{print>$3\".csv\"}"" test.csv

I can't figure out how to do this in AWK (just yet). The double quotes are also throwing me off (because of the windows version). Where am I going wrong?

This is my error output:

awk: {if($3 ~ /^[0-9]+$/) NR==1{hdr=$0;next}!($3 in files){files[$3]=1;print hdr>$3".csv"}{print>$3.csv};else print>junk.csv}
awk:                           ^ syntax error
awk: {if($3 ~ /^[0-9]+$/) NR==1{hdr=$0;next}!($3 in files){files[$3]=1;print hdr>$3".csv"}{print>$3.csv};else print>junk.csv}
awk:                                                      ^ syntax error
awk: {if($3 ~ /^[0-9]+$/) NR==1{hdr=$0;next}!($3 in files){files[$3]=1;print hdr>$3".csv"}{print>$3.csv};else print>junk.csv}
awk:                                                                                                     ^ syntax error
awk: {if($3 ~ /^[0-9]+$/) NR==1{hdr=$0;next}!($3 in files){files[$3]=1;print hdr>$3".csv"}{print>$3.csv};else print>junk.csv}
awk:                                                                                                                    ^ syntax error
errcount: 4

This is my (sample) data:

10002394;22.98;48;http://testdata.com/bla/29012827.jpg;5.95;93962094820
10003062;19.99;26;http://testdata.com/bla/29002816.jpg;5.95;17012725049
10003122;13.0;53;http://testdata.com/bla/29019899.jpg;5.95;24404000059
10004766;12.99;48;http://testdata.com/bla/29007085.jpg;5.95;95074666117
10007645;20.99;65;http://testdata.com/bla/28798580.jpg;5.95;10201848233
10009363;119.0;53;http://testdata.com/bla/29004907.jpg;5.95;9823036360
10009631;19.95;48;http://testdata.com/bla/29013097.jpg;5.95;20689058198
10010119;9.99;48;http://testdata.com/bla/29016592.jpg;5.95;80076014280
10012615;20.99;53;http://testdata.com/bla/28772382.jpg;5.95;3948187983
10015250;14.99;48;http://testdata.com/bla/29015812.jpg;5.95;93962045440
10019190;69.99;53;http://testdata.com/bla/29010968.jpg;5.95;948187983
10025155;27.99;65;http://testdata.com/bla/29011075.jpg;5.95;14201021349
10025825;12.99;65;http://testdata.com/bla/29017837.jpg;5.95;93962025367
10029650;27.99;48;http://testdata.com/bla/29003007.jpg;5.95;3692164452
10034957;34.99;53;http://testdata.com/bla/29000529.jpg;5.95;42872898825
10041967;24.99;65;http://testdata.com/bla/28781700.jpg;5.95;91229911080
10045277;59.99;65;http://testdata.com/bla/29010583.jpg;5.95;67365082290
10045795;10.99;48;http://testdata.com/bla/29002819.jpg;5.95;19422308188
10048375;26.99;26;http://testdata.com/bla/29002270.jpg;5.95;95082912275
10052550;19.99;48;http://testdata.com/bla/29016347.jpg;5.95;7368425436

And I want to accomplish this:

File --> 26.csv
10003062;19.99;26;http://testdata.com/bla/29002816.jpg;5.95;17012725049
10048375;26.99;26;http://testdata.com/bla/29002270.jpg;5.95;95082912275

File --> 48.csv
10002394;22.98;48;http://testdata.com/bla/29012827.jpg;5.95;93962094820
10004766;12.99;48;http://testdata.com/bla/29007085.jpg;5.95;95074666117
10009631;19.95;48;http://testdata.com/bla/29013097.jpg;5.95;20689058198
10010119;9.99;48;http://testdata.com/bla/29016592.jpg;5.95;80076014280
10015250;14.99;48;http://testdata.com/bla/29015812.jpg;5.95;93962045440
10029650;27.99;48;http://testdata.com/bla/29003007.jpg;5.95;3692164452
10045795;10.99;48;http://testdata.com/bla/29002819.jpg;5.95;19422308188
10052550;19.99;48;http://testdata.com/bla/29016347.jpg;5.95;7368425436

File --> 53.csv
10003122;13.0;53;http://testdata.com/bla/29019899.jpg;5.95;24404000059
10009363;119.0;53;http://testdata.com/bla/29004907.jpg;5.95;9823036360
10012615;20.99;53;http://testdata.com/bla/28772382.jpg;5.95;3948187983
10019190;69.99;53;http://testdata.com/bla/29010968.jpg;5.95;948187983
10034957;34.99;53;http://testdata.com/bla/29000529.jpg;5.95;42872898825

File --> 65.csv
10007645;20.99;65;http://testdata.com/bla/28798580.jpg;5.95;10201848233
10025155;27.99;65;http://testdata.com/bla/29011075.jpg;5.95;14201021349
10025825;12.99;65;http://testdata.com/bla/29017837.jpg;5.95;93962025367
10041967;24.99;65;http://testdata.com/bla/28781700.jpg;5.95;91229911080
10045277;59.99;65;http://testdata.com/bla/29010583.jpg;5.95;67365082290

nu11p01n73R · Accepted Answer · 2014-11-17 14:45:48Z

3

You can simplify the awk as

awk -F\; '{print > $3".csv"}' input

Will produce the following csv files with content

26.csv
10003062;19.99;26;http://testdata.com/bla/29002816.jpg;5.95;17012725049
10048375;26.99;26;http://testdata.com/bla/29002270.jpg;5.95;95082912275
48.csv
10002394;22.98;48;http://testdata.com/bla/29012827.jpg;5.95;93962094820
10004766;12.99;48;http://testdata.com/bla/29007085.jpg;5.95;95074666117
10009631;19.95;48;http://testdata.com/bla/29013097.jpg;5.95;20689058198
10010119;9.99;48;http://testdata.com/bla/29016592.jpg;5.95;80076014280
10015250;14.99;48;http://testdata.com/bla/29015812.jpg;5.95;93962045440
10029650;27.99;48;http://testdata.com/bla/29003007.jpg;5.95;3692164452
10045795;10.99;48;http://testdata.com/bla/29002819.jpg;5.95;19422308188
10052550;19.99;48;http://testdata.com/bla/29016347.jpg;5.95;7368425436
53.csv
10003122;13.0;53;http://testdata.com/bla/29019899.jpg;5.95;24404000059
10009363;119.0;53;http://testdata.com/bla/29004907.jpg;5.95;9823036360
10012615;20.99;53;http://testdata.com/bla/28772382.jpg;5.95;3948187983
10019190;69.99;53;http://testdata.com/bla/29010968.jpg;5.95;948187983
10034957;34.99;53;http://testdata.com/bla/29000529.jpg;5.95;42872898825
65.csv
10007645;20.99;65;http://testdata.com/bla/28798580.jpg;5.95;10201848233
10025155;27.99;65;http://testdata.com/bla/29011075.jpg;5.95;14201021349
10025825;12.99;65;http://testdata.com/bla/29017837.jpg;5.95;93962025367
10041967;24.99;65;http://testdata.com/bla/28781700.jpg;5.95;91229911080
10045277;59.99;65;http://testdata.com/bla/29010583.jpg;5.95;67365082290

NOTE

If you want to send the lines which have non digits in column 3 to junk.csv a small change in the above awk can be helpfull

awk -F\; '$3 ~ /^[0-9]+$/{print > $3".csv"; next} {print > "junk.csv"}' input

$3 ~ /^[0-9]+$/ performs a regex match on column 3 and if it matches, sends to corresponding csv file. else the line is written to junk.csv

OR

a much simpler version like

awk -F\; '{file=$3~/^[0-9]+$/?$3:"junk";print >file".csv"}'

Thanks to Jidder for the suggestion.

edited Nov 17, 2014 at 14:45

answered Nov 17, 2014 at 13:44

nu11p01n73R

26.8k3 gold badges42 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

fedorqui Over a year ago

Note print suffices, print $0 is not necessary. Also, to check that a field is numeric / is not, you can use /^[0-9]+$/. This will match all having only digits (and at least one).

nu11p01n73R Over a year ago

@fedorqui I have made the change with print. Thank you for that. But am not sure about your second point sorry. I have written it as $3 ~ /^[0-9]+$/ which matches only digits

fedorqui Over a year ago

Yes, this is exactly what I meant. When I commented it you had something a bit different, like $3 ~ /[0-9]+/ which would match rows like 23a.

nu11p01n73R Over a year ago

@fedorqui yeah first i wrote something like that. Changed that when i realized it was wrong. Anyways thank you for pointing that :)

user3442743 Over a year ago

Could also do the junk one this way awk -F\; '{file=$3~/^[0-9]+$/?$3:"junk";print >file".csv"}'

|

Collectives™ on Stack Overflow

AWK Split large CSV file with headers and print output files based on column value

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related