Iterate through CSV files in a folder and load data into MySQL using PHP

Question

I use the following code to iterate through the files in a folder and have the data loaded onto a MYSQL table. I couldn't quite get this working. Is there any better solution?

if ($handle = opendir('C:\Documents and Settings\Desktop\CSV Files')) {
while(false !== ($entry = readdir($handle)))
{
mysql_query("LOAD DATA LOCAL INFILE %handle
                          INTO TABLE main_table
                          FIELDS
                          TERMINATED BY ','
                          OPTIONALLY ENCLOSED BY '"'
                          ESCAPED BY '"'
                          LINES TERMINATED BY '\r\n'
                          IGNORE 1 LINES")
}
}

What do you mean by you couldn't quite get it to work? Shouldn't the ESCAPED BY part be '\\', by the way? — andrewsi
– andrewsi, Commented Apr 11, 2013 at 19:03

Sammitch · Accepted Answer · 2013-04-11 19:55:12Z

1

There is so much wrong with your code it's hard to pick where to begin...

Your directory path is not escaped.
You do not have anything to tell you that the directory cannot be opened.
%handle is not a PHP variable, and it's the wrong variable besides.
$entry is just the filename, not the full path.
there's no semicolon at the end of the mysql_query() call.
Your 'enclose' and 'escape' chars are both ", neither of which were escaped.
echo the query instead of running it to be sure it's formed correctly if it's not working.
You never actually check to see if the query was successful.
turn on error reporting, because you've got some glaring parse errors that should make this script fail before it even runs.
and the ever-popular: mysql_* functions are deprecated, you should be using PDO or MySQLi.

So this should probably work a:

<?php
error_reporting(E_ALL);
define('DEBUG', true);

$basedir = 'C:\\Documents and Settings\\Desktop\\CSV Files\\';
if ($handle = opendir($basedir)) {
  while(false !== ($entry = readdir($handle))) {
    $query = "LOAD DATA LOCAL INFILE $basedir$entry
                INTO TABLE main_table
                FIELDS
                TERMINATED BY ','
                OPTIONALLY ENCLOSED BY '\"'
                ESCAPED BY '\\'
                LINES TERMINATED BY '\r\n'
                IGNORE 1 LINES");
    if(DEBUG) { echo $query . "\n"; }
    if(!mysql_query($query)) {
      die('Query failed: ' . mysql_error());
    }
  }
} else {
  echo "Could not open $basedir";
}

answered Apr 11, 2013 at 19:55

Sammitch

32.5k7 gold badges58 silver badges92 bronze badges

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

3 Comments

Kumaran Senapathy Over a year ago

I added the connection to your code: $con=mysql_connect("localhost","root","root") or die("Failed to connect with database!!!!"); mysql_select_db("some_db", $con); and deleted the ')' after "ignore 1 lines"

Kumaran Senapathy Over a year ago

I get the following error: LOAD DATA LOCAL INFILE C:\Documents and Settings\Desktop\CSV Files\. INTO TABLE main_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY ' ' IGNORE 1 LINES Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'C:\Documents and SettingsDesktop\CSV Files\. IN' at line 1

clover Over a year ago

You have missed quotes around filename, try $query = "LOAD DATA LOCAL INFILE '$basedir$entry' INTO TABLE main_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES");

Collectives™ on Stack Overflow

Iterate through CSV files in a folder and load data into MySQL using PHP

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related