Invalid field count in CSV input

[cavey5]

I am trying to import a CSV file via phpMyAdmin and I get this error:

 

Invalid field count in CSV input on line 802.

 

The file is called workbook.csv and has 21 fields. The field names in the first row are identical

to the names of the database fields. In fact what I did was export a small database with 25 records, and

cleared all but the header row, and filled it in with new data, saved it as the same file and tried to

import it. It should work! The settings I am using on import are:

 

CSV

utf8 character set

partial import (allow interrupt) is checked

replace table data with file is checked

ignore duplicate rows is checked

fields terminated by is set to a comma (,)

fields enclosed by is set t double quotes

fields escaped is set to a backslash (\)

rows terminated by is set to auto

 

I have no idea what is causing the error. I have cleared the entire CSV file of all commas, backslashes and single or double quotes... could be empty fields? Do I need to remove the field names at the top of the list?

 

If I try to run it as CSV with LOAD DATA I get this error:

 

#1045 - Access denied for user 'cm_subsdata_1'@'%' (using password: YES)

 

Help?!?!

[teng84]

maybe u need to declare which db to use

[cavey5]

What do you mean?  ???

 

Since i am already logged into my database with PhpMyAdmin, and have selected a table to do an insert on, I think it is pretty straight forward, isn't it?

 

Anyone else?

 

Are there any 3rd party MySQL front ends that work well for inserts like this?

[teng84]

can we see your query

[fenway]

Attach a portion of the file in question... what's line 802? You can skip the header line, that's one of the LOAD DATA options.

[cavey5]

I can't actually post the file as it is proprietary client data, but I will mix it up and create some fake names and post that...

[cavey5]

I just re-ran the import of 4533 records and it gives me this notice:

 

"Import has been successfully finished, 4533 queries executed."

 

But when I browse my records in phpMyAdmin I get this:

 

"Showing rows 0 - 29 (1,823 total, Query took 0.0008 sec)"

 

and randomly a bunch of records are missing. It isn't like it stopped

at a certain number, they are sorted sequentially by user ID but

like it will skip 5 and then skip 12 of them and then have 5 in a row correct, then skip 3...  ??? ???

 

is there an easy to use import tool with an easy learning curve for this¿ phpMyAdmin sucks.

 

 

[fenway]

You selected ignore duplicates...

[cavey5]

can we see your query

 

I am not writing a query, I am using phpMyAdmin, which is doing its own query, and

I am not sure how to extract that code...

 

 

You selected ignore duplicates...

 

It doesn't matter if I select that or not, I get the same result. I tried yet another file of 200 records and it successfully inserted 277 queries, but only 102 records show up on browse. I can't have this level of inconsistency with client records!

[AndyB]

I tried yet another file of 200 records and it successfully inserted 277 queries, but only 102 records show up on browse. I can't have this level of inconsistency with client records!

 

There's something really weird about your data when you create a file with 200 records, phpAdmin claims to have inserted 77 more than you have, and then shows 98 less than you had (or 175 less than it inserted).  If any (or all) of your original data sets have the same peculiarity, it looks as though you'll need some pre-processing before you attempt to import to MySQL

[fenway]

I would tend to agree... it's being parsed incorrectly.