CODEWITHSUNDEEP
mysqlperlcsv
BlueStarry
BlueStarry

Reputation: 717

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

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()

Upvotes: 0

Views: 1141

Answers (1)

Matt Jacob
Matt Jacob

Reputation: 6553

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.

Upvotes: 2

Related Questions