I have a file sales_$date.csv. I want to split it into 10 files based on the last digit of first column (ITEM). so in reality file would be split in 10 files as sales_$date-01.csv, sales_$date-02.csv,.. and so on. Also I need to keep the header in all the files. Length for column (ITEM) value is not fixed. This process needs to run every :45 minutes every day. Below is the example

sales_$date.csv file: FILE=sales_$date ITEM,QTY,STORE,BUYABLEFLAG 4000,1,13805,Y 4001,3,1456,N 5010,2,14534,Y 7200,5,14566,N 4002,2,6534534,N 5611,9,34234,Y 7832,32,6575,N

sales_$date-01.csv should have the records for items (see the first column ITEM value) ending with 0:


sales_$date-02.csv should have the records for items (see the first column ITEM value) ending with 1:


sales_$date-03.csv should have the records for items (see the first column ITEM value) ending with 2:


Also all the file names i.e. sales_date-01,sales_date-02,sales_date-03 are in a variable called FILE_NAME.

  • Are all "ITEM" numbers guaranteed to be four digits?
    – mattdm
    Mar 31 '16 at 15:59
  • Is this a one-off action or something you will want to do automatically (like every day)?
    – Law29
    Mar 31 '16 at 16:51

If your file is merely large, not humongous, you could make 10 passes through the file with:

for digit in 0 1 2 3 4 5 6 7 8 9 ; do
    egrep "^ITEM,|^...$digit" sales.csv >sales-0$digit.csv
  • File is very large with more than 2M records in it Mar 31 '16 at 15:56
  • Why not for digit in {0..9} or for digit in $(Seq 0 9)? Also, you might meant to use grep -E instead of the deprecated egrep.
    – terdon
    Mar 31 '16 at 16:42

In a single pass:

awk '
    NR == 1 { for (i=1; i<=10; i++) print > sprintf("sales-%02d.csv", i) }
    NR > 1  { print > sprintf("sales-%02d.csv", $1%10+1) }
' data
  • Thanks @barefoot, your solution is working perfect. Only thing is that my file name (sales_$date) is in a variable so when I am trying below: FILE=sales_20160104 awk ' NR == 1 { for (i=1; i<=10; i++) print > sprintf("%s-%02d.csv", $FILE i) } NR > 1 { print > sprintf("%s-%02d.csv", $FILE $1%10+1) } ' $FILE_ITL Then I am getting the below error: awk: 0602-562 Field $() is not correct. The input line number is 1. The file is ParksDataLoader_SalesInventoryFeed_2016 04010804.csv. The source line number is 2. Apr 1 '16 at 14:47

I'd break out the perl for this - it's a little more verbose, but hopefully clearer what it's doing? It works single pass, parses the 'id' from the line, and opens a file based on that. It'll actually not create files at all, that would otherwise be empty - I'd call that a feature, but it's easy to change if you don't like it.

use strict;
use warnings;

#read header row from STDIN or file specified on command line (like grep/sed/awk)
my $header = <>; 

#set up file handles to write to 
my %file_for; 

#iterate STDIN or files on command line
while ( <> ) { 
    #get 'first digit before a comma' on current line. 
    my ( $file_id ) = /(\d),/;

    #open the file, if we haven't already. (it auto closes at script exit)
    if ( not defined $file_for{$file_id} ) {
        open ( $file_for{$file_id}, '>', "sales-0".$file_id.".csv" ) or warn $!;
        #print the header row
        print {$file_for{$file_id}} $header;
    #select this file for output, and print the current line. 
    select $file_for{$file_id} and print;

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.