我有一个来自客户端的非规范化事件日志 CSV,我试图将其加载到 MySQL 表中,以便我可以重构为一种健全的格式。我创建了一个名为“CSVImport”的表,该表对于 CSV 文件的每一列都有一个字段。 CSV 包含 99 列,因此这本身就是一项艰巨的任务:
CREATE TABLE 'CSVImport' (id INT);
ALTER TABLE CSVImport ADD COLUMN Title VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN Company VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN NumTickets VARCHAR(256);
...
ALTER TABLE CSVImport Date49 ADD COLUMN Date49 VARCHAR(256);
ALTER TABLE CSVImport Date50 ADD COLUMN Date50 VARCHAR(256);
表上没有约束,所有字段都包含 VARCHAR(256) 值,除了包含计数(由 INT 表示)、是/否(由 BIT 表示)、价格(由 DECIMAL 表示)和文本简介(由 TEXT 表示)。
我试图将数据加载到文件中:
LOAD DATA INFILE '/home/paul/clientdata.csv' INTO TABLE CSVImport;
Query OK, 2023 rows affected, 65535 warnings (0.08 sec)
Records: 2023 Deleted: 0 Skipped: 0 Warnings: 198256
SELECT * FROM CSVImport;
| NULL | NULL | NULL | NULL | NULL |
...
整个表都填满了 NULL
。
我认为问题在于文本简介包含不止一行,并且 MySQL 正在解析文件,就好像每一新行都对应一个数据库行一样。我可以毫无问题地将文件加载到 OpenOffice 中。
clientdata.csv 文件包含 2593 行和 570 条记录。第一行包含列名。我认为它是逗号分隔的,并且文本显然是用双引号分隔的。
更新:
如有疑问,请阅读手册:http://dev.mysql.com/doc/refman/5.0/en/load-data.html
我在 LOAD DATA
语句中添加了一些信息,表明 OpenOffice 足够聪明地进行推断,现在它加载了正确数量的记录:
LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
但是仍然有很多完整的 NULL
记录,并且加载的数据似乎都没有放在正确的位置。
使用 mysqlimport 将表加载到数据库中:
mysqlimport --ignore-lines=1 \
--fields-terminated-by=, \
--local -u root \
-p Database \
TableName.csv
我在 http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/ 找到的
要使分隔符成为制表符,请使用 --fields-terminated-by='\t'
您问题的核心似乎是将 CSV 文件中的列与表中的列相匹配。
许多图形化的 mySQL 客户端都为这类事情提供了非常好的导入对话框。
我最喜欢这项工作的是基于 Windows 的 HeidiSQL。它为您提供了构建 LOAD DATA
命令的图形界面;您可以稍后以编程方式重新使用它。
https://i.stack.imgur.com/RTux4.png
Screenshot: "Import textfile" dialog
要打开“导入文本文件”对话框,请转到 Tools > Import CSV file
:
https://i.stack.imgur.com/2HHSa.png
我已导入 200 多行的最简单方法是在 phpmyadmin sql 窗口中的命令下方
我有一个简单的国家表,有两列 CountryId,CountryName
https://i.stack.imgur.com/yOSm3.png
这是命令:
LOAD DATA INFILE 'c:/country.csv'
INTO TABLE country
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
记住一件事,永远不要出现在第二列,否则您的导入将停止
LOAD DATA LOCAL INFILE
。如果这会引发错误 1148“不允许使用的命令”,您可以通过在命令行中使用 --local-infile
运行 mysql 来启用它。
LOAD DATA LOCAL INFILE
运行它,一切都很好
我使用这种方法在 0.046 秒内导入超过 10 万条记录 (~5MB)
这是你如何做到的:
LOAD DATA LOCAL INFILE
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);
包含最后一行非常重要,如果您有多个字段,即通常它会跳过最后一个字段(MySQL 5.6.17)
LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);
然后,假设您将第一行作为字段的标题,您可能还想包含这一行
IGNORE 1 ROWS
如果您的文件有标题行,这就是它的样子。
LOAD DATA LOCAL INFILE
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(field_1,field_2 , field_3);
(field_1,field_2 , field_3);
是指 .csv 文件还是表中的列列表?即,这些字段是什么?
phpMyAdmin 可以处理 CSV 导入。以下是步骤:
准备 CSV 文件以使字段的顺序与 MySQL 表字段的顺序相同。从 CSV(如果有)中删除标题行,以便文件中只有数据。进入 phpMyAdmin 界面。在左侧菜单中选择表格。单击顶部的导入按钮。浏览到 CSV 文件。选择“使用加载数据的 CSV”选项。在“终止于”的字段中输入“,”。按照与数据库表中相同的顺序输入列名。单击开始按钮,您就完成了。
这是我为将来使用而准备的笔记,如果其他人可以受益,请在此分享。
NULL
。
如果您使用的是 MySQL Workbench(当前为 6.3 版本),您可以通过以下方式执行此操作:
右键单击“表格”;选择表数据导入向导;选择您的 csv 文件并按照说明进行操作(也可以使用 JSON);好处是您可以根据要导入的 csv 文件创建新表或将数据加载到现有表
https://i.stack.imgur.com/5az8h.jpg
LOAD DATA INFILE
和 mysqlimport
的错误(“这个版本的 mysql 不支持这个”)
INSERT
查询并尝试一次完成所有操作,但看起来这样做实际上每行运行一次 INSERT
。
您可以通过列出 LOAD DATA 语句中的列来解决此问题。从 manual:
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
...所以在您的情况下,您需要按照它们在 csv 文件中出现的顺序列出 99 列。
试试这个,它对我有用
LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;
IGNORE 1 ROWS 这里忽略包含字段名的第一行。请注意,对于文件名,您必须键入文件的绝对路径。
我看到了一些奇怪的东西。您用于转义的字符与用于 ENCLOSING 的字符相同。因此,当引擎发现'"'时,它不知道该怎么做,我认为这就是为什么似乎没有任何东西在正确的位置。我认为如果删除 ESCAING 行,应该会运行得很好。比如:
LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
除非您分析(手动,视觉,...)您的 CSV 并找到哪个字符用于转义。有时是'\'。但是,如果您没有它,请不要使用它。
mysql命令行在导入时容易出现太多问题。这是您的操作方法:
使用 excel 编辑标题名称以不包含空格
另存为 .csv
使用免费的 Navicat Lite Sql Browser 导入并自动创建一个新表(给它一个名字)
打开新表为 ID 插入一个主要的自动编号列
根据需要更改列的类型。
完毕!
另一个解决方案是使用令人惊叹的 csvkit 套件中的 csvsql 工具。
使用示例:
csvsql --db mysql://$user:$password@localhost/$database --insert --tables $tablename $file
该工具可以自动推断数据类型(默认行为),创建表并将数据插入到创建的表中。 --overwrite
选项可用于删除已存在的表。 --insert
选项 — 从文件中填充表格。
安装套件
pip install csvkit
先决条件: python-dev
、libmysqlclient-dev
、MySQL-python
apt-get install python-dev libmysqlclient-dev
pip install MySQL-python
如果您使用 Intellij https://www.jetbrains.com/datagrip/features/importexport.html
https://i.stack.imgur.com/79O2N.png
我使用 mysql 工作台来做同样的工作。
创建新架构打开新创建的架构,右键单击“表”并选择“表数据导入向导”,给出 csv 文件路径和表名,最后配置您的列类型,因为向导会根据它们的值设置默认列类型。
注意:使用“tail -f [mysqlworkbenchpath]/log/wb*.log”查看 mysql 工作台的日志文件是否有任何错误
如何将csv文件导入sql表
示例文件:Overseas_trade_index
数据 CSV 文件
脚步:
需要为overseas_trade_index 创建表。需要创建与 csv 文件相关的列。 SQL 查询:( id int not null 主键 auto_increment,series_reference varchar (60),period varchar (60),data_value decimal(60,0),status varchar (60),units varchar (60),magnitude int(60),主题文本(60),组文本(60),series_title_1 varchar(60),series_title_2 varchar(60),series_title_3 varchar(60),series_title_4 varchar(60),series_title_5 varchar(60),);需要在终端连接mysql数据库。 =>显示数据库; =>使用数据库; =>显示表格;请输入此命令将 csv 数据导入 mysql 表。将文件 '/home/desktop/Documents/overseas.csv' 中的数据加载到以 ',' 结尾的表 trade_index 字段中,以 '\n' 结尾(series_reference,period,data_value,status,units,magnitude,subject,series_title1,series_title_2, series_title_3,series_title_4,series_title_5);在sqldatabase上找到这个海外贸易指数数据:select * from trade_index;
如果您使用的是加载了 Excel 电子表格的 Windows 机器,那么 Excel 的新 mySql 插件非常出色。甲骨文的人在那个软件上确实做得很好。您可以直接从 Excel 建立数据库连接。该插件将分析您的数据,并以与数据一致的格式为您设置表格。我有一些巨大的 csv 数据文件要转换。这个工具可以节省大量时间。
http://dev.mysql.com/downloads/windows/excel/
您可以从 Excel 中进行更新,这些更新将在线填充到数据库中。这对于在超便宜的 GoDaddy 共享主机上创建的 mySql 文件非常有效。 (请注意,当您在 GoDaddy 创建表时,您必须选择一些非标准设置以启用数据库的异地访问...)
使用此插件,您可以在 XL 电子表格和在线 mySql 数据存储之间进行纯交互。
我知道我的回答迟了,但我想提一些其他的方法。最简单的一种是使用命令行。步骤如下:
通过输入以下命令访问 MySQL CLI:
mysql -u 我的用户名 -p
在数据库中创建表
使用新模式;创建表employee_details(id INTEGER,employee_name VARCHAR(100),employee_age INTEGER,PRIMARY KEY(id));
将 CSV 文件导入表中。我们可以提及文件路径,也可以将文件存储在 MySQL 服务器的默认目录中。
LOAD DATA INFILE '导出的 csv 文件的路径' INTO TABLE employee_details FIELDS TERMINATED BY ',' IGNORE 1 ROWS;
它是众多解决方案中唯一的一个,我在此tutorial中找到了它。如果将 CSV 文件加载到 MySQL 数据库中是您的日常任务,那么自动化此过程会更好。在这种情况下,您可以使用一些允许您按计划加载数据的第三方工具。
将csv文件导入mysql数据库的PHP查询
$query = <<<EOF
LOAD DATA LOCAL INFILE '$file'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name,mobile,email)
EOF;
if (!$result = mysqli_query($this->db, $query))
{
exit(mysqli_error($this->db));
}
**示例 CSV 文件数据**
name,mobile,email
Christopher Gritton,570-686-3439,ChristopherKGritton@inbound.plus
Brandon Wilson,541-309-5149,BrandonMWilson@inbound.plus
Craig White,516-795-8065,CraigJWhite@inbound.plus
David Whitney,713-214-3966,DavidCWhitney@inbound.plus
这是示例 excel 文件屏幕截图:
https://i.stack.imgur.com/UkOaA.jpg
另存为并选择 .csv。
如果您使用 notepad++ 或任何其他记事本打开,您将获得如下所示的 .csv 数据屏幕截图。
https://i.stack.imgur.com/QVFEV.jpg
确保删除标题并在 .csv 中进行列对齐,如 mysql 表中一样。将文件夹名称替换为您的文件夹名称
LOAD DATA LOCAL INFILE 'D:/folder_name/myfilename.csv' INTO TABLE mail FIELDS TERMINATED BY ',' (fname,lname ,email, phone);
如果是大数据,您可以喝咖啡并加载它!
这就是你所需要的。
更改服务器名、用户名、密码、数据库名、文件路径、表名和要插入的数据库中的字段
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "bd_dashboard";
//For create connection
$conn = new mysqli($servername, $username, $password, $dbname);
$query = "LOAD DATA LOCAL INFILE
'C:/Users/lenovo/Desktop/my_data.csv'
INTO TABLE test_tab
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name,mob)";
if (!$result = mysqli_query($conn, $query)){
echo '<script>alert("Oops... Some Error occured.");</script>';
exit();
//exit(mysqli_error());
}else{
echo '<script>alert("Data Inserted Successfully.");</script>'
}
?>
我使用 phpmyadmin 以简单的方式完成了它。我按照@Farhan 的步骤操作,但所有数据都在单列中。我是怎么做的:
创建了一个 CSV 文件并删除了带有列名的标题行。只保留数据。我创建了一个列名与 csv 列匹配的表。请记住为每一列分配适当的类型。我刚刚选择了导入并转到导入选项卡。在浏览中,我选择了 CSV 文件并保持所有选项不变。令我惊讶的是,所有数据都成功导入到了相应的列中。
不定期副业成功案例分享
mysqlimport
在幕后使用LOAD DATA INFILE...
,所以它几乎是一回事。LOAD DATA INFILE
一样,您需要先创建一个表才能使用mysqlimport
。--fields-optionally-enclosed-by=\"
和 `--fields-escaped-by=\`