Month: August 2009

Importing a spreadsheet, or its comma separated (CSV) equivalent, into a MySQL database can be enormously challenging and frustrating.

After many attempts at the solutions available through online searches, I determined that the most effective way was to save the spreadsheet in comma-separated format and write a PHP script to read the file row by row and insert the records in the database.

You'll need to adapt this script to your situation, the example below is keyed to the table I've been working with.
[sourcecode language="php"]
";
// ~~~~~~~~~~~~~~~~~~~~~~~~~ /create attribute list ~~~~~~~~~~~~~~~~~~~~~~~~~ //
// ~~~~~~~~~~~~~~~~~~~~~~~~~ PROCESS DATA LINES ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //
foreach($lines as $line_num => $line) {
 // trim the line – any whitespace can complicate life later
 $line = trim($line);

 // explode the line into an array, splitting it on the commmas in the csv.
 $values = explode(",",$line);
 // prepare the beginning of the INSERT statement
 $insert_statement = "INSERT INTO packages ($attribute_list) VALUES (";
 $insert_statement = "INSERT INTO packages (attribute_one,attribute_two,attribute_three,attribute_4,attribute_5) VALUES (";
 // now loop through the array, adding the elements to the values() section of the insert statement
 foreach ($values as &$value) {
  // according to
  //  http://bytes.com/groups/php/1146-insert-null-not-blank-into-mysql-php-script
  // this will insert null for empty elements
  if ($value) {
   $insert_statement .= "'" . mysql_escape_string($value) . "'";
  } else {
   $insert_statement .= "NULL"; // in the SQL query "NULL" will NOT be quoted
  }
  $insert_statement .= ",";
 }
 // now lop off the trailing string…
 $insert_statement = substr($insert_statement,0,-1);
 // … and close the statement
 $insert_statement .= ");";

 // now we'll either execute the statement or display it onscreen
 // (by default we only display onscreen for debugging – user must pass query string to execute)
 if ($_GET['execute'] == "1") {
  // if the execute instruction is passed in the query string, "?execute=true", execute the insert statement against the database
  $result = mysql_query($insert_statement);
  echo "Inserted record
";
 } else {
  // otherwise, display the insert statement onscreen for debug purposes
  echo $insert_statement;
  echo "
";
 }

}
// ~~~~~~~~~~~~~~~~~~~~~~~~~ /process data lines ~~~~~~~~~~~~~~~~~~~~~~~~~~~ //
?>
[/sourcecode]