PHP CSV Upload to Database
Here is the simplest way I know of to take a CSV (comma separated value) file and upload it to your web site somewhere, and use PHP to slam all the records into a database table. This should only be used "as is" in a controlled environment, as there is no input validation whatsoever, but it is enough to get the point across if you have never done this before. I added the mysql_real_escape_string() function, as someone asked how values containing single and double quotes could be handled with my original CSV upload to database example.
ini_set('auto_detect_line_endings',1);
$handle = fopen('upload.csv', 'r');
while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
$val1 = mysql_real_escape_string($data[0]);
$val2 = mysql_real_escape_string($data[1]);
$val3 = mysql_real_escape_string($data[2]);
mysql_query("INSERT INTO `table` (`col1`,`col2`,`col3`) VALUES ('{$val1}','{$val2}','{$val3}')");
$result = (mysql_insert_id()> 0) ? 'Pass' : 'Fail' ;
$output[$result]++;
}
print_r($result);
You’re currently reading “ PHP CSV Upload to Database ,” an entry on BRADINO
- Published:
- 5.29.07 / 5pm
- Category:
- PHP
sir , i want that , if there is any error in any of the insert statements , process should not stop , should continue with next record and at end it should tell me that how many success statements were there a nd how many were not ……
i want to know what is the solution for ‘ and ” conversion
Hi akash – I added the mysql_real_escape_string() calls for you to properly escape values, to answer your question.
Hi Shalini bhalla – I added a couple lines of code to differentiate the successful mysql inserts from the failed, and then output how many of each occurred, as per your request.
Thank you for the information. You have this on your website:
“This site is simply my small gift back, with the hope that someday I will get someone unstuck. Please email me if you want me to blog about something in particular that has you stuck…”
Well for me this blogpost was usefull. It is not something that I could not do myself, but like you said someone already did it. So why reinvent the wheel :)
Again thanks for sharing !!
Thank you Sir! I really appreciate you taking the time to post this, it’s helped me greatly!
I have not tried this yet, but I will use it to help me in the right direction (I am teaching myself PHP you see).
So thanks! :)
Why is the code so bad?
He print_r’s the wrong array.
Assigning the data to a temporary variable is bad practice, see Test Driven Design by Kent Beck, i.e.
VALUES (’{$val1}’, should be
VALUES (’”.mysql_real_escape_string($data[0]).”‘,
It reduces the lines of codes and potential for errors.
Putting the SQL into a string is another good practice, e.g.
]
$sql=”INSERT INTO `table`…
VALUES (’…
It is better because you can break it over lines for readability rather than putting it straight into a function.
The code does work though and I found it helpful.
Thx
Worked first time with small tweaks for use in my MVC, big help. Thanks!!
Perfect! This is exactly what I needed! Thank you.