Menu
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?
658
In the Format list, select CSV. Changing format-specific options. If the csv file is delimited by a character other than a comma or if there are other specifications to the csv files, we can change it in this portion. Click Go to start importing the csv file and the data will be successfully imported into MySQL.
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
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
