A Perl script to import multiple CSV files into a MySQL database

Question

I have this Perl script:

use v5.12;
use strict;
use warnings;
use DBI;


my $dsn = "DBI:mysql:host=localhost;database=testitt";


my $dbh = DBI->connect($dsn,"root","")
    or die "No db connectin: $!";
say "Connected to MySQL.";


my $source_dir = "C:/Users/ST/Desktop/";


opendir my $dirh, $source_dir or die "Unable to open directory: $!";
my @files = grep /\.csv$/i, readdir $dirh;
closedir $dirh;


die "No files found in $source_dir" unless @files;

say 'Importing data from:';
say for @files;

my $load = $dbh->prepare("LOAD DATA INFILE ? INTO TABLE tablea Character Set utf8 FIELDS TERMINATED BY '        ' LINES TERMINATED BY '\n' IGNORE 1 LINES (id, normalized_count)")
                           or die "Prepare failed: " . $dbh->errstr(); 

for my $file (@files) {
    say "Processing $source_dir/$file";
    open my $fh, '<', "$source_dir/$file"
    or die "Unable to open $source_dir/$file: $!\n";
       $load->execute($file)
           or die "Execute failed: " . $dbh->errstr(); 
}
print "Jobs done.\n";

It gives me error at the $load->execute($file) because it's unable to find the file, but I've already loaded everything on @file early in the code.

EDIT: Thanks to matt suggestion my for now is cleaner but i can't figure out a way to put the current file into the query (the token), below an attempt but gives error:

for my $file (@files) {
        my $statement = $dbh->prepare("LOAD DATA INFILE C:\\Users\\ST\\Desktop\\"$file"INTO TABLE rnaseq Character Set utf8 FIELDS TERMINATED BY '        ' LINES TERMINATED BY '\n' IGNORE 1 LINES (id,normalized_count)");
$statement->execute()

A few things: 1. You don't need to open each file for reading within the loop (MySQL is doing that part). 2. Can you even use a placeholder there? 3. You should use an absolute path for the filename, otherwise "the server looks for the file in the database directory of the default database" (source). — Matt Jacob
– Matt Jacob, Commented Oct 23, 2015 at 21:29

Matt Jacob · Accepted Answer · 2015-10-24 21:57:33Z

2

The main problem is that it looks like the behavior of LOAD DATA INFILE is not intuitively obvious when you supply relative paths, which you are.

If LOCAL is not specified, the file must be located on the server host and is read directly by the server. The server uses the following rules to locate the file:

If the file name is an absolute path name, the server uses it as given.

If the file name is a relative path name with one or more leading components, the server searches for the file relative to the server's data directory.

If a file name with no leading components is given, the server looks for the file in the database directory of the default database.

So, I would recommend constructing an absolute path to each file instead of relying on the more mysterious second and third options.

Regarding your edit, I noticed a couple problems. You need to quote the entire path, and I'd stick with forward slashes, like this:

for my $file (@files) {
    $dbh->do(qq{
        LOAD DATA INFILE 'C:/Users/ST/Desktop/$file' INTO TABLE rnaseq 
        CHARACTER SET utf8 
        FIELDS TERMINATED BY '        ' 
        LINES TERMINATED BY '\\n' 
        IGNORE 1 LINES (id, normalized_count)
    });
}

If that doesn't work, I'd probably just print the statements and pipe them into the mysql command.

edited Oct 24, 2015 at 21:57

answered Oct 23, 2015 at 21:38

Matt Jacob

6,5732 gold badges27 silver badges27 bronze badges

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

7 Comments

Matt Jacob Over a year ago

Thanks. I'm still not 100% sure that the placeholder isn't also causing a problem, though. From DBI: With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it. Does it prevent the server from doing that here? Ehhhh... I don't know.

BlueStarry Over a year ago

Hi Matt thanks for you help is really appreciated. Unfortunately i don't know how to apply your suggestion so if you can be more specific that would help me a lot. Thanks.

Matt Jacob Over a year ago

@BlueStarry You're already doing the right thing when you build an absolute path in the open() (which isn't necessary, by the way): "$source_dir/$file". The only question is whether MySQL on Windows can handle forward slashes, but I think it should.

BlueStarry Over a year ago

Maybe it's my english broken but i'm not understanding: first you say you don't need the open, then you question the relative paths and then you tell me you're right to use absolute paths in the open but it's not necessary but still, the script doesn't work.

Matt Jacob Over a year ago

1. You don't need the open(). 2. You should supply an absolute path to LOAD DATA INFILE, just like you're doing in 3 other places.

|

Collectives™ on Stack Overflow

A Perl script to import multiple CSV files into a MySQL database

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related