将多个csv文件导入到mysql表 [英] Importing multiple csv files to mysql tables

问题描述

更新

第二个问题发布后，由于结果查询的语法突出显示，我发现出了问题所在:$pl字符串没有以闭合的勾号打开.现在，我将其更改为:

The second after I posted this question, thanks to the syntax highlighting of the resulting queries I saw what went wrong: the $pl string didn't open with a closing back tick. Now I changed it to:

$pk = ',`%1$sUSER`,`%1$sTYPE`,`%1$sCODE`,`%1$sVALUE`,`%1$s';//WRONG
$pk = '`,`%1$sUSER`,`%1$sTYPE`,`%1$sCODE`,`%1$sVALUE`,`%1$s';//OK

但是，这提出了一个新问题:为什么PDO对象没有为此向我吐出错误?手动执行查询肯定会返回错误，提示没有名为fld2_AGE,的字段，最后是逗号.为什么我没有收到任何错误?有任何想法吗?

However, this poses a new question: why didn't the PDO object spew errors back at me for this? executing the query manually surely would return an error saying there is no field called fld2_AGE,, with a comma at the end. Why didn't I get any errors? any Ideas?

PS:有什么想法可以解决SO的语法突出显示问题，从而解决我的问题吗? :-)

PS: any idea's how to upvote SO's syntax highlighting for solving my problem? :-)

我将原始问题留作参考/示例，尽管它不再需要解决

I'm leaving the original question as a reference/example, though it doesn't need solving anymore

好吧，我一直使用我写了一段时间的脚本来从一个巨大的文件中导入生成多个csv文件，以便将数据快速导入到多个mysql表中.在我编写第二个脚本之前，该脚本在过去一直运行良好(我认为)，而在该脚本中，我不小心截断了我的表(愚蠢，我知道).我想没什么大不了的"，因为有了脚本，还原数据只需几秒钟.不幸的是，我发现现在仅导入了一个文件，并且没有显示错误.下面，我粘贴了脚本的整个db部分.当我执行此代码时，我得到的只是输出files imported successfully，这是代码的最后一行...

Ok, I've been using a script I wrote a while back to import generate several csv files from a huge file, to quickly import the data into several mysql tables. This script has worked failry well in the past (I think) up until I wrote a second script, in which I accidentally truncated my tables (stupid, I know). 'No biggie' I thought, as I had the the script, it would be a matter of seconds to restore my data. Unfortunately, I found that now, only one file is being imported, and no errors are showing. Below I have pasted the entire db section of the script. When I execute this code, all I get is the output files imported successfully, which is the very last line of code...

我知道这是一个繁琐的大代码块，带有大量字符串格式打印，但这并不能提高可读性，因此我还在下面提供了结果查询字符串.据我所知，它们和文件(我检查过)的格式都很好.谁能说出我应该在哪里寻找错误?这将是一个很大的帮助...谢谢！

I know it's a failry large block of code, with a lot of string format printing, which doesn't improve readability, so I've also provided the resulting query strings below. As far as I can tell, they look well formatted, as do the files (I checked). Can anyone tell where else I'm supposed to look for errors? It would be a great help... Thanks!

<?php
$files = array_fill_keys(array('filename1','filename2','filename3','filename4'),'');
//$files === array of handles fputcsv($files['filename1'],array('values','from','other','files'),';');
$tbls = array_combine($files,array('tblname1','tblname2','tblname3','tblname4'));
$path = dirname(__FILE__)'/';
$qf = 'LOAD DATA LOCAL INFILE \'%s%s.csv\' INTO TABLE my_db.tbl_prefix_%s FIELDS TERMINATED BY \';\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\n\'';
$pref = array_combine($files,array('fld1_','fld2_','fld3_','fld3_'));
$pkA = ' (`%1$sNAME`,`%1$sAGE';
$pk = '`,`%1$sUSER`,`%1$sTYPE`,`%1$sCODE`,`%1$sVALUE`,`%1$s';
try
{
    $db = new PDO('mysql:host=mysqlhostn','user','pass');
    foreach($files as $f)
    {
        $db->beginTransaction();
        $db->exec(sprintf('TRUNCATE TABLE my_db.tbl_prefix_%s',$tbls[$f]));
        $db->commit();
    }
}
catch(PDOException $e)
{
    if ($db)
    {
        $db->rollBack();
        $db = null;
    }
    die('DB connection/truncate failed: '.$e->getMessage()."\n");
}
try
{
    while($f = array_shift($files))
    {
        $db->beginTransaction();
        $q = sprintf($qf,$path,$f,$tbls[$f]).sprintf($pkA.($f !== 'agent' ? $pk : ''),$pref[$f]);
        switch($f)
        {
            case 'filename3':
                $q .= 'tbl3_specific_field';
            break;
            case 'filename2':
                $q .= sprintf('tbl2_specific_field`,`%1$tbl2_specific_field2',$pref[$f]);
            break;
            case 'filename4':
                $q .= sprintf('tbl4_specific_field`,`%1$tbl4_specific_field2`,`%1$tbl4_specific_field3`,`%1$tbl4_specific_field4',$pref[$f]);
            break;
        }
        $stmt = $db->prepare($q.'`)');
        $stmt->execute();
        $db->commit();
    }
}
catch(PDOException $e)
{
    $db->rollBack();
    $e = 'CSV import Failed: '.$e->getMessage();
    $db=null;
    die($e."\n");
}
$db = null;
exit('files imported successfully'."\n");
?>

生成的查询-执行输出:

generated Queries - execution output:

TRUNCATE TABLE my_db.tbl_prefix_tblname1
TRUNCATE TABLE my_db.tbl_prefix_tblname2
TRUNCATE TABLE my_db.tbl_prefix_tblname3
TRUNCATE TABLE my_db.tbl_prefix_tblname4

LOAD DATA LOCAL INFILE '/local/path/to/files/filename1.csv' INTO TABLE my_db.tbl_prefix_tblname1 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (`fld1_NAME`,`fld1_AGE`)
LOAD DATA LOCAL INFILE '/local/path/to/files/filename2.csv' INTO TABLE my_db.tbl_prefix_tblname2 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (`fld2_NAME`,`fld2_AGE,`fld2_USER`,`fld2_TYPE`,`fld2_CODE`,`fld2_VALUE`,`fld2_tbl2_specific_field`,`fld2_tbl2_specific_field2`)
LOAD DATA LOCAL INFILE '/local/path/to/files/filename3.csv' INTO TABLE my_db.tbl_prefix_tblname3 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (`fld3_NAME`,`fld3_AGE,`fld3_USER`,`fld3_TYPE`,`fld3_CODE`,`fld3_VALUE`,`fld3_tbl3_specific_field`)
LOAD DATA LOCAL INFILE '/local/path/to/files/filename4.csv' INTO TABLE my_db.tbl_prefix_tblname4 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (`fld4_NAME`,`fld4_AGE,`fld4_USER`,`fld4_TYPE`,`fld4_CODE`,`fld4_VALUE`,`fld4_tbl4_specific_field`,`fld4_tbl4_specific_field2`,`fld4_tbl4_specific_field3`,`fld4_tbl4_specific_field4`)

csv成功导入

csv imported successfully

我需要导入File1，这是第一个文件的示例:

File1 is getting imported as I need it to be, an example of the first file:

11; 9

11;9

file2的实际内容(未导入)如下所示:

While the actual content of file2 (which isn't getting imported) looks like this:

11; 9; 25; 5; FOOBAR; Z; 333; 321; 123

11;9;25;5;FOOBAR;Z;333;321;123

第一个字段都包含相同的数据，两个表都具有相同的字段定义，相同的存储引擎(InnoDB)，排序规则(UTF-8)...我不知道是什么引起了这个问题，所以建议将不胜感激.

Both first fields contain the same data, as they should, both tables have the same field definitions, same storage engine (InnoDB), collation (UTF-8)... I have no idea what's causing the problem, so any advice would be greatly appreciated.

推荐答案

为什么PDO对象没有为此向我吐出错误?

why didn't the PDO object spew errors back at me for this?

因为MySQL执行了您的查询而没有任何错误.仅仅因为您编写了错误的查询，这并不意味着该查询是MySQL不会接受的错误.

Because MySQL executed your query without any errors. Only because you've written the wrong query this must not mean that the query is that wrong that MySQL won't accept it.

无论何时以编程方式生成SQL查询，都要(通过调试或更好的单元测试)验证是否已针对您要执行的操作正确创建了查询.

Whenever you generate SQL queries programmatically, verify (by debugging or even better unit-tests), that the query has been created right for what you want to do.

如果您希望在每次发生错误时都获得异常，请启用该功能:

If you want to get an exception each time an error occurs, enable that:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

这篇关于将多个csv文件导入到mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！