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

[johngordon12]

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:

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

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

[Guest]

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

[johngordon12]

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?

[Guest]

I don't use Excel but rather OpenOffice, but can you export your excel files as a MySQL .sql file? If so, then import them into MySQL through phpMyAdmin. You will need to define the dbase within MySQL prior to importing the .sql file.

[johngordon12]

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....

[Lon_Winters]

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?

[Guest]

You might want to try Navicat for Windows, Mac OS X, and Linux (www.navicat.com) and SQLyog for Windows only (www.webyog.com/en/). Both have free and paid editions.

[David_Powers]

Lon Winters wrote:

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

That's the best way of importing from Excel. I haven't done it for a long time, but it's pretty easy.

[Guest]

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