首页 文章

如何将CSV文件导入MySQL表

提问于
浏览 次
240

我有一个来自客户端的非标准化事件日记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表示）和文本blurbs（由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 .

我认为问题是文本blurbs包含多行，而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 记录，并且没有任何加载的数据似乎在正确的位置 .

mysql csv load-data-infile

16 回答

  • 69

    另一种解决方案是使用来自令人惊叹的csvkit套件的csvsql工具 .

    用法示例：

    csvsql --db mysql://$user:$password@localhost/$database --insert --tables $tablename  $file

    此工具可以自动推断数据类型（默认行为），创建表并将数据插入到创建的表中 . 如果表已经存在，则可以使用 --overwrite 选项删除表 . --insert 选项 - 从文件填充表 .

    安装套件

    pip install csvkit

    Prerequisites: python-dev ， libmysqlclient-dev ， MySQL-python

    apt-get install python-dev libmysqlclient-dev
pip install MySQL-python
    回复于
  • 1

    如果您使用的是MySQL Workbench（目前为6.3版本），您可以通过以下方式执行此操作：

    • 右键单击"Tables";

    • 选择表数据导入向导;

    • 选择你的csv文件并按照说明操作（也可以使用JSON）;好处是您可以根据要导入的csv文件创建新表，或将数据加载到现有表

    回复于
  • 2

    mysql命令行在导入时容易出现太多问题 . 这是你如何做到的：

    • 使用excel编辑 Headers 名称以使其没有空格

    • 另存为.csv

    • 使用免费的Navicat Lite Sql Browser导入并自动创建一个新表（给它起个名字）

    • 打开新表插入ID的主要自动编号列

    • 根据需要更改列的类型 .

    • 完成了！

    回复于
  • 22

    更改服务器名称，用户名，密码，dbname，文件路径，表名以及要插入的数据库中的字段

    <?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>'
       }
    ?>
    回复于
  • 7

    这是示例excel文件屏幕截图：

    保存为并选择.csv .

    如果您使用记事本或任何其他记事本打开，您将拥有如下所示的.csv数据屏幕截图 .

    确保删除 Headers 并在.csv中进行列对齐，如同在mysql表中一样 . 用文件夹名称替换folder_name

    LOAD DATA LOCAL INFILE 'D:/folder_name/myfilename.csv' INTO TABLE mail FIELDS TERMINATED BY ',' (fname,lname ,email, phone);

    如果是大数据，你可以拿咖啡加载！

    多数民众赞成你需要的 .

    回复于
  • 10

    phpMyAdmin 可以处理CSV导入 . 以下是步骤：

    • 准备CSV文件，使字段的顺序与MySQL表字段的顺序相同 .

    • 从CSV中删除 Headers 行（如果有），以便只有数据在文件中 .

    • 转到phpMyAdmin界面 .

    • 在左侧菜单中选择表格 .

    • 单击顶部的导入按钮 .

    • 浏览到CSV文件 .

    • 选择“使用LOAD DATA的CSV”选项 .

    • 在“终止于的字段”中输入“，” .

    • 以与数据库表中相同的顺序输入列名 .

    • 单击“开始”按钮，您就完成了 .

    这是我为未来使用做好准备的一个注释，如果其他人可以受益，我会在这里分享 .

    回复于
  • 1

    您还可以尝试使用此在线工具，根据您的CSV生成SQL创建/插入语句 . http://www.convertcsvtomysql.com/

    我喜欢这个工具：

    • 简单

    • 它不仅生成INSERT语句，还生成CREATE语句以生成表

    • 生成CREATE语句时，此工具不仅使所有字段为VARCHAR - 它还会分析CSV中的数据，并根据该分析选择正确的数据类型 .

    缺点

    • 输入文件大小限制为3mb

    • 您可能不愿意将您的数据泄露给某些第三方

    回复于
  • 115

    使用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'

    回复于
  • 1

    您可以通过列出LOAD DATA语句中的列来解决此问题 . 来自manual：

    LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

    ...所以在您的情况下，您需要按照它们在csv文件中出现的顺序列出99列 .

    回复于
  • 0

    我导入200行的最简单方法是在phpmyadmin sql窗口中低于命令

    我有一个简单的国家表有两列CountryId，CountryName

    这是.csv数据
    CSV FILE

    这是命令：

    LOAD DATA INFILE 'c:/country.csv' 
INTO TABLE country 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

    在第二列中记住一件事，永远不会出现，否则您的导入将停止

    回复于
  • 45

    如果您使用加载Excel电子表格的Windows机器，Excel的新mySql插件是惊人的 . 甲骨文的人真的在这个软件上做得很好 . 您可以直接从Excel Build 数据库连接 . 该插件将分析您的数据，并以与数据一致的格式为您设置表格 . 我有一些怪物大csv文件的数据要转换 . 这个工具节省了大量时间 .

    http://dev.mysql.com/downloads/windows/excel/

    您可以在Excel中进行更新，这些更新将在线填充到数据库中 . 这对于在超便宜的GoDaddy共享主机上创建的mySql文件非常有效 . （注意在GoDaddy上创建表时，您必须选择一些非标准设置来启用数据库的非现场访问...）

    使用此插件，您可以在XL电子表格和在线mySql数据存储之间实现纯粹的交互 .

    回复于
  • 1

    我知道问题已经过时了，但我想分享一下

    我使用此方法在 0.046sec 中导入超过100K条记录（ ~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);

    然后，假设您的字段包含 first row as the title ，您可能还想包含此行

    IGNORE 1 ROWS

    如果您的文件有 Headers 行，这就是它的样子 .

    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);
    回复于
  • 3
    回复于
  • 3

    试试这个，它对我有用

    LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;

    IGNORE 1 ROWS在这里忽略包含字段名的第一行 . 请注意，对于文件名，您必须键入文件的绝对路径 .

    回复于
  • 1

    您的问题的核心似乎是将CSV文件中的列与表中的列匹配 .

    许多图形化的mySQL客户端都有很好的导入对话框 .

    我最喜欢的工作是基于Windows的HeidiSQL . 它为您提供了构建 LOAD DATA 命令的图形界面;您可以稍后以编程方式重复使用它 .

    Screenshot: "Import textfile" dialog

    要打开“导入文本文件”对话框，请转到 Tools > Import CSV file ：

    回复于
  • 162

    PHP Query for import csv file to mysql database

    $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
    回复于

相关问题