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.
<?php
// ----------------------------------------------------------------------
// IMPORT A .CSV FILE INTO A MYSQL DATABASE
// ----------------------------------------------------------------------// Assumptions:
// - The input file is a .xls spreadsheet saved as .csv, comma delimited with data fields not quoted
// - The first line of the input file contains accurate column names
// Note:
// - All fields will be imported as string values or NULL, any requirement for other data types will have to wait
// ----------------------------------------------------------------------
// ~~~~~~~~~~~~~~~~~~~~~~~~~ GET INPUT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //
// identify the input file
$file1 = "test_packages.csv";
// read the file into an array of lines
$lines = file($file1);
// ~~~~~~~~~~~~~~~~~~~~~~~~ /get input ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ //
include("includes/connect-string.inc.php");
// ~~~~~~~~~~~~~~~~~~~~~~~~~ CREATE ATTRIBUTE LIST ~~~~~~~~~~~~~~~~~~~~~~~~~ //
// the first line should be the attribute names, so we want to read it and
// create a list of attributes for the first part of the INSERT statements.
$first_line = trim($lines[0]); // read the first line in the array, trimming whitespace as we go
array_shift($lines); // remove first element from array - we don't want it read again.
$values = explode(",",$first_line); // split the line into an array
// now loop through the array, adding the elements to the values() section of the insert statement
foreach ($values as &$value) {
$attribute_list .= "'" . mysql_escape_string($value) . "',";
}
// now lop off the trailing string...
$attribute_list = substr($attribute_list,0,-1);
echo "~~ $attribute_list ~~<br />";
// ~~~~~~~~~~~~~~~~~~~~~~~~~ /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
// <a href="http://bytes.com/groups/php/1146-insert-null-not-blank-into-mysql-php-script">http://bytes.com/groups/php/1146-insert-null-not-blank-into-mysql-php-script</a>
// 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<br />";
} else {
// otherwise, display the insert statement onscreen for debug purposes
echo $insert_statement;
echo "<br>";
}
}
// ~~~~~~~~~~~~~~~~~~~~~~~~~ /process data lines ~~~~~~~~~~~~~~~~~~~~~~~~~~~ //
?>
