将数据从 Excel 电子表格或 CVS 导入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8031193/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import Data from Excel Spreadsheet or CVS into MySQL
提问by Kelly Roper
I have a spreadsheet which really has only one complicated table. I basically convert the spreadsheet to a cvs and use a groovy script to generate the INSERT scripts.
我有一个电子表格，它实际上只有一个复杂的表格。我基本上将电子表格转换为 cvs 并使用 groovy 脚本生成 INSERT 脚本。
However, I cannot do this with a table that has 28 fields with data within some of the fields on the spreadsheet that make importing into the CVS even more complicated. So the fields in the new CVS are not differentiated properly or my script has not accounted for it.
但是，我无法使用具有 28 个字段的表格来执行此操作，其中电子表格的某些字段中的数据会使导入 CVS 变得更加复杂。所以新的 CVS 中的字段没有正确区分或者我的脚本没有考虑到它。
Does anyone have any suggestions on a better approach to do this? Thanks.
有没有人对更好的方法有任何建议？谢谢。
采纳答案by Devart
Have a look at LOAD DATA INFILEstatement. It will help you to import data from the CSV file into table.
看看LOAD DATA INFILE语句。它将帮助您将数据从 CSV 文件导入表中。
回答by panofish
This is a recurrent question on stackoverflow. Here is an updated answer.
这是关于 stackoverflow 的一个反复出现的问题。这是更新的答案。
There are actually several ways to import an excel file in to a MySQL database with varying degrees of complexity and success.
实际上有多种方法可以将 excel 文件导入 MySQL 数据库，其复杂程度和成功程度各不相同。
Excel2MySQLor Navicatutilities. Full disclosure, I am the author of Excel2MySQL. These 2 utilities aren't free, but they are the easiest option and have the fewest limitations. They also include additional features to help with importing Excel data into MySQL. For example, Excel2MySQL automatically creates your table and automatically optimizes field data types like dates, times, floats, etc. If your in a hurry or can't get the other options to work with your data then these utilities may suit your needs.
LOAD DATA INFILE: This popular option is perhaps the most technical and requires some understanding of MySQL command execution. You must manually create your table before loading and use appropriately sized VARCHAR field types. Therefore, your field data types are not optimized. LOAD DATA INFILE has trouble importing large files that exceed 'max_allowed_packet' size. Special attention is required to avoid problems importing special characters and foreign unicode characters. Here is a recent example I used to import a csv file named test.csv.
phpMyAdmin: Select your database first, then select the Import tab. phpMyAdmin will automatically create your table and size your VARCHAR fields, but it won't optimize the field types. phpMyAdmin has trouble importing large files that exceed 'max_allowed_packet' size.
MySQL for Excel: This is a free Excel Add-in from Oracle. This option is a bit tedious because it uses a wizard and the import is slow and buggy with large files, but this may be a good option for small files with VARCHAR data. Fields are not optimized.
Excel2MySQL或Navicat实用程序。完全公开，我是 Excel2MySQL 的作者。这 2 个实用程序不是免费的，但它们是最简单的选择并且限制最少。它们还包括帮助将 Excel 数据导入 MySQL 的附加功能。例如，Excel2MySQL 会自动创建您的表格并自动优化日期、时间、浮点数等字段数据类型。如果您赶时间或无法获得其他选项来处理您的数据，那么这些实用程序可能适合您的需求。
LOAD DATA INFILE：这个流行的选项可能是最具技术性的，需要对 MySQL 命令执行有一定的了解。您必须在加载之前手动创建表并使用适当大小的 VARCHAR 字段类型。因此，您的字段数据类型未优化。LOAD DATA INFILE 无法导入超过“max_allowed_packet”大小的大文件。需要特别注意避免导入特殊字符和外来 unicode 字符时出现问题。这是我最近用来导入名为 test.csv 的 csv 文件的示例。
phpMyAdmin：首先选择您的数据库，然后选择导入选项卡。phpMyAdmin 将自动创建您的表并调整您的 VARCHAR 字段的大小，但它不会优化字段类型。phpMyAdmin 无法导入超过“max_allowed_packet”大小的大文件。
MySQL for Excel：这是来自 Oracle 的免费 Excel 插件。此选项有点乏味，因为它使用向导，并且导入速度慢且大文件有问题，但对于包含 VARCHAR 数据的小文件，这可能是一个不错的选择。字段未优化。
回答by Philip Olson
For comma-separated values (CSV) files, the results view panel in Workbench has an "Import records from external file" option that imports CSV data directly into the result set. Execute that and click "Apply" to commit the changes.
对于逗号分隔值 (CSV) 文件，Workbench 中的结果视图面板有一个“从外部文件导入记录”选项，可将 CSV 数据直接导入到结果集中。执行并单击“应用”以提交更改。
For Excel files, consider using the official MySQL for Excel plugin.
对于 Excel 文件，请考虑使用官方MySQL for Excel 插件。
回答by arober11
A while back I answered a very similar question on the EEsite, and offered the following block of Perl, as a quick and dirty example of how you could directly load an Excel sheet into MySQL. Bypassing the need to export / import via CSV and so hopefully preserving more of those special characters, and eliminating the need to worry about escaping the content.
不久前，我在EE站点上回答了一个非常相似的问题，并提供了以下 Perl 块，作为如何将 Excel 工作表直接加载到 MySQL 的快速而肮脏的示例。绕过通过 CSV 导出/导入的需要，因此希望保留更多这些特殊字符，并且无需担心转义内容。
#!/usr/bin/perl -w
# Purpose: Insert each Worksheet, in an Excel Workbook, into an existing MySQL DB, of the same name as the Excel(.xls).
# The worksheet names are mapped to the table names, and the column names to column names.
# Assumes each sheet is named and that the first ROW on each sheet contains the column(field) names.
#
use strict;
use Spreadsheet::ParseExcel;
use DBI;
use Tie::IxHash;
die "You must provide a filename to # ./Excel2mysql.pl test.xls
FILE: test.xls
DB: test
Collection Count: 1
Table(WorkSheet name):Sheet1
Rows inserted(Rows):9892
to be parsed as an Excel file" unless @ARGV;
my $sDbName = $ARGV[0];
$sDbName =~ s/\.xls//i;
my $oExcel = new Spreadsheet::ParseExcel;
my $oBook = $oExcel->Parse($ARGV[0]);
my $dbh = DBI->connect("DBI:mysql:database=$sDbName;host=192.168.123.123","root", "xxxxxx", {'RaiseError' => 1,AutoCommit => 1});
my ($sTableName, %hNewDoc, $sFieldName, $iR, $iC, $oWkS, $oWkC, $sSql);
print "FILE: ", $oBook->{File} , "\n";
print "DB: $sDbName\n";
print "Collection Count: ", $oBook->{SheetCount} , "\n";
for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
{
$oWkS = $oBook->{Worksheet}[$iSheet];
$sTableName = $oWkS->{Name};
print "Table(WorkSheet name):", $sTableName, "\n";
for(my $iR = $oWkS->{MinRow} ; defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; $iR++)
{
tie ( %hNewDoc, "Tie::IxHash");
for(my $iC = $oWkS->{MinCol} ; defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ; $iC++)
{
$sFieldName = $oWkS->{Cells}[$oWkS->{MinRow}][$iC]->Value;
$sFieldName =~ s/[^A-Z0-9]//gi; #Strip non alpha-numerics from the Column name
$oWkC = $oWkS->{Cells}[$iR][$iC];
$hNewDoc{$sFieldName} = $dbh->quote($oWkC->Value) if($oWkC && $sFieldName);
}
if ($iR == $oWkS->{MinRow}){
#eval { $dbh->do("DROP TABLE $sTableName") };
$sSql = "CREATE TABLE IF NOT EXISTS $sTableName (".(join " VARCHAR(512), ", keys (%hNewDoc))." VARCHAR(255))";
#print "$sSql \n\n";
$dbh->do("$sSql");
} else {
$sSql = "INSERT INTO $sTableName (".(join ", ",keys (%hNewDoc)).") VALUES (".(join ", ",values (%hNewDoc)).")\n";
#print "$sSql \n\n";
eval { $dbh->do("$sSql") };
}
}
print "Rows inserted(Rows):", ($oWkS->{MaxRow} - $oWkS->{MinRow}), "\n";
}
# Disconnect from the database.
$dbh->disconnect();
Note:
笔记：
- Change the connection ($oConn) string to suit, and if needed add a user-id and password to the arguments.
- If you need XLSX support a quick switch to Spreadsheet::XLSX is all that's needed. Alternatively it only takes a few lines of code, to detect the filetype and call the appropriate library.
- The above is a simple hack, assumes everything in a cell is a string / scalar, if preserving type is important, a little function with a few regexp can be used in conjunction with a few if statements to ensure numbers / dates remain in the applicable format when written to the DB
The above code is dependent on a number of CPAN modules, that you can install, assuming outbound ftp access is permitted, via a:
cpan YAML Data::Dumper Spreadsheet::ParseExcel Tie::IxHash Encode Scalar::Util File::Basename DBD::mysql
- 更改连接 ($oConn) 字符串以适应，并在需要时将用户 ID 和密码添加到参数中。
- 如果您需要 XLSX 支持，只需快速切换到 Spreadsheet::XLSX。或者，只需几行代码即可检测文件类型并调用适当的库。
- 以上是一个简单的技巧，假设单元格中的所有内容都是字符串/标量，如果保留类型很重要，可以将带有一些正则表达式的小函数与一些 if 语句结合使用，以确保数字/日期保持在适用范围内写入数据库时的格式
上面的代码依赖于许多 CPAN 模块，您可以安装这些模块，假设允许出站 ftp 访问，通过：
cpan YAML Data::Dumper Spreadsheet::ParseExcel Tie::IxHash Encode Scalar::Util File::Basename DBD::mysql
Should return something along the following lines (tis rather slow, due to the auto commit):
应该按照以下几行返回一些内容（由于自动提交，相当慢）：
##代码##