For various reasons I had a bunch of csv files that should really have been in a database. cvs2sql is a little script that helped me put them there (relatively) painlessly. Perhaps it might be useful for others at some point.
#!/usr/bin/perl
=head1 NAME
csv2sql.pl
=head2 VERSION
0.1
=head1 SYNOPSIS
read a csv file and convert fields from first line into sql
insert statements
=head2 OPTIONS
=over
=item <dbname:tablename> name of the database and table into which we
+ want to insert
=back
=head1 REQUIREMENTS
Perl 5.8.4 (not tried on other versions)
Text::CSV_XS
IO::Handle
=head1 COPYRIGHT AND LICENCE
Copyright (C)2006 Charlie Harvey
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version
2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be
useful, but WITHOUT ANY WARRANTY; without even the implied
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
PURPOSE. See the GNU General Public License for more
details.
You should have received a copy of the GNU General Public
License along with this program; if not, write to the Free
Software Foundation, Inc., 59 Temple Place - Suite 330,
Boston, MA 02111-1307, USA.
Also available on line: http://www.gnu.org/copyleft/gpl.html
=head1 SEE ALSO
=cut
use strict;
use warnings;
use Text::CSV_XS;
use IO::Handle;
my $usage="Usage: $0 <dbname:tablename>";
die $usage unless($#ARGV==0);
my ($db,$table)=(split/:/,shift);
die $usage unless($db && $table);
my $sql = "INSERT INTO `$db`.`$table` ";
my $csv = Text::CSV_XS->new ({binary=>1});
my $in = IO::Handle->new;
my $out = IO::Handle->new;
$in->fdopen(fileno(STDIN), 'r') or die "Can't fdopen STDIN: $!\n";
$out->fdopen (fileno (STDOUT), "w") or die "Cannot fdopen STDOUT: $!\n
+";
$csv->parse($in->getline) or die ("Can't parse first line of STDIN! $!
+\n");
my $cols = "(";
my $col_count =0;
for ($csv->fields){
$cols .= "`$_`, ";
$col_count++;
}
$cols =~ s/, $//;
$sql .= "$cols) VALUES ";
while (!$in->eof) {
IO::Handle->input_record_separator("\n");
my $row = $csv->getline($in);
next unless defined $row ;
if((@$row) != $col_count) {
warn "Odd row: " ;
warn (join ", ",@$row);
warn "\nExpecting $col_count elements, got " . (@$row) . "\n";
next;
}
my $vals='';
for(@$row) {
$_=~s/"/\\"/g;
$vals.='"' .$_ .'", ';
}
$vals =~s/, $//;
$sql.= "($vals), \n";
}
undef $in;
$sql=~s/, $/;/;
$out->print($sql);
undef $out;
