Using Perl to parse CSV files

Posted by Sech in Tips on Mar 8th, 2008 | no responses

It took me a while to get this right. For future reference and for others who may need the same functionality here is the information:

Task:

1. Upload a CSV file to the server,
2. Use a CRON job to run a perl script that inserts the fields in the CSV file to a MYSQL database file.

Solution 1:

#!/usr/bin/perl

use DBI;
my $DSN = 'DBI:mysql:database';
my $dsn_user = 'database_user';
my $dsn_pw = 'database_user_password';
my $dbh = DBI->connect($DSN,$dsn_user,$dsn_pw)
or die "Couldn't connect to database: " . DBI->errstr;

$rows = $dbh->do("DELETE FROM table");

$result = $dbh->do("OPTIMIZE TABLE table");

# Load local comma separated, fields enclosed by quotes text database
#- File has to be in the same directory of this file or give full directory path
$result = $dbh->do("LOAD DATA LOCAL INFILE '/full_directory_path_to/data.csv' INTO TABLE table FIELDS TERMINATED BY ','ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (table_field1,table_field2,table_field3,so_on)");

This solution worked great on a server with a MYSQL 3.23

Challenge:

Time to move to a server with a MYSQL 4 or 5 where -local infile is disabled and it is not possible to get the web host do the necessary changes. You need a work-around fast... It is not easy to find a CSV parser in Perl that will parse a CSV file with comma separated columns that also has several columns that are enclosed by quotation marks ("). After trying several different solutions that either not worked at all or required major changes I found this here:

http://coding.derkeiler.com/Archive/Perl/comp.lang.perl.misc/2004-07/1638.html

sub parseCSVLine{
my $line=shift;
my @fields=();my $field=''; # initialize
my @fragments=split(/,/,$line,-1); # split into "," seperated fragments
foreach my $nibble(@fragments){
if($field){$field .= ','} # add the missing commas;
$field .= $nibble; # combine fragment into a field until...
my $count = $field =~tr/"/"/; # count quotes
unless($count % 2){ # ...there's an even number of double quotes
$field =~s/""/"/g; $field=~s/^\s*"//g; $field=~s/"\s*$//g; # fix quotes
# fix quotes
push @fields, $field;
$field=''; # reinitialize $field
}
}
return @fields;
}