import CSV file into MySQL database
I have records into .CSV file and I want to import them into MySQL database. Whenever I import the .CSV I get the message “Import has been successfully finished…” but only 79 out of 114 records will be inserted into the database. When I try to import the CSV file with 411 records, just 282 will be inserted. The CSV file which got 411 records includes two categories of records “Active and Sold” whereby 114 records are Active. Has someone gotten this type of problem? If so what should be done?
Make sure the CSV is formatted correctly. If you made the CSV sheet in Excel (or even modified it in Excel, for that matter), chances are it's screwed up.
Thanks Josh for the reply,
My problem is, it insert just 79 records out of 114 and 282 records out of 411 records. What will be the problem?
Import the CSV into Access as a CSV for MS Excel. It's a lot more forgiving and you can fix errors within it. Then export it as CSV (either standard or Excel) and import it into phpMyAdmin. Access should be able to fix most problems with it and will allow you to recover any data that's been corrupted or in the wrong format.
Well, what I've done personally in the past is run a regular expressions cleanup of the CSV sheet (surround strings with double quotes, strip hanging commas, etc). It really depends on the situation, really. There's no cookbook of solutions for CSV errors.
You should tell us how you are trying to import the file.
are you using phpmyadmin or heidisql or another GUI?
Are you using LOAD DATA INFILE directly in mysql?
if the former there are usually fields you need to specify the delimiter between rows, make sure you have chosen those correctly.
If the latter then show us your LOAD statement.