从Excel（CSV）生成表结构并导入数据

Question

我想知道怎么从一个Excel（或者CSV）文件创建一个MYSQL表的结构。有没有现成的Python库可以用来做这个？

在这个过程中，列的标题会被处理成列名。数据类型会根据电子表格中每一列的内容来估算。完成后，数据会被加载到这个表里。

我有一个大约有200列的Excel文件，我想开始对它进行规范化处理。

Answer 1

这里有个简单粗暴的解决办法，适合用phpmyadmin：

• 先创建一个表，确保它有足够的列。要确保你的数据能放得下这些列。
• 把CSV文件导入到这个表里。
• 使用 建议表结构 的功能。

Answer 2

使用 xlrd 模块；可以从这里开始了解一下 http://www.python-excel.org。（声明：我是这个模块的作者。）xlrd 会把单元格分成文本、数字、日期、布尔值、错误、空白和空的几种类型。它通过查看单元格的格式来区分日期和数字，比如“dd/mm/yyyy”和“0.00”。

编写代码来处理用户输入的数据，以决定每一列应该用什么数据库数据类型，这个工作并不是那么容易自动化的。你需要亲自查看数据，然后给它们分配类型，比如整数、货币、文本、日期、日期时间、时间等等，并写代码来检查你的判断。要注意，有些数字或日期数据可能会被输入到文本框里（在图形界面上看起来没问题）。你需要有一个处理那些不符合“估计”数据类型的单元格的策略。你还需要验证和清理你的数据。确保你对文本字符串进行规范化（去掉前后的空格，把多个空格替换成一个空格）。Excel中的文本是（仅限BMP）Unicode；不要把它转成ASCII或“ANSI”格式——要在Unicode中工作，并用UTF-8编码把它放入你的数据库中。

Answer 3

为了方便自己，我把我做的事情记录下来：

1. XLRD这个工具挺好用的，不过我直接把Excel的数据保存成了CSV格式，这样我就可以用 LOAD DATA INFILE 来导入数据了。
2. 我复制了表头，然后开始写导入和规范化的脚本。
3. 这个脚本做了：用 CREATE TABLE 创建了一个表，所有的列都设置为文本类型，除了主键。
4. 通过查询MySQL的 LOAD DATA LOCAL INFILE，把所有的CSV数据加载到文本字段里。
5. 根据 PROCEDURE ANALYSE 的输出，我能够用 ALTER TABLE 来给列设置合适的数据类型和长度。PROCEDURE ANALYSE 会把有少量不同值的列返回为 ENUM 类型，这个我不需要，但后来发现对规范化很有帮助。用 PROCEDURE ANALYSE 看200列数据非常简单。而从PhpMyAdmin得到的表结构建议则没什么用。
6. 我写了一些规范化的代码，主要是对列使用 SELECT DISTINCT 来获取不同的值，然后把结果 INSERT 到不同的表里。我先在旧表里加了一个外键列。然后在 INSERT 之后，获取它的ID，并用 UPDATE 更新外键列。循环结束后，我删除了旧的列，只留下外键列。对于多个依赖列也是这样做的。这个过程比我预想的要快得多。
7. 我运行了(django) python manage.py inspectdb，把输出复制到models.py，并添加了所有的 ForeignKeyField，因为MyISAM不支持外键。我还写了一些python代码在views.py和urls.py里，做了几个模板……完成了！

基于来自 https://www.get-information-schools.service.gov.uk/Downloads 的数据，创建了 https://blocl.uk/schools。