Combining multiple csv files together and also adding a column during concatenation
Question: I have a set of files I am trying to import into mySql.

Each csv file looks like this:

    
    
    Header1;Header2;Header3;Header4;Header5
    Data1;Data2;Data3;Data4;Data5;
    Data1;Data2;Data3;Data4;Data5;
    Data1;Data2;Data3;Data4;Data5;
    Data1;Data2;Data3;Data4;Data5;
    

Data may contain spaces, periods or a full colon. They absolutely will not
contain a semi-colon so that is a valid delimiter. They also will not contain
\n or any other newline characters.

Example Data

    
    
    2010.08.30 18:34:59
    0.7508 
    String Of Characters With Spaces In Them
    

Each file has a unique name to it. The names all follow the following pattern
Token1_Token2_Token3.csv

I am interested in combining a lot of these csv files (on the order of several
hundred) into one CSV file. Files can range from 10KB to 400MB. Ultimately, I
want to send it over to mysql. Don't worry about getting rid of the individual
header rows; I can do that in MySql easily.

I would like the final CSV file to look like this:

    
    
    Header1,Header2,Header3,Header4,Header5,FileName
    Data1,Data2,Data3,Data4,Data5,Token1
    Data1,Data2,Data3,Data4,Data5,Token1
    Data1,Data2,Data3,Data4,Data5,Token1
    Data1,Data2,Data3,Data4,Data5,Token1
    Data1,Data2,Data3,Data4,Data5,Token1
    

I dont care about any of the other tokens. I can also live if the solution
just dumps each csv filename into the Token1 field because, again, I can parse
that in mySql easily.

Please help me!! I've spent over 10 hours on what should be a relatively easy
problem.

Technologies available:

awk windows batch linux bash powershell perl python php mysql-import

This is a server box so I wont be able to compile anything but if you give me
a Java solution I will definitely try to run it on the box.


Answer: Using [`Text::CSV`](http://search.cpan.org/perldoc?Text%3a%3aCSV):

## Program

    
    
    #!/usr/bin/env perl
    
    use strict;
    use warnings;
    
    use File::Find;
    use Text::CSV;
    
    my $semi_colon_csv = Text::CSV->new( { 'sep_char' => ';', } );
    my $comma_csv = Text::CSV->new( {
        'sep_char' => ',',
        'eol'      => "\n",
    } );
    
    open my $fh_output, '>', 'output.csv' or die $!;
    
    sub convert {
        my $file_name = shift;
    
        open my $fh_input, '<', $file_name or die $!;
    
        # header
        my $row = $semi_colon_csv->getline($fh_input);
        $comma_csv->print( $fh_output, [ @$row, $file_name ] );
    
        while ( $row = $semi_colon_csv->getline($fh_input) ) {
            pop @$row unless $row->[-1];  # remove trailing semi-colon from input
            my ($token) = ( $file_name =~ /^([^_]+)/ );
            $comma_csv->print( $fh_output, [ @$row, $token ] );
        }
    }
    
    sub wanted {
        return unless -f;
        convert($_);
    }
    
    my $path = 'csv';  # assuming that all your CSVs are in ./csv/
    find( \&wanted, $path );
    

## Output (output.csv)

    
    
    Header1,Header2,Header3,Header4,Header5,Token1_Token2_Token3.csv
    Data1,Data2,Data3,Data4,Data5,Token1
    Data1,Data2,Data3,Data4,Data5,Token1
    Data1,Data2,Data3,Data4,Data5,Token1
    Data1,Data2,Data3,Data4,Data5,Token1
    

