Import Excel spreadsheet into phpMyAdmin

Question

I have been trying to import an excel (xlsx) file into phpMyAdmin.

I have tried as both excel and csv file. I have tried csv and csv using load data.

I have replaced the default field termination value from ; to ,.

Most times I was getting an variety of error messages, so I deleted my field names column and then was able to import a single row of data only.

The data was off by a column, and I guess that has something to do with the structure of my table, which has a field for ID# as a primary auto-incrementing field which is not in my csv file.

I tried adding a column for that before importing with no success. I would have thought that I could import right from the xlsx file as that is one of the choices in phpMyAdmin but everything I read or watch online converts to csv.

I could use some help here.

Answer 1

This is hopelessly late, but I'm replying in the hope that this might help a future viewer.

The reason that the CSV data is off by one is the very fact that you don't have the ID# field in it! The way to get around this is to import the file into a temporary table, then run

INSERT INTO `table`
SELECT NULL, <field1>, <field2>...
FROM `temp table`;

Adding NULL to the list of fields means that MySQL will autogenerate the ID# field (assuming you've set it to AUTO_INCREMENT).

Answer 2

I had a similar problem that I solved it by changing the 'fields enclosed by' option from " (double quote) to ' (single quote) and doing the same to the first line of the file which contains the field names. Worked like a charm. Hope this helps.