Copy link to clipboard
Copied
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:
SQL query:
INSERT INTO `gbpukcom_products`.`Sheet1` (
#1054 - Unknown column 'Product_ID' in 'field list'
Any pointers, or a 101 on what I should be doing would be much appreciated!
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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....
Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Get ready! An upgraded Adobe Community experience is coming in January.
Learn more