导入 CSV 到 mysql 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11077801/
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 CSV to mysql table
提问by lcm
What is the best/fastest way to upload a csv file into a mysql table? I would like for the first row of data be used as the column names.
将 csv 文件上传到 mysql 表的最佳/最快方法是什么？我想将第一行数据用作列名。
Found this:
发现这个：
How to import CSV file to MySQL table
But the only answer was to use a GUI and not shell?
但唯一的答案是使用 GUI 而不是 shell？
回答by hjpotter92
Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.
您可以将 MYSQL 直接链接到它并使用以下 SQL 语法上传信息，而不是编写脚本来从 CSV 文件中提取信息。
To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.
要将 Excel 文件导入 MySQL，首先将其导出为 CSV 文件。从生成的 CSV 文件中删除 CSV 标题以及 Excel 可能放在 CSV 文件末尾的空数据。
You can then import it into a MySQL table by running:
然后，您可以通过运行将其导入 MySQL 表：
load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)
as read on: Import CSV file directly into MySQL
继续阅读：将CSV 文件直接导入 MySQL
EDIT
编辑
For your case, you'll need to write an interpreter first, for finding the first row, and assigning them as column names.
对于您的情况，您需要先编写一个解释器，以查找第一行并将它们分配为列名。
EDIT-2
编辑-2
From MySQL docs on LOAD DATAsyntax:
来自 MySQL 文档的LOAD DATA语法：
The
IGNORE number LINESoption can be used to ignore lines at the start of the file. For example, you can useIGNORE 1 LINESto skip over an initial header line containing column names:LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
该
IGNORE number LINES选项可用于忽略文件开头的行。例如，您可以使用IGNORE 1 LINES跳过包含列名的初始标题行：LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;
Therefore, you can use the following statement:
因此，您可以使用以下语句：
LOAD DATA LOCAL INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(uniqName, uniqCity, uniqComments)
回答by Hawkee
Here's a simple PHP command line script that will do what you need:
这是一个简单的 PHP 命令行脚本，可以满足您的需求：
<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$database = 'database';
$db = mysql_connect($host, $user, $pass);
mysql_query("use $database", $db);
/********************************************************************************/
// Parameters: filename.csv table_name
$argv = $_SERVER[argv];
if($argv[1]) { $file = $argv[1]; }
else {
echo "Please provide a file name\n"; exit;
}
if($argv[2]) { $table = $argv[2]; }
else {
$table = pathinfo($file);
$table = $table['filename'];
}
/********************************************************************************/
// Get the first row to create the column headings
$fp = fopen($file, 'r');
$frow = fgetcsv($fp);
foreach($frow as $column) {
if($columns) $columns .= ', ';
$columns .= "`$column` varchar(250)";
}
$create = "create table if not exists $table ($columns);";
mysql_query($create, $db);
/********************************************************************************/
// Import the data into the newly created table.
$file = $_SERVER['PWD'].'/'.$file;
$q = "load data infile '$file' into table $table fields terminated by ',' ignore 1 lines";
mysql_query($q, $db);
?>
It will create a table based on the first row and import the remaining rows into it. Here is the command line syntax:
它将基于第一行创建一个表并将其余行导入其中。这是命令行语法：
php csv_import.php csv_file.csv table_name
回答by Mukesh
First create a table in the database with same numbers of columns that are in the csv file.
首先在数据库中创建一个表，其列数与 csv 文件中的列数相同。
Then use following query
然后使用以下查询
LOAD DATA INFILE 'D:/Projects/testImport.csv' INTO TABLE cardinfo
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
回答by Jose Ortiz
if you have the ability to install phpadmin there is a import section where you can import csv files to your database there is even a checkbox to set the header to the first line of the file contains the table column names (if this is unchecked, the first line will become part of the data
如果您能够安装 phpadmin，则有一个导入部分，您可以在其中将 csv 文件导入到数据库中，甚至还有一个复选框可以将标题设置为包含表列名称的文件的第一行（如果未选中，则第一行将成为数据的一部分
回答by Rakesh
To load data from text file or csv file the command is
要从文本文件或 csv 文件加载数据，命令是
load data local infile 'file-name.csv'
into table table-name
fields terminated by '' enclosed by '' lines terminated by '\n' (column-name);
In above command, in my case there is only one column to be loaded so there is no "terminated by" and "enclosed by" so I kept it empty else programmer can enter the separating character . for e.g . ,(comma) or " or ; or any thing.
在上面的命令中，在我的情况下，只有一列要加载，因此没有“终止于”和“封闭于”，因此我将其留空，否则程序员可以输入分隔符。例如。,（逗号）或 " 或 ; 或任何东西。
**for people who are using mysql version 5 and above **
**对于使用mysql 5及以上版本的人**
Before loading the file into mysql must ensure that below tow line are added in side etc/mysql/my.cnf
在将文件加载到 mysql 之前，必须确保在侧边添加下面的拖线 etc/mysql/my.cnf
to edit my.cnf command is
编辑 my.cnf 命令是
sudo vi /etc/mysql/my.cnf
sudo vi /etc/mysql/my.cnf
[mysqld]
local-infile
[mysql]
local-infile
回答by marciomolusco
If you start mysql as "mysql -u -p --local-infile ", it will work fine
如果你以“mysql -u -p --local-infile”启动mysql，它会正常工作
回答by ravenchilde
I wrote some code to do this, i'll put in a few snippets:
我写了一些代码来做到这一点，我会放一些片段：
$dir = getcwd(); // Get current working directory where this .php script lives
$fileList = scandir($dir); // scan the directory where this .php lives and make array of file names
Then get the CSV headers so you can tell mysql how to import (note: make sure your mysql columns exactly match the csv columns):
然后获取 CSV 标题，以便您可以告诉 mysql 如何导入（注意：确保您的 mysql 列与 csv 列完全匹配）：
//extract headers from .csv for use in import command
$headers = str_replace("\"", "`", array_shift(file($path)));
$headers = str_replace("\n", "", $headers);
Then send your query to the mysql server:
然后将您的查询发送到 mysql 服务器：
mysqli_query($cons, '
LOAD DATA LOCAL INFILE "'.$path.'"
INTO TABLE '.$dbTable.'
FIELDS TERMINATED by \',\' ENCLOSED BY \'"\'
LINES TERMINATED BY \'\n\'
IGNORE 1 LINES
('.$headers.')
;
')or die(mysql_error());
回答by Bob Stein
Here's how I did it in Python using csvand the MySQL Connector:
这是我在 Python 中使用csv和MySQL Connector 的方法：
import csv
import mysql.connector
credentials = dict(user='...', password='...', database='...', host='...')
connection = mysql.connector.connect(**credentials)
cursor = connection.cursor(prepared=True)
stream = open('filename.csv', 'rb')
csv_file = csv.DictReader(stream, skipinitialspace=True)
query = 'CREATE TABLE t ('
query += ','.join('`{}` VARCHAR(255)'.format(column) for column in csv_file.fieldnames)
query += ')'
cursor.execute(query)
for row in csv_file:
query = 'INSERT INTO t SET '
query += ','.join('`{}` = ?'.format(column) for column in row.keys())
cursor.execute(query, row.values())
stream.close()
cursor.close()
connection.close()
Key points
关键点
- Use prepared statements for the INSERT
- Open the file.csv in
'rb'binary - Some CSV files may need tweaking, such as the
skipinitialspaceoption. - If
255isn't wide enough you'll get errors on INSERT and have to start over. - Adjust column types, e.g.
ALTER TABLE t MODIFY `Amount` DECIMAL(11,2); - Add a primary key, e.g.
ALTER TABLE t ADD `id` INT PRIMARY KEY AUTO_INCREMENT;
回答by agentv
I wrestled with this for some time. The problem lies not in how to load the data, but how to construct the table to hold it. You must generate a DDL statement to build the table before importing the data.
我为此纠结了一段时间。问题不在于如何加载数据，而在于如何构造表来保存数据。在导入数据之前，您必须生成一个 DDL 语句来构建表。
Particularly difficult if the table has a large number of columns.
如果表有大量列，则特别困难。
Here's a python script that (almost) does the job:
这是一个（几乎）完成这项工作的python脚本：
#!/usr/bin/python
import sys
import csv
# get file name (and hence table name) from command line
# exit with usage if no suitable argument
if len(sys.argv) < 2:
sys.exit('Usage: ' + sys.argv[0] + ': input CSV filename')
ifile = sys.argv[1]
# emit the standard invocation
print 'create table ' + ifile + ' ('
with open(ifile + '.csv') as inputfile:
reader = csv.DictReader(inputfile)
for row in reader:
k = row.keys()
for item in k:
print '`' + item + '` TEXT,'
break
print ')\n'
The problem it leaves to solve is that the final field name and data type declaration is terminated with a comma, and the mySQL parser won't tolerate that.
它要解决的问题是最终的字段名称和数据类型声明以逗号终止，而 mySQL 解析器不会容忍这种情况。
Of course it also has the problem that it uses the TEXT data type for every field. If the table has several hundred columns, then VARCHAR(64) will make the table too large.
当然它也有问题，它对每个字段都使用 TEXT 数据类型。如果表有几百列，那么 VARCHAR(64) 会使表变得太大。
This also seems to break at the maximum column count for mySQL. That's when it's time to move to Hive or HBase if you are able.
这似乎也打破了 mySQL 的最大列数。如果可以的话，那是时候转移到 Hive 或 HBase 了。
回答by Elangovan
Import CSV Files into mysql table
将 CSV 文件导入 mysql 表
LOAD DATA LOCAL INFILE 'd:\Site.csv' INTO TABLE `siteurl` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Character Escape Sequence
##代码## An ASCII NUL (0x00) character
\b A backspace character
\n A newline (linefeed) character
\r A carriage return character
\t A tab character.
\Z ASCII 26 (Control+Z)
\N NULL
visits : http://www.webslessons.com/2014/02/import-csv-files-using-php-and-mysql.html
访问：http: //www.webslessons.com/2014/02/import-csv-files-using-php-and-mysql.html
