Greetings all,

Well - it has been at least 9 months since I have added a new table to our database and it appears that something has changed with the Import function as it is not as user friendly as it was in the past.

All the information in our tables is created in Excel and then saved under the CSV format for import using phpMyAdmin (current version is 2.8.0.2).

Before import, I create the table I want in our MySQL database.

For this table I have surname (text),given(text),relation(text),info(text),abode(text),remarks(text),1920Mich_ID(int, unsigned zero fill, auto increment, primary).

In Excel, I have the same layout but right now, out of 3000 records, I maybe have two items in the "remarks" column and nothing in the 1920Mich_ID column as I want this to autoincrement when imported. Once all data is entered, I save as a CSV file.

In the past - import was never a problem with phpMyAdmin but it appears that the last version is not as "smart" as it will error out with "Invalid field count in CSV input on line XXX."

A sample of the CSV file is as follows:
Aarzulitis,William,,"Kent County, Grand Rapids MI, 25, Emmig 1914, Born Lithuania, NA 1917",#1,
Abiszo,Antoni,Head,"Wayne County, Detroit MI, 61, Emmig 1890, Born Lithuania, A",#2,
Abiszo,Anna,Wife,"61, Born Lithuanian, A",#2,
Abiszo,Alexander,Son,"22, Born Lithuanian, A",#2,

The import is choking on the first line using:
Fields terminated by ,
Fields enclosed by "
Fields escaped by \
Lines terminated by auto

Column names (left blank)

So, thinking that maybe the import was not using the table structure, I added the
column names: surname,given,relation,info,abode,remarks,1920Mich_ID

Still chokes on Line 1. So, I went in by hand and added an extra "," at the end of the line for the first 4 lines:

Aarzulitis,William,,"Kent County, Grand Rapids MI, 25, Emmig 1914, Born Lithuania, NA 1917",#1,,
Abiszo,Antoni,Head,"Wayne County, Detroit MI, 61, Emmig 1890, Born Lithuania, A",#2,,
Abiszo,Anna,Wife,"61, Born Lithuanian, A",#2,,
Abiszo,Alexander,Son,"22, Born Lithuanian, A",#2,,
Abiszol,Roman,,"Wayne County, Detroit MI, 34, Emmig 1911, Born Lithuania, A",#3,

and get the return error "Invalid field count in CSV input on line 5."

In the past, phpMyAdmin was smart enough to figure out this out on its own but right now it looks like I am going to have to enter this all in by hand.

Since I was a little bored, I decided to try a few things to speed up the import.

  • In the last column of the Excel file, I start with 0 and increment by +1 for each additional line. I copy the column and paste the values in a new column and then delete the previous column. Upon import, it runs in to a duplicate key problem.
  • So I try all "1's" in the last column - duplicate key problem.
  • So I try just adding a space as a placeholder for the last column - duplicate key problem

Grr...so I delete the last column and recreate it as a text only column (of course I now no longer have a primary key to index against. I cannot use any of the other columns as there many families with the same surname, or first name, etc. and the ID was intended to be unique).

So now I import..things are going well until line 988 which returns the error:
"Invalid field count in CSV input on line 988."

(987) Kirtiklis,Anastasa,Wife,"30, Born Lithuania, NA",#368,,985
(988) Kissel,Anthony,,"Wayne County, Detroit, MI, 28, Born Lithuania, PA",#369,"See: Moroz, William",986
(989) Kissel,Martha,,"Wayne County, Detroit, MI, 28, Born Lithuania, A",#370,"See: Moroz, William",987

Interesting - there is clearly 7 columns designated by the comma...
So, I delete line 988 from Excel, resave as a CSV file, empty and reimport.

Now I get to line 1947: "Invalid format of CSV input on line 1947."

A little snippet...
(1946) Steinkotz,Dessie,Wife,"24, Born Galicia, NR",#742,,1946
(1947) Steinkus,Stanley,,"Wayne County, Detroit MI, 19, Emmig 1912, Born Lithuania, A",#743,,1947
(1948) Steklkis,George,,"Kent County, Grand Rapids MI, 37, Emmig 1919, Born Lithuania, A",#744,,1948

I delete this line...etc...etc.. and then add back in the ones I had to delete by hand manually...

SURELY - there is an easier way to do this or it is quite possible that I am missing something entirely.

All I know - what worked before, does not work now.

Ideas?

Thanks!
Richard

    Maybe you should report this in the Database Forum.

      The simplest work around would be to import into a work tabel without the autoinc so that you can get your ID column into the db. You can then copy into the main table which has the autoinc column.

      It is always a good idea to import data into a work table so that you can validate it etc before adding to your existing live data.

      As to why phpmyadmin is gicing you so much trouble: your guess is as good as mine.

        Bob - thanks for the suggestion - I have cross posted there.

        Roger - the work around I am using right now is to drop the last column using the autoinc. Import the rest of the data into the table and then add the last column with the autoinc.

        This seems to be the easiest way at this time to get around the problem. I never had this problem in the past and have yet to figure out WHY the import is handling the import differently. Means a little more work in the beginning to get the information in - will keep digging.

        Thanks!

          Write a Reply...