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;

You are not making use of Text::CSV at all. What you have now will pass a complete line of CSV as a string including the newline as the first ? to MySQL. You should be getting an error like expects 5 params but called with 1. — simbabque
– simbabque, Commented Feb 19, 2014 at 12:31
the error is like : Can't locate TEXT/CSV.pm in @INC (@INC contains: C:/Perl/site/lib C:/Perl/lib .) at C:\Perl\scripts\upload.pl line 4. BEGIN failed--compilation aborted at C:\Perl\scripts\upload.pl line 4. — Benny
– Benny, Commented Feb 19, 2014 at 12:36
That's because there is no such module. Perl's module names are case-sensitive. See my answer for an explanation on this, and more. — simbabque
– simbabque, Commented Feb 19, 2014 at 12:43

simbabque · Accepted Answer · 2014-02-19 12:43:01Z

8

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.

answered Feb 19, 2014 at 12:43

simbabque

54.4k8 gold badges77 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Benny Over a year ago

will u tell me about this error : Global symbol "$userid" requires explicit package name at D:\Perl programs\uploa d1.pl line 11.

simbabque Over a year ago

It means the variable is not declared. I said I left out some variables because of length. You need to add them back in.

tobyink · Accepted Answer · 2014-02-19 12:48:57Z

Reading from a filehandle in list context (i.e. the <CSV> bit in your code) reads all the lines from the file and returns them as a list. So your ?, ?, ?, ? placeholders each get given a whole line from the file (including the line break character at the end). For some of the fields (perhaps dept_id?) this may not be a valid value, so the INSERT statement fails.

Though actually, you're also setting $/ to undef, which makes it even wrongerer. $/ changes Perl's notion of a new line when it's reading text files. Setting it to undef means that Perl will consider the entire file to be a single line.

At a guess, what you're trying to do is read the CSV file one line at a time, and pump each into the database.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Text::CSV;  # case-sensitive!

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";

# Connect to database.
my $dsn = "dbi:mysql:dbname=$databasename;host=$dbhost;port=$dbport;";
my $dbh = DBI->connect($dsn, $userid, $password,{ RaiseError => 1})
  or die "Could not connect to database! $DBI::errstr";

# DBI can be more efficient if you prepare the SQL query once, and then
# execute it multiple times, rather than calling `do` for each insert.
my $sth = $dbh->prepare(<<'SQL');
  INSERT INTO student (stud_id,stud_name,dept_id,stud_mark,stud_address)
  VALUES (NULL, ?, ?, ?, ?)"
SQL

# Open the CSV file.A
open my $CSV, '<', $csv
  or die "Couldn't open csvfile: $!";

# Create an instance of Text::CSV.
my $reader = Text::CSV->new;

# Use Text::CSV to read a line.
while (my $row = $reader->getline($CSV))
{
  # Insert into database.
  $sth->execute( @$row );
}

# Clean up (optional; Perl will do this when your script ends anyway).
$dbh->disconnect;
close $CSV;

Collectives™ on Stack Overflow

From CSV to MySQL Database Using Perl

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related