首页 文章

将CSV导入mysql表

提问于
浏览 次
93

将csv文件上传到mysql表的最佳/最快方法是什么？我想将第一行数据用作列名 .

发现这个：

How to import CSV file to MySQL table

但唯一的答案是使用GUI而不是shell？

mysql csv import load-data-infile database-table

12 回答

  • 1

    您可以直接将MYSQL链接到它并使用以下SQL语法上载信息，而不是编写脚本以从CSV文件中提取信息 .

    要将Excel文件导入MySQL，请先将其导出为CSV文件 . 从生成的CSV文件中删除CSV Headers 以及Excel可能放在CSV文件末尾的空数据 .

    然后，您可以通过运行以下命令将其导入MySQL表：

    load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
  enclosed by '"'
  lines terminated by '\n'
    (uniqName, uniqCity, uniqComments)

    阅读：Import CSV file directly into MySQL

    编辑

    对于您的情况，您需要先编写一个解释器，以查找第一行，并将它们指定为列名 .

    编辑-2

    来自MySQL文档on LOAD DATA syntax：

    IGNORE数字LINES选项可用于忽略文件开头的行 . 例如，您可以使用IGNORE 1 LINES跳过包含列名的初始 Headers 行：LOAD DATA INFILE'/ tmp / test.txt'INTO TABLE test IGNORE 1 LINES;

    因此，您可以使用以下语句：

    LOAD DATA LOCAL INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(uniqName, uniqCity, uniqComments)
    回复于
  • 0

    将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
\0      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

    访问量：http://www.webslessons.com/2014/02/import-csv-files-using-php-and-mysql.html

    回复于
  • 145

    这是一个简单的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);

?>

    它将基于第一行创建一个表，并将剩余的行导入其中 . 这是命令行语法：

    php csv_import.php csv_file.csv table_name
    回复于
  • 4

    如果你有能力安装phpadmin有一个导入部分，你可以在其中导入csv文件到你的数据库甚至有一个复选框，设置 Headers 到文件的第一行包含表列名称（如果未选中，则第一行将成为数据的一部分

    回复于
  • 2

    首先在数据库中创建一个表，其中包含csv文件中相同数量的列 .

    然后使用以下查询

    LOAD DATA INFILE 'D:/Projects/testImport.csv' INTO TABLE cardinfo
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
    回复于
  • 0

    要从文本文件或csv文件加载数据，命令是

    load data local infile 'file-name.csv'
into table table-name
fields terminated by '' enclosed by '' lines terminated by '\n' (column-name);

    在上面的命令中，在我的情况下，只有一列要加载，所以没有“终止”和“括号”所以我保持空，否则程序员可以输入分隔字符 . 例如 . ，（逗号）或“或;或任何事物 .

    适用于使用mysql 5及以上版本的用户

    在将文件加载到mysql之前，必须确保在下面添加拖曳线 etc/mysql/my.cnf

    编辑my.cnf命令是

    sudo vi /etc/mysql/my.cnf

    [mysqld]  
local-infile

[mysql]  
local-infile
    回复于
  • 3

    如果你启动mysql为“mysql -u -p --local-infile”，它将正常工作

    回复于
  • -2

    我摔跤了一段时间 . 问题不在于如何加载数据，而在于如何构造表来保存数据 . 在导入数据之前，必须生成DDL语句来构建表 .

    如果表具有大量列，则特别困难 .

    这是一个（几乎）完成工作的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'

    它要解决的问题是最终的字段名称和数据类型声明以逗号结束，而mySQL解析器不会容忍它 .

    当然它也存在问题，它为每个字段使用TEXT数据类型 . 如果表有几百列，那么VARCHAR（64）将使表太大 .

    这似乎也打破了mySQL的最大列数 . 那是时候转移到Hive或HBase了，如果你能的话 .

    回复于
  • 3

    我写了一些代码来做到这一点，我将提出几个片段：

    $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

    然后获取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);

    然后将您的查询发送到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());
    回复于
  • 23

    这是我在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()

    关键点

    • 为INSERT使用预准备语句

    • 在 'rb' 二进制文件中打开file.csv

    • 某些CSV文件可能需要tweaking，例如 skipinitialspace 选项 .

    • 如果 255 isn 't wide enough you' ll在INSERT上出错并且必须重新开始 .

    • 调整列类型，例如 ALTER TABLE t MODIFYAmountDECIMAL(11,2);

    • 添加primary key，例如 ALTER TABLE t ADDidINT PRIMARY KEY AUTO_INCREMENT;

    回复于
  • 4

    正如其他人所提到的，本地infile的加载数据工作得很好 . 我尝试了Hawkee发布的php脚本，但对我没有用 . 而不是调试它，这是我做的：

    1）将CSV文件的 Headers 行复制/粘贴到txt文件中，并使用emacs进行编辑 . 在每个字段之间添加逗号和CR，以便在每个字段上添加逗号和CR .
    2）将该文件另存为FieldList.txt
    3）编辑文件以包含defns for每个字段（大多数是varchar，但很多是int（x） . 将create table tablename（到文件的开头）添加到文件的末尾 . 保存为CreateTable.sql
    4）使用Createtable.sql文件中的输入启动mysql客户端以创建表
    5）启动mysql客户端，复制/粘贴大部分'LOAD DATA INFILE'命令，替换我的表名和csv文件名 . 粘贴在FieldList.txt文件中 . 在字段列表中粘贴前一定要包含'IGNORE 1 LINES'

    听起来很多工作，但很容易与emacs .....

    回复于
  • 2

    我有谷歌搜索很多方法将csv导入mysql，包括“加载数据infile”，使用mysql工作台等 .

    当我使用mysql workbench导入按钮时，首先需要自己创建空表，自己设置每个列类型 . 注意：您必须在末尾添加ID列作为主键，而不是null和auto_increment，否则，导入按钮将在以后不可见 . 但是，当我开始加载CSV文件时，没有任何加载，似乎是一个bug . 我放弃 .

    幸运的是，到目前为止我发现的最好的简单方法是使用Oracle的mysql for excel . 你可以从这里下载mysql for excel

    这就是你要做的事情：在excel中打开csv文件，在Data选项卡中，找到mysql for excel按钮

    选择所有数据，单击导出到mysql . 注意将ID列设置为主键 .

    完成后，转到mysql工作台来改变表格，例如货币类型应为十进制（19,4），用于大量小数（10,2）以供常规使用 . 其他字段类型可以设置为varchar（255） .

    回复于

相关问题