2

I have a huge file (more than 2M records in it). Here is my requirement:

  • Initially, break the one big file up in to 10 smaller files.
  • The files should be formatted as follows: <File_name>-<timestamp>-xx
    • <timestamp> is the same time in each file
    • xx will represent which file it is from 1 to 10
  • The files must have a clean split between items. That is, we cannot have the same item in more than one file.

For example, if I have the file as below:

ITEM,PARENT_PARTNUMBER,STORE_NUMBER,QUANTITY,BUYABLE,AVAILABILITYCODE,STORENAME,PHONENUMBER
400000209333,400000209333P,ALL,1297,1,2,,
400000209333,400000209333P,A-80007838,1297,1,2,,
400009664058,400009664058P,ALL,499,1,1,,
400009664058,400009664058P,A-80007838,477,1,1,,
400009664058,400009664058P,13806529,104,0,0,WDW - FLOWER & GARDEN,8-224-6122/5866
400000276151,400000276151P,ALL,0,0,0,,
400000276151,400000276151P,A-80007823,0,0,0,,
400000209692,400000209692P,ALL,8,1,1,,

Then I want to split the files into like this. First file (assume first file has reached to 20000 limit and at 19999 there is item number change which can not be in the same file because of max file limit as 20000 and we need to maintain the unique item number in the file:

400000209333,400000209333P,ALL,1297,1,2,,
400000209333,400000209333P,A-80007838,1297,1,2,,
400009664058,400009664058P,ALL,499,1,1,,
400009664058,400009664058P,A-80007838,477,1,1,,
400009664058,400009664058P,13806529,104,0,0,WDW - FLOWER & GARDEN,8-224-6122/5866

Second file:

400000276151,400000276151P,ALL,0,0,0,,
400000276151,400000276151P,A-80007823,0,0,0,,
400000209692,400000209692P,ALL,8,1,1,,

and so on until file 10.

  • 2
    You should start using an actual database rather than a CSV. 2 million records? Yikes. – Wildcard Mar 16 '16 at 21:44
  • What if the ITEM change was at 20001 instead of the 19999 in your example? – roaima Mar 16 '16 at 22:34
1
#!/bin/bash

file_name="huge.file"

#get file mask
my_mask="$(date +"$file_name-%F-")"

#collect lines with same item in one string separated by unexpected symbol
sed ':1;N;/^\([^,]\+,\).*\n\1/s/\n/×/;t1;P;D' "$file_name" > tmp.file

#divide tmp.file for 10 pieces without line splitting
split -dn l/10 "tmp.file" "$my_mask"

#split lines with same item back
sed -i 's/×/\n/g' "$my_mask"*

#remove tmp.file if need it
rm tmp.file
  • sed: 0602-417 The label :1;N;/^([^,]\+,).*\n\1/s/\n/×/;t1;P;D is greater than eight characters.split: Not a recognized flag: d Usage: split [-l Line_Count] [-a Suffix_Length] [File [Prefix]] or: split -b Number[k|m] [-a Suffix_Length] [File [Prefix]] sed: Not a recognized flag: i Usage: sed [-n] [-u] Script [File ...] sed [-n] [-u] [-e Script] ... [-f Script_file] ... [File ...] – saurabh Mar 17 '16 at 17:12
0

Final version, I swear ... :)

It is assumed that:

  • the first line contains the header,
  • ITEM is always the first column,
  • all lines with same ITEM number are consecutive,
  • the number of lines with same ITEM number is lower than the number of lines in each smaller files minus 1.

This version guarantees that the smaller files will contain a number of lines lower than or equal to the upper limit specified (i.e. LINES).

#!/usr/bin/awk -f
BEGIN {
    numberOfLinesInCurrentFile=0;
    numberOfLinesInBuffer=0;
    filenumber=0;
    header="";
    previousITEM="";
    FS=",";
    timestamp=ENVIRON["TIMESTAMP"];
    numberOfLinesPerFile=ENVIRON["LINES"];
    currentFilename=FILENAME "-" timestamp "-00";
}
{
  if (NR == 1) {
    header=$0;
    print header >> currentFilename;
    numberOfLinesInCurrentFile=1;
  } else {
    currentITEM=$1;
    if (previousITEM != currentITEM) {
      for (i=0; i<numberOfLinesInBuffer; i++) {
        print bufferOfLines[i] >> currentFilename;
      }
      numberOfLinesInCurrentFile+=numberOfLinesInBuffer;
      numberOfLinesInBuffer=0;
      bufferOfLines[1]=$0
    }
    if ((numberOfLinesInCurrentFile+numberOfLinesInBuffer) >= numberOfLinesPerFile) {
        filenumber++;
        currentFilename=sprintf("%s-%s-%02d", FILENAME, timestamp, filenumber);
        print header >> currentFilename;
        numberOfLinesInCurrentFile=1;
    }
    bufferOfLines[numberOfLinesInBuffer++]=$0
    previousITEM=$1;
  }
}

LINES is used to specify the max number of lines per smaller files.

TIMESTAMP is used to specify the timestamp.

bigfile is the file to split.

The test is below:

LINES=4000 TIMESTAMP=20160320101538 ./scriptv2.awk bigfile

ls bigfile*
bigfile                    bigfile-20160320101538-02  bigfile-20160320101538-04  bigfile-20160320101538-06  bigfile-20160320101538-08
bigfile-20160320101538-01  bigfile-20160320101538-03  bigfile-20160320101538-05  bigfile-20160320101538-07

----


SECOND VERSION FOR REFERENCE: it does not guarantee that the number of lines in each smaller file will be under the specified limit.

One more version quickly tested. It is assumed that the first line contains the header and that ITEM is always the first column. All lines with same ITEM number are consecutive.

cat script.awk
#!/usr/bin/awk -f
BEGIN {
    filenumber=0;
    header="";
    previousITEM="";
    FS=",";
    timestamp=ENVIRON["TIMESTAMP"];
    numberOfLinesPerFile=ENVIRON["LINES"];
    currentFilename=FILENAME "-" timestamp "-00";
    changeFilenameWhenPossible=0;
}
{
  if (NR == 1) {
    header=$0;
  } else {
    currentITEM=$1;
    if (NR % numberOfLinesPerFile == 0) {
      if (previousITEM != currentITEM) {
        filenumber=filenumber+1;
        filenamberString=sprintf("%02d",filenumber);
        currentFilename=FILENAME "-" timestamp "-" filenamberString;
        changeFilenameWhenPossible=0;
        print header >> currentFilename;
      } else {
        changeFilenameWhenPossible=1;
      } 
    } else if (changeFilenameWhenPossible == 1 && previousITEM != currentITEM) {
      filenumber=filenumber+1;
      filenamberString=sprintf("%02d",filenumber);
      currentFilename=FILENAME "-" timestamp "-" filenamberString;
      changeFilenameWhenPossible=0;
      print header >> currentFilename;
    }
    previousITEM=$1;
  }
  print $0 >> currentFilename;
}

LINES should be set to number of lines wanted per smaller files.

TIMESTAMP should be set to timestamp specified.

bigfile is the 2M lines file.

The test is below:

chmod +x script.awk
LINES=200000 TIMESTAMP=20160318101538 ./script.awk bigfile

ls -1 bigfile-*
bigfile-20160318101538-01
bigfile-20160318101538-02
bigfile-20160318101538-03
bigfile-20160318101538-04
bigfile-20160318101538-05
bigfile-20160318101538-06
bigfile-20160318101538-07
bigfile-20160318101538-08
bigfile-20160318101538-09
bigfile-20160318101538-10

For reference first answer...

I noticed that you would like to get rid of the very first line with the header, right?

#!/bin/bash --
nblines=$(wc -l "${1}" | cut -d\  -f1)
nblines=$(((nblines - 1)/10))
tail -n +2 "${1}" | split -l $nblines -d -- - "${1}"-"${2}"-
touch -r "${1}" ./"${1}"?*

Be sure to test with a bigfile which contains 11 lines or more.

The touch command is used to apply the times of bigfile to all the smaller ones just created. Remove touch if it is not needed.

new edit not tested yet:

ls

bigfile
script.sh

chmod +x ./script.sh
./script.sh bigfile 20160309144430

ls -l bigfile*
-rw-r--r-- 1 jay stackgrp 556 Mar 16 17:03 bigfile
-rw-r--r-- 1 jay stackgrp 92 Mar 16 17:03 bigfile-20160309144430-00
-rw-r--r-- 1 jay stackgrp 42 Mar 16 17:03 bigfile-20160309144430-01

etc.

Note that ls shows the same time for all bigfile* thanks to touch command.

  • How does this keep all of the same ITEM in a single file? – roaima Mar 16 '16 at 22:31
  • Haa I see the ITEM column... No this version does not keep the same items in the same file, it needs to be reworked. – Jay jargot Mar 16 '16 at 22:35
  • awk should help here and be also very efficient. I will test something tomorrow if the exact answer is still missing. Good night from french alps. – Jay jargot Mar 16 '16 at 22:42
  • I forgot to add the header in my example. I need the header too. All I want is the same item# should not be in any other file but one file can have multiple items with the limit of each file let's say as 200000. – saurabh Mar 17 '16 at 15:45
  • the answer had been updated. – Jay jargot Mar 18 '16 at 2:49

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.