I've been having problems with a CSV file when importing it into MySQL.

 

All the rows import as normal except for the odd few.

The only difference with these rows is that they contain & in a few of the columns.

Could this be causing the problem?

Thanks I've just checked over it and found rows that contain & so that's obviously not the cause. But basically I've got 3460 rows in a CSV file but when I import it I'm only getting 3166 into the database and can't work out why?!

I can rule out duplicates because there's some rows that just aren't appearing at all.

No, that's the thing! I've done a search in Excel and can't finid anything...

I had suspected that if there was a ' or " anywhere, then everything after it wouldn't be imported. But I can't find either of those...

Originally the CSV file was rather large, so I thought it could of been because of that, so I split it down and removed everything else from the database and inserted it by itself but it's always the same rows that are missing.

I use a script to insert the the CSV, which is ran as a Cron Job, the script downloads the CSV as a zip file, then unzips it and imports it into the database.

I know there's no problem with the unzipping part, because I can take the CSV file after the script has unzipped it and all the rows are there. The problem seems to be once it's imported.

I'll try importing the CSV file direct and see how that turns out.

Edited June 5, 201412 yr by Lee_K

If importing as CSV check for commas or apostrophes.

Can we see a sample of the CSV, rather then looking at the CSV in excel look at it in notepad++ (or other code editor) as it will be easier to see errors

I don't think the comma's are the problem as every row contains multiple comma's inside of a certain column.

When I import the CSV manually I get a "#1062 - Duplicate entry" error. But if I tick the "Do not abort on INSERT error" box then it imports the 3166 rows. Leaving the other 294 rows out.

Is this a case of the other 294 rows being duplicate? Or just 1 of the rows causing problems for the remaining rows after it?

Edited June 5, 201412 yr by Lee_K

What you can do is make an identical table in a diffrent database but with no key constraints and import the data to that database and then run something like

 

SELECT DISTINCT keyCol1, keyCol2, keyCol3 FROM testTable

 

And see if the returned rows match 3166 or 3460

When I run that in my table as it is now I get:

This table does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.

Then this underneath:

CONSTRAINT_NAME
program_name

Which is the name of 1 of my columns.

Edited June 5, 201412 yr by Lee_K

I think I've fixed this by removing the Unique from Program_Name and making another column Unique instead.

I don't understand enough about MySQL yet to really understand what I've done, but I think it works!

I'll be back to let you know. thanks for the help guys!

[EDIT]: Yeah well those missing rows are now showing up fine, so all seems to be well again! Going to check out the rest of my CSV files to make sure their doing the same. But I think this is fixed!

Edited June 5, 201412 yr by Lee_K