2

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:

ITEM,QTY,STORE,BUYABLEFLAG
4000,1,13805,Y
5010,2,14534,Y
7200,5,14566,N

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

ITEM,QTY,STORE,BUYABLEFLAG
4001,3,1456,N
5611,9,34234,Y

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

ITEM,QTY,STORE,BUYABLEFLAG
4002,2,6534534,N
7832,32,6575,N

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

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

3 Answers 3

4

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
done
2
  • File is very large with more than 2M records in it Mar 31, 2016 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, 2016 at 16:42
3

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
1
  • 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, 2016 at 14:47
0

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.

#!/usr/bin/perl
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;
}

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .