I'm having a bit of a problem trying to import data from a CSV and have a couple of questions on it that I haven't managed to solve myself yet.
First off here's my code to help put things in perspective (tidied it up a bit, removing CSS and DB connection):
<body>
<div id="container">
<div id="form">
<?php
$deleterecords = "TRUNCATE TABLE tablename"; //empty the table of its current records
mysql_query($deleterecords);
//Upload File
if (isset($_POST['submit'])) {
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded
successfully." . "</h1>";
echo "<h2>Displaying contents:</h2>";
readfile($_FILES['filename']['tmp_name']);
}
//Import uploaded file to Database
$handle = fopen($_FILES['filename']['tmp_name'], "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$import="INSERT into importing(text,number)values('$data[0]','$data[1]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
print "Import done";
//view upload form
} else {
print "Upload new csv by browsing to file and clicking on Upload<br />\n";
print "<form enctype='multipart/form-data' action='upload.php' method='post'>";
print "File name to import:<br />\n";
print "<input size='50' type='file' name='filename'><br />\n";
print "<input type='submit' name='submit' value='Upload'></form>";
}
?>
</div>
</div>
</body>
It's basically an adaptation of an example I have found after many many attempts at various methods.
My CSV has two columns of data, the first one being text and the second is integers The table in the database also has two columns, the first called "text" and the second "number"
So the questions I have are:
I think that covers everything, thanks in advance for any help!
EDIT:
Just done a test of 10,000 record uploading and got the error:
"Fatal error: Maximum execution time of 30 seconds exceeded"
any thoughts?
I answered a virtually identical question just the other day: Save CSV files into mysql database
MySQL has a feature LOAD DATA INFILE, which allows it to import a CSV file directly in a single SQL query, without needing it to be processed in a loop via your PHP program at all.
Simple example:
<?php
$query = <<<eof
LOAD DATA INFILE '$fileName'
INTO TABLE tableName
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field1,field2,field3,etc)
eof;
$db->query($query);
?>
It's as simple as that.
No loops, no fuss. And much much quicker than parsing it in PHP.
MySQL manual page here: http://dev.mysql.com/doc/refman/5.1/en/load-data.html
Hope that helps
$i=0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if($i>0){
$import="INSERT into importing(text,number)values('".$data[0]."','".$data[1]."')";
mysql_query($import) or die(mysql_error());
}
$i=1;
}
Database Connection:
try {
$conn = mysqli_connect($servername, $username, $password, $db);
//echo "Connected successfully";
} catch (exception $e) {
echo "Connection failed: " . $e->getMessage();
}
Code to read CSV file and upload to table in database.
$file = fopen($filename, "r");
while (($getData = fgetcsv($file, 10000, ",")) !== FALSE) {
$sql = "INSERT into db_table
values ('','" . $getData[1] . "','" . $getData[2] . "','" . $getData[3] . "','" . $getData[4] . "','" . $getData[5] . "','" . $getData[6] . "')";
$result = mysqli_query($conn, $sql);
if (!isset($result)) {
echo "<script type=\"text/javascript\">
alert(\"Invalid File:Please Upload CSV File.
window.location = \"home.do\"
</script>";
} else {
echo "<script type=\"text/javascript\">
alert(\"CSV File has been successfully Imported.\");
window.location = \"home.do\"
</script>";
}
}
fclose($file);
set_time_limit(10000);
$con = mysql_connect('127.0.0.1','root','password');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("db", $con);
$fp = fopen("file.csv", "r");
while( !feof($fp) ) {
if( !$line = fgetcsv($fp, 1000, ';', '"')) {
continue;
}
$importSQL = "INSERT INTO table_name VALUES('".$line[0]."','".$line[1]."','".$line[2]."')";
mysql_query($importSQL) or die(mysql_error());
}
fclose($fp);
mysql_close($con);
letsay $infile = a.csv //file needs to be imported.
class blah
{
static public function readJobsFromFile($file)
{
if (($handle = fopen($file, "r")) === FALSE)
{
echo "readJobsFromFile: Failed to open file [$file]\n";
die;
}
$header=true;
$index=0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
// ignore header
if ($header == true)
{
$header = false;
continue;
}
if ($data[0] == '' && $data[1] == '' ) //u have oly 2 fields
{
echo "readJobsFromFile: No more input entries\n";
break;
}
$a = trim($data[0]);
$b = trim($data[1]);
if (check_if_exists("SELECT count(*) FROM Db_table WHERE a='$a' AND b='$b'") === true)
{
$index++;
continue;
}
$sql = "INSERT INTO DB_table SET a='$a' , b='$b' ";
@mysql_query($sql) or die("readJobsFromFile: " . mysql_error());
$index++;
}
fclose($handle);
return $index; //no. of fields in database.
}
function
check_if_exists($sql)
{
$result = mysql_query($sql) or die("$sql --" . mysql_error());
if (!$result) {
$message = 'check_if_exists::Invalid query: ' . mysql_error() . "\n";
$message .= 'Query: ' . $sql;
die($message);
}
$row = mysql_fetch_assoc ($result);
$count = $row['count(*)'];
if ($count > 0)
return true;
return false;
}
$infile=a.csv;
blah::readJobsFromFile($infile);
}
hope this helps.
i think the main things to remember about parsing csv is that it follows some simple rules:
a)it's a text file so easily opened b) each row is determined by a line end \n so split the string into lines first c) each row/line has columns determined by a comma so split each line by that to get an array of columns
have a read of this post to see what i am talking about
it's actually very easy to do once you have the hang of it and becomes very useful.
Source: Stackoverflow.com