How to sort huge CSV file?

Question

I have some huge (2GB and more) CSV files I need to sort, using Powershell or Perl (company request). I need to sort them from any column, depending on the file.

My CSV files are, for some, looking like this, using double quotes :

Column1;Column2;Column3;Column4
1234;1234;ABCD;"1234;ABCD"
5678;5678;ABCD;"5678;ABCD"
9012;5678;ABCD;"9012;ABCD"
...

In Powershell, I already tested the solution Import-CSV, but I got OutOfMemory Exception problem. I also tried to load my CSV file in SQL table using OleDb Connection with this code :

$provider = (New-Object System.Data.OleDb.OleDbEnumerator).GetElements() | Where-Object { $_.SOURCES_NAME -like "Microsoft.ACE.OLEDB.*" }

if ($provider -is [system.array]) { $provider = $provider[0].SOURCES_NAME } else {  $provider = $provider.SOURCES_NAME }

$csv = "PathToCSV\file.csv"

$firstRowColumnNames = "Yes"

$connstring = "Provider=$provider;Data Source=$(Split-Path $csv);Extended Properties='text;HDR=$firstRowColumnNames;';"

$tablename = (Split-Path $csv -leaf).Replace(".","#")

$sql = "SELECT * from [$tablename] ORDER BY Column3"

# Setup connection and command
$conn = New-Object System.Data.OleDb.OleDbconnection
$conn.ConnectionString = $connstring
$conn.Open()
$cmd = New-Object System.Data.OleDB.OleDBCommand
$cmd.Connection = $conn
$cmd.CommandText = $sql

$cmd.ExecuteReader()

# Clean up
$cmd.dispose 
$conn.dispose

But this returns me the error : Exception calling "ExecuteReader" with "1" argument(s): "No value given for one or more required parameters." and I don't understand why. I tried to modify the code, the SQL code, and it still doesn't work.

I think this is the best solution to do that, but I didn't achieve to make it work for now, and I'm open to all other solutions that could work...

I'm a total beginner in Perl, so I just tried to understand how it works and how to use it, but didn't code anything for the moment.

EDIT

I just tested all the solutions you proposed, and thank you very much for this help.

Both solutions didn't work because the modules you told me to use (Text::CSV, File::Sort or Data::Dumper) are not installed in the Perl version I am using, and I can't install them (company restrictions...).

What I've tried instead, is to try a simple sort on a column, without taking care of the double quotes problem :

use CGI qw(:standard);
use strict;
use warnings;

my $file = 'path	o\my\file.csv';

open (my $csv, '<', $file) || die "cant open";
foreach (<$csv>) {
   chomp;
   my @fields = split(/\;/);
}

@sorted = sort { $a->[1] cmp $b->[1] } @fields;

I was thinking this should work, sorting in my array @sorted the datas I have by the 2nd column, but it doesn't work, and I don't understand why...

user_967621 · Answer

Use *NIX sort (or its equivalent in the OS you are using) with a delimiter option (e.g., -t';' - make sure to quote the semicolon) to sort the file. If the company requires that you use Perl, wrap the system call to sort in Perl like so:

system "sort -t';' [options] in_file > out_file" and die "cannot sort: $?"

Examples:

Sort by column 1, numerically:

sort -k1,1g -t';' in_file.txt > out_file.txt

Note that ( head -n1 in_file.txt ; tail -n+2 in_file.txt | sort ... ) is needed to keep the header on top and sort only the data lines.

Sort by column 1, numerically descending:

( head -n1 in_file.txt ; tail -n+2 in_file.txt | sort -k1,1gr -t';' ) > out_file.txt

Sort by column 4, ASCIIbetically, then by column 1, numerically descending:

( head -n1 in_file.txt ; tail -n+2 in_file.txt | sort -k4,4 -k1,1gr -t';' ) > out_file.txt

user_3343917 · Answer

One could use DBD::CSV, in which case something like this might do the job:

use Data::Dumper;
    $Data::Dumper::Deepcopy=1;
    $Data::Dumper::Indent=1;
    $Data::Dumper::Sortkeys=1;
use DBI;
use Getopt::Long::Descriptive ('describe_options');
use Text::CSV_XS ('csv');
use Try::Tiny;
use 5.01800;
use warnings;

try {
    push @ARGV,'--help'
        unless (@ARGV);
    my ($opts,$usage)=describe_options(
            'my-program %o ',
            ,['field|f=s'     ,'the order by field']
            ,['table|t=s'     ,'The table (file) to be sorted']
            ,['new|n=s'       ,'The sorted table (file)']
            ,[]
            ,['verbose|v'      ,'print extra stuff'              ,{ default => !!0 }]
            ,['help'           ,'print usage message and exit'   ,{ shortcircuit => !1 }]
            );

    if ($opts->help()) { # MAN! MAN!
        say <<"_HELP_";
        @{[$usage->text]}

_HELP_
        exit;
        }
    else { # No MAN required.
        };

    my $dbh=DBI->connect("dbi:CSV:",undef,undef,{
        f_ext        => ".csv/r",
        csv_sep_char => ";",
        RaiseError   => 1,
        }) or die "Cannot connect: $DBI::errstr";

    my $sth=$dbh->prepare(
        "select * from @{[$opts->table()]} order by @{[$opts->field()]}"
        );

    # New table
    my $csv=Text::CSV_XS->new({ sep_char => ";" });
    open my $fh,">:encoding(utf8)","@{[$opts->new()]}.csv"
        or die "@{[$opts->new()]}.csv: $!";

    # fields
    $sth->execute();
    my $fields_aref=$sth->{NAME};
    $csv->say($fh,$fields_aref);

    # the sorted rows
    my $max_rows=5_000;
    while (my $aref=$sth->fetchall_arrayref(undef,$max_rows)) {
        $csv->say($fh,$_)
            for (@$aref);
        };
    close $fh
        or die "@{[$opts->new()]}.csv: $!";
    $dbh->disconnect();
    }
catch {
    Carp::confess $_;
    };
__END__

(Under Window) invoke it as

perl CSV_01.t -t data -f "column2 DESC" -n newest

Invoked without parameters gets help or

    my-program [-fntv] [long options...] 
    -f STR --field STR  the order by field
    -t STR --table STR  The table (file) to be sorted
    -n STR --new STR    The sorted table (file)

    -v --verbose        print extra stuff
    --help              print usage message and exit

(Sadly verbose doesn't do any extra.)

How to sort huge CSV file?

2 Answers