Hello, Im using a HTML form upload to send a CSV to my web server. I am looking for some help with parsing that CSV file to send it to my DB. The CSV has about 8 columns and has about 6,000 rows inside it. For an example can someone please lead me in the right direction with an example using say, 3 columns ad 10 rows of data to loop into a DB? the structure for example is: Question First Name Last Name Why is the sky blue? Timothy Allard Next Row Next Row Next Row ETC.. I'm also looking to read from a specific row # so when someone uploads the CSV a second time, it starts parsing from the row it left off so it reduces any chance of redundancy.. any help would GREATLY be appreciated. Thank you! -Tim
OK... i made some progress.... This is working...but it is placing the whole CSV row into the curiosity column...and not separating it out.. I think it is a problem with the syntax of the CSV, because i tried with no delimiters and it worked fine.. any ideas on how to get this CSV syntax to be placed into the correct columns? note the first colimn has no quotes.. example CSV row: Why the western part of the state has full staffed toll booths the collect no tolls. People from NY come in get a ticket, get off and owe no money. Why,"Susan","","email@gmail.com","02030","","Transportation","","","","","","","05/24/2009 11:53am" <?php #configure CSV delimiter and enclosure $delimiter = ","; //<!-- enter the character that is used for column separation. Use "\t" for tabs. $enclosure = '"'; //<!-- enter the character that is used with "large strings" # Open the file $f = fopen('testreal2.csv','r'); # Read the file, line by line while($row = fgetcsv($f, 4024, $delimiter, $enclosure)) { # Convert the row into the correct columns list($curiosity, $firstname, $lastname, $email, $zipcode, $town, $category, $uploadphoto, $uploadvideo, $documentupload, $matowns, $othertowns, $nhtowns, $date) = $row; # Create the insert statement; /* $query = sprintf("INSERT INTO curiosities SET Curiosity='$row[0]', FirstName='$row[1]', LastName='$row[2]'"); # Run the insert statement mysql_query($query) or die(mysql_error()); # Get the member_id (auto increment column) */ } echo "\nAll done"; ?> This inserts into the DB all into the curiosity column,... ----- one more thing...it hangs on ' in the text so it needs to be escaped but im not sure exactly how,.. thanks!!! ugh this is killin me!!
You need to rewrite your output so the first field has quotes around it. Your parser will think that "People from NY come in get a ticket, get off and owe no money" is two separate fields because of the comma in the middle. Also, the syntax is wrong on your insert query. Should be: INSERT INTO curiosities (Curiosity, FirstName, LastName) VALUES ('$row[0]', '$row[1]', '$row[2]')
Are PHP and MySQL on the same server ? It's worth looking into the LOAD DATA INFILE syntax before getting PHP involved with this.
You need to enclose the values in quotes if they have spaces or commas as part of the value. I hope that makes sense. Regarding reducing the chances of duplicates, you have to check to see if the row exists before you do an insert. If it exists, you may want to update the existing row or discard the duplicate. That depends on your requirements of course.
Thank you all for this help, it is a big help to me. Do you know how i can enclose that first column in quotes? I can not make edits tot he CSV file, i don't know why it wasn't enclosed in quotes in the first place..
For consistency, all columns should be enclosed in quotes and separated with a delimiter. If you have no control over the CSV file, there are some approaches you can use to overcome the problem. I can think of a couple, but let me think it over for a few.
great thank you. I totally agree with the consistency part of things, unfortunately i have no control over the CSV its an export from another tool.
for the purpose of example, i made a new CSV with 5 lines in it. I manually added the quotes...but this is where the problem is.. It is successfully putting it in the DB but all under the "Curiosity" column. e.x. Curiosity Column: "Do all major league Empires work as a Group with the same schdule all year? Or do they have each their own Schdule." ,"Al","","email@comcast.net","03044","Fremont, NH","Sports","","","","","","","05/24/2009 6:48am" and it should as shown below (be bold = column name) Curiosity: "Do all major league Empires work as a Group with the same schdule all year ? Or do they have each their own Schdule." First Name: "Al" Last Name: "" Email: "email@comcast.net"
I ran the above snippet through the debugger with all columns enclosed in quotes. It appears to be working as expected. Can you post the actual code you are using to test? I suspect the INSERT statement syntax is incorrect, but cannot confirm without seeing the code.
Thats all of the code I have for this test. outside of that there is a csv with: "Do all major league Empires work as a Group with the same schdule all year? Or do they have each their own Schdule." ,"Al","","email@comcast.net","03044","Fremont, NH","Sports","","","","","","","05/24/2009 6:48am" in 1 column, on 1 row i agree with the insert part of it,..any ideas whats causing it not to place it in its respective columns? when you say "It appears to be working as expected." what do you mean? I will help with some paypal if you would like, I really appreciate your help.
this is very simple. you can try this code. $handle = fopen ('./file.csv', 'r'); while (($data = fgetcsv($handle, 1000, ',', '"')) !== FALSE) { $query = "INSERT INTO services VALUES ('". implode("','", $data) ."')"; $query = @mysql_query($query); } if you don't want to implode function then just pass array with index number i.e. $handle = fopen ('./file.csv', 'r'); while (($data = fgetcsv($handle, 1000, ',', '"')) !== FALSE) { $query = "INSERT INTO services VALUES ($data[0],$data[1],.....)"; $query = @mysql_query($query); }
thank you KRISHNA KUMAR unfortunately this is still not working,.. its storing the whole row as $data[0] and not separating it....any further ideas?
The problem lies here i believe.. The CSV has the comma delimited data all stored within the first column... so in column one this is the data "Do all major league Empires work as a Group with the same schdule all year? Or do they have each their own Schdule." ,"Al","","email@comcast.net","03044","Fremont, NH","Sports","","","","","","","05/24/2009 6:48am" when i try with a csv with data in each column it works fine..
im thinking out loud here,..but since i am getting a row as 1 long string,...shouldnt i just need to explode that string and send it into the db that way? if so how can i explode that line to get separate values? "Do all major league Empires work as a Group with the same schdule all year? Or do they have each their own Schdule." ,"Al","","email@comcast.net","03044","Fremont, NH","Sports","","","","","","","05/24/2009 6:48am"
OK - I have made super major progress. Sorry for the multiple posts, but i hope this can be a walk-through sort of speak for others as a learning process. I added code to explode that 1 line to break down the elements. It is now working with the below code. BUT it this was by me modifying the origional csv by manually addeing my quotes around that first column. How can I have it ad quotes around my first column of data? below code is working for me <?php include 'config/config-test.php'; # Configure CSV delimiter and enclosure $delimiter = ","; //<!-- enter the character that is used for column separation. Use "\t" for tabs. $enclosure = '"'; //<!-- enter the character that is used with "large strings" # Open the file $f = fopen('test.csv','r'); # Read the file, line by line while($row = fgetcsv($f, 4024, $delimiter, $enclosure)) { # Convert the row into the correct columns list($curiosity, $firstname, $lastname, $email) = $row; # Create the insert statement; $new = $row; $new = explode('"',$new[0]); echo "Curiosity: " . $new[1]; echo "<br>"; echo "First Name: " . $new[3]; echo "<br>"; echo "Last Name: " . $new[5]; echo "<br>"; echo "Email: " . $new[7]; echo "<br>"; echo "Zipcode: " . $new[9]; echo "<br>"; echo "Town: " . $new[11]; echo "<br>"; echo "Category: " . $new[13]; echo "<br>"; echo "Upload Photo: " . $new[15]; echo "<br>"; echo "Upload Video: " . $new[17]; echo "<br>"; echo "Upload Document: " . $new[19]; echo "<br>"; echo "MA Towns: " . $new[21]; echo "<br>"; echo "Other Towns: " . $new[23]; echo "<br>"; echo "NH Towns: " . $new[25]; echo "<br>"; echo "Date: " . $new[27]; echo "<br>"; echo "<br><br>"; $query = sprintf("INSERT INTO curiosities (Curiosity) VALUES ('$row[0]')"); # Run the insert statement mysql_query($query) or die(mysql_error()); # Get the member_id (auto increment column) } ?> and giving me the output of: Curiosity: The curiosity content here First Name: billy Last Name: fusick Email: Zipcode: 02152-1837 Town: Winthrop Category: WBZ Upload Photo: Upload Video: Upload Document: MA Towns: Other Towns: NH Towns: Date: 05/24/2009 8:41am SWEET. ALMOST THERE. OK so again, to get this to work,...i had to modify my csv to include quotes around the first column.. THEN i added a new explode line to get it to parse the row because it was stored as 1 column. How can i have it add quotes around that first column of data? THANK YOU ALL
This uses fgets instead of fgetcsv to get around the problem of not having quotes around your first field. Kind of an ugly way to parse csv but it seems to work. It separates fields based on (comma, quote) instead of just comma. $handle = fopen("yourfile.csv", "r"); while (!feof($handle)) { // grab one line of data $data = fgets($handle); // trim the newline from end of line $data = trim($data); // put fields into array separated by ," (comma, quote), this still leaves a trailing quote that we have to get rid of $fields = explode(',"', $data); // trim the trailing quote and echo the fields for ($i=0; $i<sizeof($fields); $i++) { $fields[$i] = rtrim($fields[$i], '"'); echo 'fields['. $i . '] -- ' . $fields[$i] . '<br />'; } } fclose($handle); PHP:
oh this is good thank you. but what if there are quotes and commas in that first column already? its basicly a free form field where the user would type a questions, and i have instances of quotes and commas in there. ex. bold is column 1: Hi, my name is "Tim" how are you?, "tim", "allard", "question" ogh wait i get it,.. comma,quote. interesting...let me see wht happens..gonna try now. thanks!
Getting there... This is my output: fields[0] -- """Why the western part of the state has full staffed toll booths the collect no tolls. People from NY come in get a ticket, get off and owe no money. Why fields[1] -- "Susan fields[2] -- fields[3] -- "email@gmail.com fields[4] -- "02030 fields[5] -- fields[6] -- "Transportation fields[7] -- fields[8] -- fields[9] -- fields[10] -- fields[11] -- fields[12] -- fields[13] -- "05/24/2009 11:53am so the first column has 3 quotes and all other fields has 1 at the beginning, how do i remove those? thanks..!
i just did $fields[$i] = ltrim($fields[$i], '"'); and it removed the left quotes! wooo! ok.. now.. i need to reset up my insert call..