Senior Pomidor Senior Pomidor - 9 months ago 37
Linux Question

Bash sort CSV large file and output sorted to separate files

I have large (4GB) semicolon-separated file (

1.txt
):

- "3321";"<a href='/files/goods/edit/647/'><u>[ID 647]</u></a> Шорты";"2015-09-06 18:39:17";"1590";"1";"500";"";"Лейла";"878785";"Да";"80.140.1.38"
- "2780";"<a href='/files/goods/edit/647/'><u>[ID 647]</u></a> Шорты";"2015-09-06 18:42:51";"1590";"1";"500";"";"Мара";"8664456";"Да";"46.00.00.2"
- "3352";"<a href='/files/goods/edit/698/'><u>[ID 698]</u></a> Deck";"2015-09-06 19:05:42";"990";"1";"400";"";"Ed";"456452";"Нет";"80.26.00.00"
- "3764";"<a href='/files/goods/edit/669/'><u>[ID 669]</u></a> Fish";"2015-09-06 18:36:18";"1390";"1";"530";"";"Ann";"545566";"Нет";"80.00.35.90"
- "3323";"<a href='/files/goods/edit/669/'><u>[ID 669]</u></a> Fish";"2015-09-06 18:54:18";"1390";"1";"530";"";"юрий";"99393";"Да";"85.141.00.100"
- "32763";"<a href='/files/goods/edit/430/'><u>[ID 430]</u></a> Radio";"2015-09-06


I need sort
1.txt
by second column and output all result to separate files depending on second column name.

I do this:

sed -r -i -e 's#"<a href=\x27\/files\/goods\/edit\/##g' 1.txt | sed -r -i -e 's#\/\x27>#;#g' 1.txt | sort --field-separator=';' --key=2 1.txt


But how now to split
1.txt
file and put all same ID (second column) value lines to separate file and count records in file? To have something like
647_count.txt
,
698_count.txt
,
669_count.txt
,
430_count.txt
.

Answer Source

Try the following awk script(let's call it parser.awk):

BEGIN { FS=";"; }   # field separator
{ 
    if (match($2, /[0-9]+/)) {           # matching `ID` value
        m=substr($2, RSTART, RLENGTH);
        a[m]++;                          # accumulating number of lines for each `ID`
        print > m"_count.txt";    # writing lines pertaining to certain `ID` into respective file
    } 
}
END {
    for(i in a) { 
        print "mv "i"_count.txt "i"_"a[i]".txt"  # renaming files with actual counts
    }
} 

Usage:

awk -f parser.awk 1.csv | sh

For the input fragment you have posted in the question I've obtained the following list of files:

430_1.txt 
647_2.txt 
669_2.txt
698_1.txt