colleyboy Posted July 23, 2012 Share Posted July 23, 2012 Hi have found a great script to upload CSV files into a mysql database. Problem is that it uploads the first line of the file. I need it to skip this line as it only has headers for the columns. Is there any way to "skip this?" Upload.php: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Upload page</title> <style type="text/css"> body { background: #E3F4FC; font: normal 14px/30px Helvetica, Arial, sans-serif; color: #2b2b2b; } a { color:#898989; font-size:14px; font-weight:bold; text-decoration:none; } a:hover { color:#CC0033; } h1 { font: bold 14px Helvetica, Arial, sans-serif; color: #CC0033; } h2 { font: bold 14px Helvetica, Arial, sans-serif; color: #898989; } #container { background: #CCC; margin: 100px auto; width: 945px; } #form {padding: 20px 150px;} #form input {margin-bottom: 20px;} </style> </head> <body> <div id="container"> <div id="form"> <?php include "connection.php"; //Connect to Database $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 products(product_name,variety,description,price) values('$data[0]','$data[1]','$data[2]','$data[3]')"; 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> </html> Quote Link to comment Share on other sites More sharing options...
Jessica Posted July 23, 2012 Share Posted July 23, 2012 If you add an extra $data = fgetcsv($handle, 1000, ","); before the loop it'll skip the first. You can also use an if and keep track of the line numbers. Quote Link to comment Share on other sites More sharing options...
colleyboy Posted July 23, 2012 Author Share Posted July 23, 2012 Do you mean like this? :S $handle = fopen($_FILES['filename']['tmp_name'], "r"); while (($data = fgetcsv($handle, 1000, ","($data = fgetcsv($handle, 1000, ","))) !== FALSE) { $import="INSERT into products(product_name,variety,description,price) values('$data[0]','$data[1]','$data[2]','$data[3]')"; mysql_query($import) or die(mysql_error()); } fclose($handle); Quote Link to comment Share on other sites More sharing options...
Jessica Posted July 23, 2012 Share Posted July 23, 2012 Really? Does anything about that look right? Do you understand what your code is doing? Quote Link to comment Share on other sites More sharing options...
colleyboy Posted July 23, 2012 Author Share Posted July 23, 2012 Sorry I am a bit new to loops. I got this code elsewhere and have done little things to it but am stuck on this bit. Please can you help me out. Much appreciated Quote Link to comment Share on other sites More sharing options...
Jessica Posted July 23, 2012 Share Posted July 23, 2012 Go through the code and understand what it does. If you don't understand what each line does, you need to. Quote Link to comment Share on other sites More sharing options...
colleyboy Posted July 23, 2012 Author Share Posted July 23, 2012 This is what I understand from the code: //Import uploaded file to Database $handle (variable for handling request to open CSV) = fopen($_FILES['filename']['tmp_name'] (opens the file from the temp directory), "r (retreives)"); (This is the loop part I think where it looks up the csv contents and fetches the data for the first 1000 rows of data) while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { (this bit imports the data into the database as per the column names in the database and the column count in the csv file) $import="INSERT into products(product_name,variety,description,price) values('$data[0]','$data[1]','$data[2]','$data[3]')"; (if there is an error importing stop) mysql_query($import) or die(mysql_error()); } (closes the csv opening session) fclose($handle); (echo's it has done) print "Import done"; This is what I understand from the code. Please let me know if I am thinking along the right lines? Quote Link to comment Share on other sites More sharing options...
colleyboy Posted July 23, 2012 Author Share Posted July 23, 2012 I thought logically to what you said and added it and it worked //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"); $data = fgetcsv($handle, 1000, ","); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $import="INSERT into products(product_name,variety,description,price) values('$data[0]','$data[1]','$data[2]','$data[3]')"; mysql_query($import) or die(mysql_error()); } fclose($handle); print "Import done"; "Eureka!" - Thanks Quote Link to comment Share on other sites More sharing options...
Jessica Posted July 23, 2012 Share Posted July 23, 2012 Good job Quote Link to comment Share on other sites More sharing options...
colleyboy Posted July 23, 2012 Author Share Posted July 23, 2012 Many Thanks. I have another question though? How would I display the results better on the page. At the moment it processes and displays it like: File dutchhouseproducts.csv uploaded successfully. Displaying contents: product_name,variety,description,price Red Flower,Roses,This is a description for the red flower,3.99 Yellow Flower,Roses,This is a lovely yellow flower,14.99 Import done I would like it to display a row at a time... Example: File dutchhouseproducts.csv uploaded successfully. Displaying contents: Row 1: Red Flower,Roses,This is a description for the red flower,3.99 Row2: Yellow Flower,Roses,This is a lovely yellow flower,14.99 Import Complete. Quote Link to comment Share on other sites More sharing options...
Jessica Posted July 23, 2012 Share Posted July 23, 2012 You mean you want it to output it in real time as it loads it? Quote Link to comment Share on other sites More sharing options...
colleyboy Posted July 23, 2012 Author Share Posted July 23, 2012 That would be nice but if it is not real-time then just a way to display the output so all the columns of code are not together when it shows what it has put in the db. Quote Link to comment Share on other sites More sharing options...
Jessica Posted July 23, 2012 Share Posted July 23, 2012 Just change that line of code. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.