将Excel电子表格导入phpMyAdmin [英] Import Excel spreadsheet into phpMyAdmin

问题描述

我一直在尝试将一个excel（xlsx）文件导入到phpMyAdmin。

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

我试图作为excel和 csv 文件。我已经尝试csv和csv使用加载数据。

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

我已经替换了默认字段终止值从; code>，。

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.

数据是由列关闭的，我想这与我的表的结构有关，它有 ID＃作为主要自动递增字段的字段，不在我的csv文件中。

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.

在导入之前添加一个列，但没有成功。我想我可以从 xlsx 文件导入，因为这是phpMyAdmin中的选择之一，但我在线阅读或观看的内容会转换为 csv 。

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.

推荐答案

我在回复，希望这可能有助于未来的观众。

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

CSV数据关闭的原因是，你没有ID＃字段中！解决这个问题的方法是将文件导入临时表，然后运行

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`;

将 NULL 添加到字段列表MySQL将自动生成ID＃字段（假设您已将其设置为 AUTO_INCREMENT ）。

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

这篇关于将Excel电子表格导入phpMyAdmin的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！