Issues Importing CSV file into MySQL database

[cmdlinegeek]

Not receiving any errors after compiling, but the script isn't reading the CSV file into the MySQL table. Can someone point me in the right direction? Many thanks.

#!/usr/bin/perl -w

use DBI;
use strict;
use warnings;



# Declare varaibles
my $DBNAME   =  "Test";
my $DBTABLE  =  "Pandora";
my $DBUSER   =  "slacker";
my $DBPASS   =  "password";
my $DBHOST   =  "localhost";
my $csvfile  =  "/tmp/nowplayingtest.csv";

# Connect to database at hand, or die
my $dbh = DBI->connect("DBI:mysql:$DBNAME:$DBHOST", $DBUSER, $DBPASS)
   or die "Cannot connect to the database";

open (INFILE, $csvfile) 
	or die "Can't open file!";
  while (<INFILE>) 
  {	
  chomp;
  my @rows = split(';', $_);
   
  my $artist  =  $rows[0];
  my $title   =  $rows[1];
  my $album   =  $rows[2];
  
  $dbh->do(qq/insert into "$DBTABLE" (Artist, Title, Album) values ($artist, $title, $album/) or warn "failed to insert $artist, $title, $album into table - $dbh->errstr" if ($@);

  my $res = $dbh->selectall_arrayref( q( SELECT Artist, Title, Album FROM Pandora));

  foreach( @$res ) 
	{
	
	print "\n$_->[0], $_->[1], $_->[2]\n\n";

	}
}

$dbh->disconnect;

 

[ChrisHunt]

You've got the bit of your script which prints out the contents of the table inside the loop which poulates the table. Is that really what you want?

That's not the cause of the problem though. The problem is that you need quotes around the artist/title/album values when you insert them.

However, there's a better way to do inserts that'll run quicker and cope with potential nasties like having special characters (like ' ) in field values. Here's just the loading loop:

[red]$sth = dbh->prepare("insert into $DBTABLE (Artist, Title, Album) values (?, ?, ?)")
    or die "Can't prepare SQL";[/red]
open (INFILE, $csvfile) 
    or die "Can't open file!";
  while (<INFILE>) 
  {    
  chomp;
  my @rows = split(';', $_);
   
  my $artist  =  $rows[0];
  my $title   =  $rows[1];
  my $album   =  $rows[2];
  
  [red]$sth->execute ($artist, $title, $album)[/red] or warn "failed to insert $artist, $title, $album into table - $dbh->errstr" if ($@);
[red]}[/red]

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[MacTommy]

Something else you might want to look into is the MySQL documentation on loading data from a (possibly comma separated) file.
That is way faster than using a script to do it line by line.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

And if you want to do it with a Perl script anyway you might want to collect all the insert values first and then run one insert query for all the values.
So build one query that goes
"INSERT INTO table (col1, col2) VALUES ('a', 'b'), ('c', 'd), ('e', 'f'), etc..."
And issue that query once, rather than doing one insert query per line.

Also, as a complete BTW, if you use /usr/bin/perl -w you don't need to 'use warnings' (it is the same if I am correct...).

 

[cmdlinegeek]

I made the changes and added the statement handler, no errors, but its still not adding "one" line from the CSV file. The CSV file has only one line, so there is no need for a fast and efficient process. I just want the script to read the line and add it to the already existing table as an additional entry.

I also removed the loop for displaying the already existing entry, there was really no need for it, only some feedback.

TIA

 

[tsuji]

Try modify the corresponding line see what you get, it should work.
[tt]
$sth->execute ($artist, $title, $album)
or warn "failed to insert $artist, $title, $album into table - " . $sth->errstr;
[/tt]

 

[MillerH]

Your problem arises because your insert statement will only run if there is an eval error, $@. Just use the following code:

[gray]#!/usr/bin/perl[/gray]

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]DBI[/green][red];[/red]
[black][b]use[/b][/black] [green]strict[/green][red];[/red]
[black][b]use[/b][/black] [green]warnings[/green][red];[/red]

[gray][i]# Declare varaibles[/i][/gray]
[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$DBNAME[/blue]   =  [red]"[/red][purple]Test[/purple][red]"[/red][red];[/red]
[black][b]my[/b][/black] [blue]$DBTABLE[/blue]  =  [red]"[/red][purple]Pandora[/purple][red]"[/red][red];[/red]
[black][b]my[/b][/black] [blue]$DBUSER[/blue]   =  [red]"[/red][purple]slacker[/purple][red]"[/red][red];[/red]
[black][b]my[/b][/black] [blue]$DBPASS[/blue]   =  [red]"[/red][purple]password[/purple][red]"[/red][red];[/red]
[black][b]my[/b][/black] [blue]$DBHOST[/blue]   =  [red]"[/red][purple]localhost[/purple][red]"[/red][red];[/red]
[black][b]my[/b][/black] [blue]$csvfile[/blue]  =  [red]"[/red][purple]/tmp/nowplayingtest.csv[/purple][red]"[/red][red];[/red]

[gray][i]# Connect to database at hand, or die[/i][/gray]
[black][b]my[/b][/black] [blue]$dbh[/blue] = DBI->[maroon]connect[/maroon][red]([/red][red]"[/red][purple]DBI:mysql:[blue]$DBNAME[/blue]:[blue]$DBHOST[/blue][/purple][red]"[/red], [blue]$DBUSER[/blue], [blue]$DBPASS[/blue][red])[/red]
	or [url=http://perldoc.perl.org/functions/die.html][black][b]die[/b][/black][/url] [red]"[/red][purple]DB connect failed: [blue]$DBI::errstr[/blue][/purple][red]"[/red][red];[/red]

[black][b]my[/b][/black] [blue]$update_h[/blue] = [blue]$dbh[/blue]->[maroon]prepare[/maroon][red]([/red][red]qq{[/red][purple]INSERT INTO $DBTABLE (Artist, Title, Album) VALUES (?, ?, ?)[/purple][red]}[/red][red])[/red][red];[/red]

[url=http://perldoc.perl.org/functions/open.html][black][b]open[/b][/black][/url] [black][b]my[/b][/black] [blue]$ih[/blue], [blue]$csvfile[/blue] or [black][b]die[/b][/black] [red]"[/red][purple]Can't open file, [blue]$csvfile[/blue]: [blue]$![/blue][/purple][red]"[/red][red];[/red]

[olive][b]while[/b][/olive] [red]([/red]<[blue]$ih[/blue]>[red])[/red] [red]{[/red]
	[url=http://perldoc.perl.org/functions/chomp.html][black][b]chomp[/b][/black][/url][red];[/red]
	
	[black][b]my[/b][/black] [blue]@rows[/blue] = [url=http://perldoc.perl.org/functions/split.html][black][b]split[/b][/black][/url] [red]'[/red][purple];[/purple][red]'[/red][red];[/red] [gray][i]# Is a semicolon your delimiter, or is it a comma?[/i][/gray]

	[black][b]my[/b][/black] [blue]$artist[/blue]  =  [blue]$rows[/blue][red][[/red][fuchsia]0[/fuchsia][red]][/red][red];[/red]
	[black][b]my[/b][/black] [blue]$title[/blue]   =  [blue]$rows[/blue][red][[/red][fuchsia]1[/fuchsia][red]][/red][red];[/red]
	[black][b]my[/b][/black] [blue]$album[/blue]   =  [blue]$rows[/blue][red][[/red][fuchsia]2[/fuchsia][red]][/red][red];[/red]

	[blue]$update_h[/blue]->[maroon]execute[/maroon][red]([/red][blue]$artist[/blue], [blue]$title[/blue], [blue]$album[/blue][red])[/red] or [black][b]die[/b][/black] [blue]$dbh[/blue]->[maroon]errstr[/maroon][red];[/red]
[red]}[/red]

[black][b]my[/b][/black] [blue]$res[/blue] = [blue]$dbh[/blue]->[maroon]selectall_arrayref[/maroon][red]([/red] [red]q([/red][purple] SELECT Artist, Title, Album FROM Pandora[/purple][red])[/red][red])[/red][red];[/red]

[olive][b]foreach[/b][/olive][red]([/red] [blue]@$res[/blue] [red])[/red] [red]{[/red]
	[url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] [red]"[/red][purple][purple][b]\n[/b][/purple][blue]$_[/blue]->[0], [blue]$_[/blue]->[1] [blue]$_[/blue]->[2][purple][b]\n[/b][/purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
[red]}[/red]

[tt]------------------------------------------------------------
Pragmas (perl 5.10.0) used :
[ul]
[li]strict - Perl pragma to restrict unsafe constructs[/li]
[li]warnings - Perl pragma to control optional warnings[/li]
[/ul]
Other Modules used :
[ul]
[li]DBI[/li]
[/ul]
[/tt]- Miller

 

[cmdlinegeek]

MillerH,

Much oblige, the code works perfectly.

Regards,

Robert