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> Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/ 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. Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363718 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); Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363721 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? Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363722 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 Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363726 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. Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363727 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? Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363730 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 Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363733 Share on other sites More sharing options...
Jessica Posted July 23, 2012 Share Posted July 23, 2012 Good job Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363734 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. Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363737 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? Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363740 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. Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363741 Share on other sites More sharing options...
Jessica Posted July 23, 2012 Share Posted July 23, 2012 Just change that line of code. Link to comment https://forums.phpfreaks.com/topic/266122-skip-the-first-line-when-processing-csv-file/#findComment-1363770 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.