From CSV to MySQL Database Using Perl

Question

I tried to upload some data from CSV to MySQL database - But it not working

Below one is my code

#!/usr/bin/perl -w
use DBI;
use strict;
use TEXT::CSV;
use warnings;

my $driver       = "mysql"; 
my $database     = "test";
my $host         = "localhost"
my $databaseport = "3306";
my $userid       = "root";
my $password     = "password";
my $csv          = "C:/Perl/scripts/table.csv";

my $dsn          = "dbi:mysql:dbname=$databasename;host=$dbhost;port=$dbport;";

open (CSV, "$csv") or die "Couldn't open csvfile: $!";
my $dbh = DBI->connect($dsn, $userid, $password,{ RaiseError => 1})
or die "Could not connect to database! $DBI::errstr";
{ 
 local $/ = undef; 
  $dbh->do("INSERT INTO student (stud_id,stud_name,dept_id,stud_mark,stud_address) 

  values (?, ?, ?, ?, ?)", undef, <CSV>);
 }
 $dbh->disconnect;
close CSV;

Answer 1

There are a few issues here. I'll list the ones that will give you error messages first.

• There is no module TEXT::CSV. There is one called Text::CSV though.
• You are using 5 placeholders in your query, but you are passing the first line of the csv file through the diamond operator <CSV>. That will give an error message.

Then there are problems with your logic. You are passing the complete file to the DB (as the first argument). That does not make sense. You need to split the input or use Text::CSV to do it and read the file line by line.

Furthermore, it is good practice nowadays to use open with three arguments and make the filehandle lexical.

I've written all of this up as an example with self-made CSV handling. If your file is more complex, read up on Text::CSV und use it.

use DBI;
use strict;
use warnings;

my $csv          = "C:/Perl/scripts/table.csv";
# omitted settings here ...

my $dbh = DBI->connect($dsn, $userid, $password,{ RaiseError => 1})
  or die "Could not connect to database! $DBI::errstr";
open (my $fh, '<', $csv) 
  or die "Couldn't open csvfile: $!";

# prepare statement handle for reuse in the loop
my $sth = $dbh->prepare(qq{
  INSERT INTO student(stud_id,stud_name,dept_id,stud_mark,stud_address) 
  VALUES (?, ?, ?, ?, ?)});

# read the file line by line
while (my $line = <$fh>) {
  chomp $line; # remove newline
  $sth->execute( split /;/, $line ); # assuming the separator is a semicolon 
}

close $fh;
# DB handle will disconnect implicitly on end of program

As you can see, I decided to prepare the statement up front and reuse it. That saves a lot of time in the loop, because the DB will remember the statement.