php: importing multiple csv files into mysql table

Question

<?php

error_reporting(E_ALL);
ini_set('memory_limit', '512M');
ini_set('display_errors', 'ON');

$host = "127.0.0.1"; // Host name
$username = "root"; // Mysql username
$password = ""; // Mysql password
$db_name = "test2"; // Database name
$tbl_name = "tcg_unique";

$con = mysql_connect("$host", "$username", "$password")or die("cannot connect");
$db_con = mysql_select_db($db_name) or die("cannot select DB");
$charset = mysql_set_charset('utf8',$con);

$dir_iterator = new RecursiveDirectoryIterator("/Users/jacksons/Dropbox/MTG/SQL staging");
$iterator = new RecursiveIteratorIterator($dir_iterator, RecursiveIteratorIterator::SELF_FIRST);

foreach ($iterator as $file) {
echo $file, "\n";
if(strpos($file, '.csv') !== false){
mysql_query("LOAD DATA LOCAL INFILE '$file' INTO TABLE $tbl_name") or die (mysql_error());
}
else{
    print "else";
}
}

I am tying to load multiple csv files from a directory. I am getting all the file path's to print out, but i am not able to get the data to load into the tables. I tried one in mysql and it imported with no problem (all the column names matched). Any help would be greatly appreciated.

csv looks like: cardname vendor condition price shipping quantity Date Brimaz, King of Oreskos Game Citadel Near Mint 18 0.5 4 5/30/14 Kiora, the Crashing Wave FTW Games Lightly Played 13.09 NA 1 5/30/14 Courser of Kruphix Chicagoland Games Near Mint 11.68 0.75 3 5/30/14

You're generating your LOAD DATA LOCAL query, but not actually running it - shouldn't it be inside a call to mysql_query()? — andrewsi
– andrewsi, Commented Jul 16, 2014 at 17:46
i had tried it like this orginally, but still getting no data — six7zero9
– six7zero9, Commented Jul 16, 2014 at 18:44
You need to use mysql_query to run it. Try mysql_query("LOAD DATA LOCAL INFILE '$file' INTO TABLE '$tbl_name'") or die (mysql_error()) to see what error you get. — andrewsi
– andrewsi, Commented Jul 16, 2014 at 19:55
OK. So your code will either load your data; die with an error message; or print out else. What do you get? — andrewsi
– andrewsi, Commented Jul 16, 2014 at 20:43
sorry I think i messed up the comment ... It actually looks like its loading the files, but the fields are empty or null. — six7zero9
– six7zero9, Commented Jul 16, 2014 at 20:53

Erico · Accepted Answer · 2014-07-16 21:33:53Z

1

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 '\r\n'

edited Jul 16, 2014 at 21:33

answered Jul 16, 2014 at 20:45

Erico

1,47110 silver badges18 bronze badges

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

8 Comments

six7zero9 Over a year ago

that did help, but the data is going in different than when i manually do one. I can just hit import and it has everything set up right, but when i try it this way it leaves off the first column and which shifts everything to the left one.

Erico Over a year ago

Can you please post 3 or 4 lines of your csv file to your topic ? Thanks.

six7zero9 Over a year ago

it isnt listing it right, but it is an excel file with "cardname vendor condition price shipping quantity" as the top of each column and the other stuff obviously goes in the corresponding column

six7zero9 Over a year ago

ok i think it might have something to do with the "first line contains field names" box that i have checked when i import a single one. also might need the other settings there

Erico Over a year ago

If you're exporting the files from excel you also need to add to the statement: "LINES TERMINATED BY '\r\n'

|

arielnmz · Accepted Answer · 2014-07-16 21:12:49Z

1

If you want to import several csv files and you're doing it manually, you might also want to use a graphical tool to make the job easier. You can use Adminer for this:

Access adminer from your browser.
Log in with admin rights.
Select your database.
Click on the Import option on the left.
Choose your .csv file.
Press the Execute button.

answered Jul 16, 2014 at 21:12

arielnmz

9,21510 gold badges43 silver badges73 bronze badges

Collectives™ on Stack Overflow

php: importing multiple csv files into mysql table

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related