Perl打开多个文件并导入到MySQL数据库中

Question

我希望将600多个txt文件导入到MySQL中，并希望使用perl和以下代码，但它似乎不起作用。在某种意义上，它连接，然后断开连接，但似乎没有按照指令打开每个文件和进程，所以我的导入表仍然是空的。该目录已经存在，所以除非我的代码中有明显的错误，否则我不确定哪里会出错。当我执行perl脚本时，没有收到错误消息。每个文件每行都有一个记录，用一个\n字符表示记录的结尾。我希望有人能帮助我，这个小问题令人困惑，我看不到任何明显的原因，为什么它不能工作。

use strict;
use warnings;
use DBI;

# Set datasource
my $dsn = "DBI:mysql:host=somehost;database=somedb"
        . ";port=someport";

# Connect to database 
my $dbh = DBI->connect($dsn,"someuser","somepassword");
print "Status: Connected to MySQL.\n";

my $dir = "t:\\some directory";

opendir (DIR, $dir) or die "Unable to open directory";
my @files = grep /\.txt/, readdir(DIR);
closedir(DIR);

foreach my $file (@files) {
open(FH, '<', "$dir\\$file") or die "Unable to open $file - $!\n";
while (<FH>){ 
    my $data = split (/\n$/);
    my $insert = $dbh->prepare('INSERT INTO sometable 
            (TEXTDATA,SOURCEFILE) 
        VALUES (?,?)') or die "Prepare failed: " . $dbh->errstr(); 
$insert->execute($data,$file) or die "Execute failed: " . $dbh->errstr(); 
}
close(FH);
}
print "Status: Processing of complete.\n";

# Disconnect from the database
$dbh->disconnect ();
print "Status: Disconnected from MySQL.";

Answer 1

你正在做的一些事情已经过时了。

• 使用正斜杠而不是反斜杠作为路径分隔符。
• 如果你的准备语句在每次迭代中都被准备好了，那么它就不会有多大的效果。应该在循环之外准备一次。默认情况下，
• 新行是记录分隔符。因此，不要使用split将一行数据存储到变量中，只需将该行赋值给variable.
• You're，而不检查在给定路径中是否确实找到了任何文件。

我做了如下更正：

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

# Set datasource
my $dsn = "DBI:mysql:host=somehost;database=somedb;port=someport";

# Connect to database 
my $dbh = DBI->connect($dsn,"someuser","somepassword")
    or die "Couldn't connect to MySQL server: $!";
say "Status: Connected to MySQL.";

my $source_dir = "t:/some/directory";

# Store the handle in a variable.
opendir my $dirh, $source_dir or die "Unable to open directory: $!";
my @files = grep /\.txt$/i, readdir $dirh;
closedir $dirh;

# Stop script if there aren't any files in the list
die "No files found in $source_dir" unless @files;

# You can comment out the following two lines
# once you're sure things are working
say 'Importing data from:';
say for @files;

my $insert = $dbh->prepare('INSERT INTO sometable (TEXTDATA,SOURCEFILE) '
                           . 'VALUES (?,?)')
                           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";

    # Don't use split. Just assign the line.
    while (my $line = <$fh>) {
       $insert->execute($line,$file)
           or die "Execute failed: " . $dbh->errstr(); 
    }

    close $fh;
}
print "Status: Processing of complete.\n";