Parse huge file shell ( or other scripting language )

I said in comments that shell read is slow, and so is opening the output once per record.

Your shell-script version looks like it never empties its associative array, but also never reuses old entried. So eventually your shell will be using huge memory, because it keys each record's entries to a record counter.

You're just re-formatting records from blocks separated by empty lines to single lines with fields separated by spaces. This isn't hard, and doesn't require keeping previous records in memory.

I was thinking along the same lines as Walter A. This awk program is most of the way to solving the problem.

Note the delete a after printing the record into a csv line, to clear the fields.

awk   -vOFS=' ; ' -F'\\s*:\\s*' '/^#/{print; this_is_for_debugging }
    function output_rec(){ print a["kmMsisdn"], a["serialNumber"], a["kmSubscriptionType"], a["objectClass"] }
    /^$/ { output_rec(); delete a;next}
    END  { output_rec() }
    {  sub(/\s+$/, "", $2);  # strip trailing whitespace if needed
       if ($1 == "objectClass" && a[$1] )
           { a[$1]= (a[$1] "+" $2) } else { a[$1]=$2; }
    }' foo.etl

I'll leave it up to you to print the rest of the fields. (They're already getting parsed, by the a[$1] = $2 statement, in the else block of the "objectClass" condition.)

Splitting on whitespace*:whitespace* means we don't have to bother stripping whitespace at the start of the 2nd field. Apparently the -F arg needs doubled backslashes. It's probably a good idea to add a check that NF <= 2, to make sure there aren't any lines with multiple :.

Output for your sample input

 ;  ;  ; 
# entry-id: 1
 ;  ;  ; 
# entry-id: 3
31653440000 ; 204084400000000 ; FLEXI ; top+device+kmConnection
# entry-id: 4
31153128215 ; 214283011000000 ; FLEXI ; top+device+kmConnection+kmMultiMediaPortalService

To avoid data duplication between printing the header line and printing the fields, you could put the field names in an array, and loop over them in both places.

I was originally thinking that -v RS='\n\n' would be useful, to make every block an AWK record. Actually, that might still be useful, with FS='\n'. Then you can loop over fields (lines of each record), and split it on :. If it's impossible for a record to contain a :, like your shell script assumes, then the splitting is easy with split (same as we're doing with -F to set FS).

(In your shell version, use Column_Name=${var%%:*} to remove the longest suffix (including all the :s), instead of the shortest. Or use IFS=: read Column_Name Column_Value)

This might be better written in perl, since it's getting bulky for an awk program. perl would make it easier to do the splitting on only the first : on the line.

awk -vOFS=' ; ' -F: '
 function output_rec(){ gsub(/[ \t]+$/, "",$2);
 print a["entry-id"],a["kmMsisdn"],a["kmSubscriptionType"],a["kmSubscriptionType2"],a["kmVoiceTan"],a["kmDataTan"],a["kmPaymentMethod"],a["kmMccsDate"],a["kmCustomerBlocked"],a["kmNetworkOperatorBlocked"],a["kmBlockedNetwork"],a["kmMmpNoStatus"],a["kmMmpM3cCreditLimit"],a["kmMmpM3cStatus"],a["kmMmpM3cStatusDate"],a["kmMmpM3cRegistrationDate"],a["creatorsName"],a["createTimestamp"],a["modifiersName"],a["modifyTimestamp"],a["kmBrandName"],a["objectClass"],a["cn"],a["kmBlockedServices"],a["kmServiceProvider"]}
 /entry-id/ {output_rec(); delete a;a["entry-id"]=$2;next}
 END  { output_rec() }
   {gsub(/[ \t]+$/, "",$2);
   if ($1 == "objectClass" && a[$1] ) { a[$1]= (a[$1]"+"$2) } else { a[$1]=$2; } }' $ODSM_FILE >> $p_newFileName

With perl I'd approach it like this:

#!/usr/bin/env perl
use strict;
use warnings;

use Text::CSV;

#configure output columns and ordering.
my @output_cols = qw (
    entry-id kmMsisdn serialNumber
    kmSubscriptionType kmSubscriptionType2
    kmVoiceTan kmDataTan kmPaymentMethod
    kmMccsDate kmCustomerBlocked
    kmNetworkOperatorBlocked kmBlockedNetwork
    kmMmpNoStatus kmMmpM3cCreditLimit
    kmMmpM3cStatus kmMmpM3cStatusDate
    kmMmpM3cRegistrationDate creatorsName
    createTimestamp modifiersName
    modifyTimestamp kmBrandName
    objectClass cn
    kmBlockedServices kmServiceProvider
);

#set up our csv engine - separator of ';' particularly. 
#eol will put a linefeed after each line (might want "\r\n" on DOS)
my $csv = Text::CSV->new(
    {   sep_char => ';',
        eol      => "\n",
        binary   => 1
    }
);

#open output
open( my $output, '>', 'output_file.csv' ) or die $!;
#print header row. 
$csv->print( $output, \@output_cols );
#set columns, so print_hr knows ordering. 
$csv->column_names(@output_cols);

#set record separator to double linefeed
local $/ = "\n\n";

#iterate the 'magic' filehandle. 
#this either reads data piped on `STDIN` _or_ a list of files specified on 
#command line. 
#e.g. myscript.pl file_to_process 
#or 
#cat file_to_process | myscript.pl
#this thus emulates awk/grep/sed etc.
#NB works one record at a time - so a chunk all the way to a double line feed. 

while (<>) {
    #pattern match the key-value pairs on this chunk of data (record).
    #multi-line block.
    #because this regex will return a list of paired values (note - "g" and "m" flags), we can
    #insert it directly into a hash (associative array)
    my %row = m/^(?:# )?([-\w]+): (.*)$/mg;

    #skip if this row is incomplete. Might need to be entry-id? 
    next unless $row{'kmMsisdn'};
    $csv->print_hr( $output, \%row );
}
close ( $output );

This generates:

entry-id;kmMsisdn;serialNumber;kmSubscriptionType;kmSubscriptionType2;kmVoiceTan;kmDataTan;kmPaymentMethod;kmMccsDate;kmCustomerBlocked;kmNetworkOperatorBlocked;kmBlockedNetwork;kmMmpNoStatus;kmMmpM3cCreditLimit;kmMmpM3cStatus;kmMmpM3cStatusDate;kmMmpM3cRegistrationDate;creatorsName;createTimestamp;modifiersName;modifyTimestamp;kmBrandName;objectClass;cn;kmBlockedServices;kmServiceProvider
3;31653440000;204084400000000;FLEXI;;25;;ABO;22/03/2004;N;N;N;;;;;;;;"cn=directory manager";20331210121726Z;VOID;kmConnection;MCCS;;1
4;31153128215;214283011000000;FLEXI;;25;;ABO;22/03/2004;N;N;N;;;Potential;12/01/2012;;;;"cn=directory manager";22231210103328Z;VOID;kmMultiMediaPortalService;MCCS;;1

Note: Because we're using while ( <> ) { we can use this script like you would awk/sed. perl uses that operator as either:

• Data piped in
• open files specified on command line and read them.

So you can:

./myscript.pl filename1 filename2

or

somecommand_to_generate_data | ./myscript.pl

That’s an impressive shell script but the problem you’re solving is not a good fit for a traditional shell script. I’d imagine that using echo and output redirection for all the file writes would dramatically slow things down. With a proper programming language, you could buffer your file writes – and read in more than one line at a time.

You’ve already mentioned Perl and Python and these are exactly what I’d suggest. Both languages are used by system administrators though Python seems to be more favoured by data scientists. I’ve leaned both and Python is also my personal favourite as I like its syntax, similarity to pseudo-code and how most of its libraries that I’ve used were easy to use – and read.

Good luck with learning whichever language you choose. (A discussion on which language would be best would probably lead to this question being closed for being too opinion-based).

You can try it with awk.
awk has associative arrays, so you can use something like -F: '{row[$1]=$2}' for normal rows. You can print/reset when you have a new set.

/entry-id/ '{print row["kmMsisdn"], " , ", row["serialNumber"], " , ", row["kmSubscriptionType]}'

and when supported make the array empty by deleting it {delete row}.

It should be a lot faster than your current version.

EDIT

I looked at the answer of @Peter and just edited his solution.
I added the other fields, used $ODSM_FILE, $p_newFileName, and changed the logic for finding a new record:
After each line with an entry-id
Credit to Peter for the awk code and his explanation.

  awk -vOFS=' ; ' -F'\\s*:\\s*' ' BEGIN {
                a["entry-id"]="entry-id"; 
                a["kmMsisdn"]="kmMsisdn"; 
                a["serialNumber"]="serialNumber";
                a["kmSubscriptionType"]="kmSubscriptionType";
                a["kmSubscriptionType2"]="kmSubscriptionType2";
                a["kmVoiceTan"]="kmVoiceTan";                  
                a["kmDataTan"]="kmDataTan";                    
                a["kmPaymentMethod"]="kmPaymentMethod";        
                a["kmMccsDate"]="kmMccsDate";                  
                a["kmCustomerBlocked"]="kmCustomerBlocked";    
                a["kmNetworkOperatorBlocked"]="kmNetworkOperatorBlocked";
                a["kmBlockedNetwork"]="kmBlockedNetwork";                
                a["kmMmpNoStatus"]="kmMmpNoStatus";                      
                a["kmMmpM3cCreditLimit"]="kmMmpM3cCreditLimit";          
                a["kmMmpM3cStatus"]="kmMmpM3cStatus";                    
                a["kmMmpM3cStatusDate"]="kmMmpM3cStatusDate";            
                a["kmMmpM3cRegistrationDate"]="kmMmpM3cRegistrationDate";
                a["creatorsName"]="creatorsName";                        
                a["createTimestamp"]="createTimestamp";                  
                a["modifiersName"]="modifiersName";
                a["modifyTimestamp"]="modifyTimestamp";
                a["kmBrandName"]="kmBrandName";
                a["objectClass"]="objectClass";
                a["cn"]="cn";
                a["kmBlockedServices"]="kmBlockedServices";
                a["kmServiceProvider"]="kmServiceProvider";
        }
        function output_rec(){ print a["entry-id"],
                a["kmMsisdn"],
                a["serialNumber"],
                a["kmSubscriptionType"],
                a["kmSubscriptionType2"],
                a["kmVoiceTan"],
                a["kmDataTan"],
                a["kmPaymentMethod"],
                a["kmMccsDate"],
                a["kmCustomerBlocked"],
                a["kmNetworkOperatorBlocked"],
                a["kmBlockedNetwork"],
                a["kmMmpNoStatus"],
                a["kmMmpM3cCreditLimit"],
                a["kmMmpM3cStatus"],
                a["kmMmpM3cStatusDate"],
                a["kmMmpM3cRegistrationDate"],
                a["creatorsName"],
                a["createTimestamp"],
                a["modifiersName"],
                a["modifyTimestamp"],
                a["kmBrandName"],
                a["objectClass"],
                a["cn"],
                a["kmBlockedServices"],
                a["kmServiceProvider"] }
        END  { output_rec() }
        /^$/ { next }
        /entry-id/ {output_rec();delete a; a["entry-id"]=$2;next}
        {
           sub(/\s*$/, "", $2); # strip trailing whitespace
           if ($1 == "objectClass") { a[$1]= (a[$1]"+"$2) } else { a[$1]=$2; }
        }'  $ODSM_FILE > $p_newFileName

I tested it with 25.000 lines data, and the awk code was 30 times faster than the original code. For an input file of 11 million lines the awk solution needed 40s on my system.
@Peter: Good job!