Importing multiple csv files to mysql tables

Question 1

UPDATE

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

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: 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

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 imported successfully

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

11;9

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

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

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.

Question 2

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

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.

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

Question 3

The following works for me:

<?php

$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "pass");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare("INSERT INTO `null_test` (`can_be_null`) VALUES (:null)");
$stmt->bindValue(":null", null, PDO::PARAM_NULL);

$stmt->execute();

Pass in PHP's null, with type of PDO::PARAM_NULL. Also, make sure your prepare emulation is set to false. That might help.

Question 4

lastInsertId() is a method of the PDO class, not the PDOStatement class.

This should work:

$groupID = $dbo->lastInsertId();

Question 5

This should do it:

file_names <- dir("~/Desktop/data") 
df <- do.call(rbind, lapply(file_names, function(x) cbind(read.csv(x), name=strsplit(x,'\.')[[1]][1])))

Question 6

If your file is actually a csv file with fields separated by comma, you must specify the field separator.

LOAD DATA INFILE '$file' INTO TABLE $tbl_name FIELDS TERMINATED BY ','

If you don't state the field separator mysql considers the field separator as tab (t).

In case the file is created with return carriage, monstly windows applications do that, you also need to add in the end of the statement:

LINES TERMINATED BY 'rn'

Importing multiple csv files to mysql tables

Answers

php,mysql,pdo