Skip to main content
J
johngordon12
Inspiring
July 3, 2011
Question

Help trying to import an excel file into mySQL using PHP myAdmin

  • July 3, 2011
  • 2 replies
  • 4081 views

I'm sure I've done this before without any problem, but something isn't right here....

Basically I have a spreadsheet, and have created a table in PHPmyAdmin with the same fields and field names.

But when I try to import it, I'm getting the error:

Error

SQL query:

INSERT INTO `gbpukcom_products`.`Sheet1` (

`Product_ID` ,
`Product_Code` ,
`Main_Category` ,
`1st_Sub` ,
`2nd_Sub` ,
`3rd_Sub` ,
`Colour` ,
`Product_Description` ,
`Price` ,
`Image`
)
VALUES (
NULL , 'WP-FC212', 'Cladding', 'All Cladding', 'a', 'b', 'White', 'Shiplap Single Plank Starter Trim', 11.67, 'c'
), (
NULL , 'WP-FC209', 'Cladding', 'All Cladding', 'a', 'b', 'White', 'Shiplap Single Plank Butt Joint', 0.8800000000000001, 'c'
), (
NULL , 'WP-FC204', 'Cladding', 'All Cladding', 'a', 'b', 'White', 'Shiplap Single Plank 2 Part Corner Trim', 25.5, 'c'
), (
NULL , 'WP-FC202', 'Cladding', 'All Cladding', 'a', 'b', 'White', 'Shiplap Single Plank Universal Edge Trim', 13.19, 'c'
), (
NULL , 'WP-FC150', 'Cladding', 'All Cladding', 'a', 'b', 'White', 'Shiplap Single Plank 150mm', 24.55, 'c'
);

MySQL said: Documentation

#1054 - Unknown column 'Product_ID' in 'field list'

Any pointers, or a 101 on what I should be doing would be much appreciated!

This topic has been closed for replies.

2 replies

A
Anonymous
April 4, 2017

I know few commercial add-ins for really easy export/import data between Excel and MySQL. I recommend Devart MySQL Excel add-in​, it's a nice one

A
Anonymous
July 3, 2011

Is your column named Product_ID and not Product_Id, or Product_id?

If that is not it, you could post your sql information for a better comparrison.

gary

J
johngordon12Author
Inspiring
July 3, 2011

They're Product_ID in both, and both the excel file and table called 'cladding'

I tried making a copy of the excel file, cladding2 - should I be able to import an Excel file into mySQL so that it creates the table automatically on import, rather than import it into a ready made table?

Lon_Winters
Lon_Winters
Inspiring
July 4, 2011

Will have a look at that....

I tried deleting the table in PHPmyAdmin, then importing the Excel file. Which worked, but truncated some of the data by not creating a large enough Varchar string, which wasn't helpful.

Seems to want to create a new table, rather than import into an already created table with the correctly defined fields. Which would be fine if it wasn't truncating stuff on import....


I've only ond eit once, so of course I can't remember how.

But I did have to create the empty table first and make sure the column headings matched exactly. If it's truncating the data, then it seems that could be easily fixed in the initial table when you declare the length, or max number of characters.

Also, I think I may have converted the Excel file to CSV and didn't use the XLS or or XLSX format, but not sure.

PHP My Admin leaves a lot to be desired for database management if you ask me. But I've only recently starting using it, couple of years and was spoiled using Access as a MSSQL front end which was much easier for exports and imports, not to mention queries. Does anyone know of a good standalone app for MySql admin that makes these things a little more visual oriented?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices