a6april Posted November 7, 2012 Share Posted November 7, 2012 Hi All! Have a couple of questions I hope you can help me with. I am creating an upload by browse button. Select the csv file and upload. Then run Queries on the data later. A couple of problems is that this only works when I re edit the csv file renaming the headers or columns. Some of the column headers contain spaces, #,$,/, etc and of course MySQL doesn't like these characters. Another issue I have is I can not figure out, how to populate the table using the first row of the csv file as the column header. Right now I was able to try and test it renaming the header file and adding the MySQL values in the php and worked ok, however ultimately I want the user to be able to browse for the csv file upload it and then be able to run the queries, but my script is set up for "here is the csv file, these are the headers, they already match up so lets go ahead and populate the rows" Ultimately it should be "here is the csv file, lets take the first row and use that to create our column headers, renaming the column headers for user friendly while removing duplicates, slashes etc etc and then lets go ahead and populate the rows" Here is my code, I am assuming I will have to start from scratch? <?php include '_inc/include.php'; if ($_FILES[csv][size] > 0) { //get the csv file $file = $_FILES[csv][tmp_name]; $handle = fopen($file,"r"); //loop through the csv file and insert into database do { if ($data[0]) { $mysql_query = mysql_query("INSERT INTO carbon_PA (closeddate, soldprice, streetnumber, streetname, streetsuffix, city, state, zip, gla, built, acres, taxid, photo, dom) VALUES ( '".addslashes($data[0])."', '".addslashes($data[1])."', '".addslashes($data[2])."', '".addslashes($data[3])."', '".addslashes($data[4])."', '".addslashes($data[5])."', '".addslashes($data[6])."', '".addslashes($data[7])."', '".addslashes($data[8])."', '".addslashes($data[9])."', '".addslashes($data[10])."', '".addslashes($data[11])."', '".addslashes($data[12])."', '".addslashes($data[13])."' ) "); } } while ($data = fgetcsv($handle,10000,",","'")); // //redirect } ?> <!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>Import a CSV File with PHP & MySQL</title> </head> <body> <?php if (!empty($_GET[success])) { echo "<b>Your file has been imported by import 55.php</b><br><br>"; } //generic success notice ?> <form action="" method="post" enctype="multipart/form-data" name="form1" id="form1"> Choose your file: <br /> <input name="csv" type="file" id="csv" /> <input type="submit" name="Submit" value="Submit" /> </form> <P> Display Data Contents <a href ='display55.php'>here</a></P> </body> </html> I would really appreciate any advice or help or direction on this. Thank You! Quote Link to comment https://forums.phpfreaks.com/topic/270430-upload-csv-file-to-mysql/ Share on other sites More sharing options...
requinix Posted November 7, 2012 Share Posted November 7, 2012 No, you won't have to start from scratch. 1. Make an array of table fields and CSV header names, like $mapping = array( "closeddate" => "Closed Date", 2. Read the first line from the file. This is the header row 3. Build an array that will map the table field to the value in the row. If the CSV had "Closed Date,Street Number,Sold Price" as headers then the array would end up looking like $fields = array( "closeddate", "streetnumber", "soldprice" ) 3b. Optionally validate the $fields. I assume there are some things that are required? How do you want to deal with unknown headers? Consider that someone might incorrectly use "Close Date". 4. Read data rows from the file. Your INSERT then uses $fields for the list of fields to insert into and the $data as the actual data values. "INSERT INTO carbon_PA (" . implode(", ", $fields) . ") VALUES (" . /* data */ . ")" Quote Link to comment https://forums.phpfreaks.com/topic/270430-upload-csv-file-to-mysql/#findComment-1390945 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.