将Excel表格导入数据库 [英] importing excel table into database

问题描述

我有一个xlsx格式的下表，我想将其导入到我的sql数据库中:

表非常复杂，我只想要'1)HEADING'之后的记录

我一直在寻找要导入sql的php库，但它们似乎仅用于简单的excel文件.

解决方案

您有两种方法可以实现:

第一种方法:

1)将其导出为某种文本格式.最简单的可能是制表符分隔的版本，但是CSV也可以使用.

2)使用加载数据功能.参见 http://dev.mysql.com/doc/refman /5.1/zh-CN/load-data.html

3)向下浏览页面的一半，因为它将为制表符分隔的数据提供一个很好的示例:

以'\ t'结尾的文件，以'\'隔开的

4)检查您的数据.有时引用或转义会出现问题，您需要调整源代码，导入命令，或者通过SQL进行后处理可能会更容易.

第二种方法:

有一个简单的在线工具可以执行此操作，称为 sqlizer.io .

您将XLSX文件上传到其中，输入工作表名称和单元格范围，它将生成CREATE TABLE语句和一堆INSERT语句，以将所有数据导入MySQL数据库.

I have a following table in xlsx format which I would like to import into the my sql database:

The table is pretty complicated and I only want the records after '1)HEADING'

I have been looking at php libraries to import into sql but they only seem to be for simple excel files.

解决方案

You have two ways to realize that :

First method :

1) Export it into some text format. The easiest will probably be a tab-delimited version, but CSV can work as well.

2) Use the load data capability. See http://dev.mysql.com/doc/refman/5.1/en/load-data.html

3) Look half way down the page, as it will gives a good example for tab separated data:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\'

4) Check your data. Sometimes quoting or escaping has problems, and you need to adjust your source, import command-- or it may just be easier to post-process via SQL.

Second method :

There's a simple online tool that can do this called sqlizer.io.

You upload an XLSX file to it, enter a sheet name and cell range, and it will generate a CREATE TABLE statement and a bunch of INSERT statements to import all your data into a MySQL database.

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