Importing .csv file via phpMyAdmin

[gwh]

Hi everyone,

 

I'm trying to import a .csv file via phpMyAdmin. I started out in excel and then saved as .csv. The following are the headings in the excel file which correspond to a table in phpMyAdmin:

 

shirtID, shirtTitle, shirtSKU, shirtDescLadies, shirtDescMen, shirtPrice, shirtColours, shirtSizes, shirtImage, shirtSwatch

 

The first field, shirtID is an auto-incrementing primary key. All fields are set to be Not Null with the exception of three fields, ie. shirtDescLadies, shirtDescMen and shirtSwatch which are set to NULL.

 

I've inserted five rows of data under each column heading and then saved the file as a .csv file. I then opened up the file in a text editor and deleted the first row which contain the headings.

 

Back in phpMyAdmin, I browsed for the .csv file and have the following settings:

 

Format of imported file: CSV

Replace table data with file: unchecked

Ignore duplicate rows: unchecked

Fields terminated by: ,

Fields enclosed by: "

Fields escaped by: \

Lines terminated by: auto

 

Then I typed in the column names in the "column names field". I typed all of them in except the first primary key column, ie. shirtID

 

When I click "Go", it only inserts the first row and gives me the following error:

 

Invalid field count in CSV input on line 2.

 

Out of the 5 sample rows that I inserted, all the fields were populated with data with the exception of the last column, shirtSwatch, which was left blank. Also one of the shirtDescMen fields was left blank, but since both these fields were set to NULL in the database table I didn't think this would matter.

 

Can anyone tell me what I'm doing wrong?

 

Appreciate any help.

 

[JonnoTheDev]

Your data may have a , in it so the import thinks it is the start of a new field

[JustLikeIcarus]

Can you provide you mysql table layout and a sample from the csv file?

[gwh]

Thanks for the replies,

 

The following is the SQL for the table:

 

CREATE TABLE `shirts` (

  `shirtID` int(10) unsigned NOT NULL auto_increment,

  `shirtTitle` varchar(100) NOT NULL,

  `shirtSKU` varchar(255) NOT NULL,

  `shirtDescLadies` varchar(255) default NULL,

  `shirtDescMen` varchar(255) default NULL,

  `shirtPrice` double NOT NULL,

  `shirtColours` varchar(255) NOT NULL,

  `shirtSizes` varchar(100) NOT NULL,

  `shirtImage` varchar(255) NOT NULL,

  `shirtSwatch` varchar(255) default NULL,

  PRIMARY KEY  (`shirtID`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

 

I've attached a screenshot of what it looks like in Excel and below is what it looks like when I open up the .csv file in a text editor, although as mentioned I remove the first row before I try to import it:

 

shirtID,shirtTitle,shirtSKU,shirtDescLadies,shirtDescMen,shirtPrice,shirtColours,shirtSizes,shirtImage,shirtSwatch

,Essentials _ Ladies and Men's Coordinates,1000,"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton.","3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton.",40,"Ink, Burgundy, Blue, White, Charcoal ",6-26,1000.jpg,

,Essentials _ Ladies and Men's Coordinates,2000,"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton.",NULL,40,"Ink, Burgundy, Blue, White, Charcoal ",6-26,2000.jpg,

,Essentials _ Ladies and Men's Coordinates,3000,"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton.","3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton.",40,"Teal, Blue, Lilac ",SM-4XL,3000.jpg,

,Essentials _ Ladies and Men's Coordinates,4000,"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton.","3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton.",40,"Sky, Wheat, Blue, Navy ",6-26,4000.jpg,

,Essentials _ Ladies and Men's Coordinates,5000,"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton.",NULL,40,White,6-26,5000.jpg,

 

[attachment deleted by admin]

[JonnoTheDev]

This is not csv format. Just look at it. There are a million commas. Where is the end of a record line?

[gwh]

That's how it came out when I saved the excel file as a .csv file. I don't know what the alternative is?

[JustLikeIcarus]

Looks like there are a couple problems.  First you dont need the first line in the csv with the headers. Second your not enclosing all your fields in quotes.

 

try formatting it something like

NULL,"Essentials _ Ladies and Men's Coordinates","1000","3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton.","3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton.","40","Ink, Burgundy, Blue, White, Charcoal ","6-26","1000.jpg", ""

[gwh]

Yes but this file will eventually be hundreds of rows long. I can't really format it all manually. Is there another way to get excel to export a proper csv file?

[JonnoTheDev]

OK, in Excel, format the columns correctly so they are of the correct type for the data that they contain. You can see that your description fields contain commas within the text so that is obviously going to cause an issue if you are using a comma as a field delimiter. Export the data from Excel or Access but use a pipe as a delimiter | for fields.

[gwh]

OK, in Excel, format the columns correctly so they are of the correct type for the data that they contain. You can see that your description fields contain commas within the text so that is obviously going to cause an issue if you are using a comma as a field delimiter. Export the data from Excel or Access but use a pipe as a delimiter | for fields.

 

Only problem is that when I save as a .csv file from excel, it doesn't give you the option to choose a delimiter. Or am I missing something?

[JonnoTheDev]

Only problem is that when I save as a .csv file from excel, it doesn't give you the option to choose a delimiter. Or am I missing something?

Import the xls file into Access and then export from there.

[JustLikeIcarus]

Control Panel -> Regional -> reginal options -> customize -> list seperator to change the comma to a pipe

[gwh]

Thanks I'll give that a try.

[gwh]

Ok so I opened up the excel file in access and saved it as a .csv file with a pipe delimiter. The code it produced looks like this:

 

"Essentials ? Ladies and Men's Coordinates"|"1000"|"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton."|"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton."|40.00|"Ink, Burgundy, Blue, White, Charcoal "|"6-26"|"1000.jpg"|

|"Essentials ? Ladies and Men's Coordinates"|"2000"|"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton."|"NULL"|40.00|"Ink, Burgundy, Blue, White, Charcoal "|"6-26"|"2000.jpg"|

|"Essentials ? Ladies and Men's Coordinates"|"3000"|"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton."|"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton."|40.00|"Teal, Blue, Lilac "|"SM-4XL"|"3000.jpg"|

|"Essentials ? Ladies and Men's Coordinates"|"4000"|"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton."|"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton."|40.00|"Sky, Wheat, Blue, Navy "|"6-26"|"4000.jpg"|

|"Essentials ? Ladies and Men's Coordinates"|"5000"|"3/4 Sleeve with Roll Up Feature, 65% Polyester, 35% Cotton."|"NULL"|40.00|"White"|"6-26"|"5000.jpg"|

 

But this still hasn't solved the problem. It's still only inserting the first record and then throws up the same error:

 

Invalid field count in CSV input on line 2

 

Any suggestions?

[JustLikeIcarus]

You need something to hold the place of your autoincrement field and your missing your last field to so it should have "" there.

 

See my first post about those fields.

[gwh]

That worked – thanks. The only problem is that if I add two inverted commas, ie "" to the empty columns in the excel spreadsheet and then export to .csv in access, it adds another set of inverted commas around the two existing ones, eg. """"

 

Is there anyway around this?

 

Also, I noticed that access is stripping out my dashes and replacing them with a question mark, eg.

 

Essentials ? Ladies and Men's Coordinates

 

Do you know why this is happening?

 

[JustLikeIcarus]

Thats a weird one.  Maybe something to do with the field type in access?  As for the extra quotation marks... do you have a good text editor that you can just insert what you need? Or try just setting the value of the fields to space in access.

[gwh]

Will probably just experiment with using a space in access. Thanks for the suggestion.