Import an excel (.csv) into MySQL using PHP code and an HTML form
21,971
Solution 1
I haven't fully tested this, but I don't see any reason why it wouldn't work.
<?php
if ( isset( $_FILES['userfile'] ) )
{
$csv_file = $_FILES['userfile']['tmp_name'];
if ( ! is_file( $csv_file ) )
exit('File not found.');
$sql = '';
if (($handle = fopen( $csv_file, "r")) !== FALSE)
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$sql .= "INSERT INTO `table` SET
`column0` = '$data[0]',
`column1` = '$data[1]',
`column2` = '$data[2]';
";
}
fclose($handle);
}
// Insert into database
//exit( $sql );
exit( "Complete!" );
}
?>
<!DOCTYPE html>
<html>
<head>
<title>CSV to MySQL Via PHP</title>
</head>
<body>
<form enctype="multipart/form-data" method="POST">
<input name="userfile" type="file">
<input type="submit" value="Upload">
</form>
</body>
</html>
Of course you would need to validate the data first.
Solution 2
Everything in your code is fine, only mistake is you are not using mysql_query for inserting the data into table. Mysql query not running in your script. corrected code follows...
<?php
if ( isset( $_FILES['userfile'] ) )
{
$csv_file = $_FILES['userfile']['tmp_name'];
if ( ! is_file( $csv_file ) )
exit('File not found.');
$sql = '';
if (($handle = fopen( $csv_file, "r")) !== FALSE)
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$sql = mysql_query("INSERT INTO `table` SET
`column0` = '$data[0]',
`column1` = '$data[1]',
`column2` = '$data[2]';
");
}
fclose($handle);
}
// Insert into database
//exit( $sql );
exit( "Complete!" );
}
?>
<!DOCTYPE html>
<html>
<head>
<title>CSV to MySQL Via PHP</title>
</head>
<body>
<form enctype="multipart/form-data" method="POST">
<input name="userfile" type="file">
<input type="submit" value="Upload">
</form>
</body>
</html>
Solution 3
We used this awhile ago, and it works just fine. Just watch your file and directory permissions. csv_upload_mysql_conf.inc is just the DB link. This will parse multiple files at once, and put them in a table called import. Update accordingly.
<?php
/* The conf file */
include_once "csv_upload_mysql_conf.inc";
$php_self = $_SERVER['PHP_SELF'];
$file_open = 0;
$file_exts = array
( 'csv');
#Our Form.....
$form = <<< EOFFORM
<div align='center' style='border: 1px solid #CCC; background-color: #FAFAFA;padding: 10px; color: #006699; width: 620px; font-family: palatino, verdana, arial, sans-serif;' >
<table align=center style='border: 1px solid #CCC; background-color: #FFF;padding: 20px; color: #006699;' cellspacing=1><tbody>
<tr><td>
<form enctype='multipart/form-data' action='$php_self' method='post'><input type='hidden' name='MAX_FILE_SIZE' value='2000000' /><input type='hidden' name='selected' value='yes' /> Selected file: <input name='userfile[]' type='file' id='userfile[]' multiple='' onChange='makeFileList();' /><br /><br /><input type='submit' value='Upload CSV' />
</td></tr></tbody></table></div>
<p>
<strong>Files You Selected:</strong>
</p>
<ul id="fileList"><li>No Files Selected</li></ul>
<script type='text/javascript'>
function makeFileList() {
var input = document.getElementById('userfile[]');
var ul = document.getElementById('fileList');
while (ul.hasChildNodes()) {
ul.removeChild(ul.firstChild);
}
for (var i = 0; i < input.files.length; i++) {
var li = document.createElement('li');
li.innerHTML = input.files[i].name;
ul.appendChild(li);
}
if(!ul.hasChildNodes()) {
var li = document.createElement('li');
li.innerHTML = 'No Files Selected';
ul.appendChild(li);
}
}
</script>
EOFFORM;
#End Form;
if(!isset($_POST['selected'])){
echo "$form";
}
elseif($_POST['selected'] == "yes"){
$uploaddir = 'uploads/';
if(count($_FILES['userfile']['name'])) {
foreach ($_FILES['userfile']['name'] as $key => $error) {
if ($error == UPLOAD_ERR_OK) {
$tmp_name = $_FILES['userfile']['tmp_name'][$key];
$name = $_FILES['userfile']['name'][$key];
$f_type = trim(strtolower(end(explode('.', $name))));
if (!in_array($f_type, $file_exts)) die("Sorry, $f_type files not allowed");
}
$uploadfile = $uploaddir . $name;
if (! file_exists($uploadfile)) {
if (move_uploaded_file($tmp_name, $uploadfile)) {
print "File is valid, and was successfully uploaded. ";
$flag = 1;
chmod($uploadfile, 0777);
} else {
print "File Upload Failed. ";
$flag = 0;
}
$flag = 1;
if ($flag == 1) {
echo "\n parsing Data...";
flush();
if (file_exists($uploadfile)) {
$fp = fopen($uploadfile, 'r') or die (" Can't open the file");
$fileopen = 1;
$length = calculate_length($uploadfile);
}
$replace = "REPLACE";
$field_terminater = ",";
$enclose_option = 1;
$enclosed = '"';
$escaped = '\\\\';
$line_terminator = 1;
$local_option = 1;
$sql_query = 'LOAD DATA';
if ($local_option == "1") {
$sql_query .= ' LOCAL';
}
$sql_query .= ' INFILE \'' . $uploadfile . '\'';
if (!empty($replace)) {
$sql_query .= ' ' . $replace;
}
$sql_query .= ' INTO TABLE ' . "`import`";
if (isset($field_terminater)) {
$sql_query .= ' FIELDS TERMINATED BY \'' . $field_terminater . '\'';
}
if (isset($enclose_option) && strlen($enclose_option) > 0) {
$sql_query .= ' OPTIONALLY';
}
if (strlen($enclosed) > 0) {
$sql_query .= ' ENCLOSED BY \'' . $enclosed . '\'';
}
if (strlen($escaped) > 0) {
$sql_query .= ' ESCAPED BY \'' . $escaped . '\'';
}
if (strlen($line_terminator) > 0){
$sql_query .= ' LINES TERMINATED BY \'' . '\r\n' . '\'';
}
$result = mysql_query ($sql_query);
echo mysql_error() ;
if(mysql_affected_rows() > 1) {
echo " <div align=center><b><font color=#66CC33>The csv data was added.</font></div> ";
}
else {
error_log(mysql_error());
echo " <div align=center><b><font color=#E96B10> Couldn't enter the data to db </font></div>";
}
if ($file_open ==1) {
fclose($fp) or die("Couldn't close the file");
}
}
}
}
echo "<meta http-equiv='refresh' content='0; url=index.php'>";
}
}
function calculate_length($fp) {
$length = 1000;
$array = file($fp);
for($i=0;$i<count($array);$i++)
{
if ($length < strlen($array[$i]))
{
$length = strlen($array[$i]);
}
}
unset($array);
return $length;
}
?>
Author by
DMor
Updated on January 25, 2020Comments
-
DMor over 4 years
I know there are other posts similar to this, but everyone recommends just doing it directly in PHPMyAdmin into MySQL (Which works perfectly, but I need to import through an HTML form to PHP to MySQL.
I would like to have an HTML form that collects a file. Then passes that file to a PHP script and I would like to know if it is possible to simply call a PHP function that converts a comma delimeted .csv file into MySQL and adds it to the database.
Or is the only way to parse the file line by line and add each record?
-
Casey Chu over 11 yearsPHPMyAdmin uses an HTML form and PHP. Read its source to see how they do it.
-
JDavis over 11 yearsHave a look at code.google.com/p/php-csv-parser it will convert .csv to an array. Then you can loop through the array and construct your INSERT queries.
-
-
Dean Grell over 11 years90% not my code, and not claiming it as such. Use it at your own risk...it's all over Google search results. I'm sure some improvements could be made. Also, please excuse our lazy file extension array. We sometimes allow txt files, so we leave the array code in with only one value.
-
JDavis about 11 yearsI didn't want to go into actually putting the data in the database because it wasn't necessary to answer his question. I simply wanted to show a working example that you could actually run and test to see what the query could look like. I'm pretty sure your example would fail if you tried to run it. Ideally you would use
PDOandbindParamrather then concatenating a series ofINSERT INTOstatements. -
Kasnady over 10 yearsgoing well just need to ammend a little for other database. can you advise me how to export? i will be very appreciated.
