使用CSV存储引擎直接从CSV文件创建mysql表?

I just learned that Mysql has a Native CSV storage engine which stores data in a Comma Separated Value file per table.

我刚刚了解到Mysql有一个本地的CSV存储引擎，它将数据存储在每个表的逗号分隔值文件中。

Is it possible to create a table directly from a uploaded CSV file, something like:

是否可以直接从上传的CSV文件中创建一个表，比如:

CREATE TABLE USERS < PATH/USERS.CSV

创建表用户< PATH/ user . csv。

where users.csv is uploaded by the user?

用户的地方。csv是用户上传的?

12 个解决方案

#1

This is not possible. To create table you need a table schema. What you have is a data file. Schema cannot be created with it. What you can do is check if your file has header row. In that case you can create table using that header row but manually.

这是不可能的。要创建表，需要一个表模式。您拥有的是一个数据文件。不能用它创建模式。您可以做的是检查文件是否有头行。在这种情况下，您可以使用头行创建表，但需要手动创建。

However there is a way to generate create table statement by a batch file as described by John Swapceinski in the comment section of MySQL manual.

但是，有一种方法可以通过批处理文件生成create table语句，如John Swapceinski在MySQL手册的注释部分中所描述的那样。

Posted by John Swapceinski on September 5 2011 5:33am.
Create a table using the .csv file's header:

John Swapceinski在2011年9月5日早上5:33发布。使用.csv文件的标题创建一个表:

#!/bin/sh
# pass in the file name as an argument: ./mktable filename.csv
echo "create table $1 ( "
head -1 $1 | sed -e 's/,/ varchar(255),\n/g'
echo " varchar(255) );"

#2

Just discovered csvkit, which is a set of unix command line tools for CSV files. Installed on my Mac with pip install csvkit and the command was:

刚刚发现csvkit，它是一组用于CSV文件的unix命令行工具。在我的Mac上安装了pip安装csvkit，命令是:

 csvsql --dialect mysql --snifflimit 100000 bigdatafile.csv > maketable.sql

You can alternatively provide a db connection string and it can load the table directly.

您也可以提供一个db连接字符串，它可以直接加载表。

#3

I know that this is a little bit stale as a topic, but there is an easier way -- IF -- you are using phpmyadmin as your mysql front end.

我知道这是一个有点陈腐的话题，但是有一个更简单的方法——如果——你使用phpmyadmin作为你的mysql前端。

1)Create a database with just default settings.

1)使用默认设置创建一个数据库。

2)Select the database.

2)选择数据库。

3)Click "Import" at the top of the screen.

3)点击屏幕顶部的“导入”。

4)Select CSV under "Format".

4)选择“格式”下CSV。

5)Choose the options appropriate to your csv file (open the csv file in a text editor and reference it to get 'appropriate' options).

5)选择适合您的csv文件的选项(在文本编辑器中打开csv文件并引用它以获得“适当”选项)。

If you muck it up, no problem, simply drop the database and try again.

如果您搞砸了，没问题，只需删除数据库并再次尝试。

#4

This is what helped me. Just add your csv file and you are good to go.

这对我有帮助。只要添加csv文件就可以了。

http://www.convertcsv.com/csv-to-sql.htm

#5

In addition to the other solutions mentioned Mac users may want to note that SQL Pro has a CSV import option which works fairly well and is flexible - you can change column names, and field types on import. Choose new table otherwise the initial dialogue can appear somewhat disheartening.

除了提到的其他解决方案之外，Mac用户可能还想注意SQL Pro有一个CSV导入选项，它工作得很好，而且很灵活——您可以在导入时更改列名和字段类型。选择新的表格，否则最初的对话可能有些令人沮丧。

SQL Pro: http://www.sequelpro.com Sequel Pro - database management application for working with MySQL databases

SQL Pro: http://www.sequelpro.com Sequel专业数据库管理应用程序，用于使用MySQL数据库

#6

I'm recommended use MySQL Workbench where is import data. There is also possible make new table from CSV or JSON.

建议使用MySQL Workbench进行导入数据。还可以从CSV或JSON中创建新表。

In MySQL Workbench application use context menu on table list and use

在MySQL Workbench应用程序中，使用表列表中的上下文菜单并使用

Table Data Import Wizard

表数据导入向导

MySQL Workbench image

MySQL工作台形象

Link for MySQL Workbench. https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html

链接MySQL工作台。https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html

#7

if someone is looking for php solutin:

如果有人在寻找php解决方案:

Package: https://github.com/uzi88/PHP_MySQL_wrapper

包:https://github.com/uzi88/PHP_MySQL_wrapper

$db = new MySQL_wrapper(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect(); 

// this sample gets column names from first row of file
//$db->createTableFromCSV('test_files/countrylist.csv', 'csv_to_table_test');

// this sample generates column names 
$db->createTableFromCSV('test_files/countrylist1.csv', 'csv_to_table_test_no_column_names', ',', '"', '\\', 0, array(), 'generate', '\r\n');

/** Create table from CSV file and imports CSV data to Table with possibility to update rows while import.
 * @param   string      $file           - CSV File path
 * @param   string      $table          - Table name
 * @param   string      $delimiter      - COLUMNS TERMINATED BY (Default: ',')
 * @param   string      $enclosure      - OPTIONALLY ENCLOSED BY (Default: '"')
 * @param   string      $escape         - ESCAPED BY (Default: '\')
 * @param   integer     $ignore         - Number of ignored rows (Default: 1)
 * @param   array       $update         - If row fields needed to be updated eg date format or increment (SQL format only @FIELD is variable with content of that field in CSV row) $update = array('SOME_DATE' => 'STR_TO_DATE(@SOME_DATE, "%d/%m/%Y")', 'SOME_INCREMENT' => '@SOME_INCREMENT + 1')
 * @param   string      $getColumnsFrom - Get Columns Names from (file or generate) - this is important if there is update while inserting (Default: file)
 * @param   string      $newLine        - New line delimiter (Default: \n)
 * @return  number of inserted rows or false
 */
// function createTableFromCSV($file, $table, $delimiter = ',', $enclosure = '"', $escape = '\\', $ignore = 1, $update = array(), $getColumnsFrom = 'file', $newLine = '\r\n')

$db->close();

#8

I adopted the script from shiplu.mokadd.im to fit my needs. Whom it interests:

我采用了shiplu.mokadd的脚本。我适合我的需要。他们的利益:

#!/bin/bash
if [ "$#" -lt 2 ]; then
    if [ "$#" -lt 1 ]; then 
        echo "usage: $0 [path to csv file] <table name> > [sql filename]"
        exit 1
    fi
    TABLENAME=$1
else
    TABLENAME=$2
fi
echo "CREATE TABLE $TABLENAME ( "
FIRSTLINE=$(head -1 $1)
# convert lowercase characters to uppercase
FIRSTLINE=$(echo $FIRSTLINE | tr '[:lower:]' '[:upper:]')
# remove spaces
FIRSTLINE=$(echo $FIRSTLINE | sed -e 's/ /_/g')
# add tab char to the beginning of line
FIRSTLINE=$(echo "\t$FIRSTLINE")
# add tabs and newline characters
FIRSTLINE=$(echo $FIRSTLINE | sed -e 's/,/,\\n\\t/g')
# add VARCHAR
FIRSTLINE=$(echo $FIRSTLINE | sed -e 's/,/ VARCHAR(255),/g')
# print out result
echo -e $FIRSTLINE" VARCHAR(255));"

#9

I have made a Windows command line tool that do just that.

我已经创建了一个Windows命令行工具。

You can download it here: http://commandline.dk/csv2ddl.htm

您可以在这里下载:http://commandline.dk/csv2ddl.htm

Usage:

用法:

C:\Temp>csv2ddl.exe mysql test.csv test.sql

或

C:\Temp>csv2ddl.exe mysql advanced doublequote comma test.csv test.sql

#10

MySQL for excel plugin can help you.

excel插件MySQL可以帮助您。

http://dev.mysql.com/doc/refman/5.6/en/mysql-for-excel.html

Open your CSV file in excel. You can use this plugin to export excel data into a new table of remote or local mysql server. It will analyze your data (top 100 to 1000 rows) and create a corresponding table schema.

在excel中打开CSV文件。可以使用此插件将excel数据导出到远程或本地mysql服务器的新表中。它将分析您的数据(前100到1000行)并创建相应的表模式。

#11

This is not possible, you can however overwrite an existing table file. But be sure, that the line endings in your file are unix style (ending only with \n), not windows style (ending with \r\n), whether you are working under windows or not.

这是不可能的，但是您可以重写现有的表文件。但是请确保文件的行尾是unix风格(仅以\n结尾)，而不是windows风格(以\r\n结尾)，不管您是否在windows下工作。

#12

If you're ok with using Python, Pandas worked great for me (csvsql hanged forever for my case). Something like:

如果你可以使用Python，熊猫对我来说很有用(csvsql永远挂在我的箱子上)。喜欢的东西:

from sqlalchemy import create_engine
import pandas as pd

df = pd.read_csv('/PATH/TO/FILE.csv')
# Optional, set your indexes to get Primary Keys
df = df.set_index(['COL A', 'COL B'])

engine = create_engine('mysql://user:pass@host/db', echo=False)

df.to_sql(table_name, dwh_engine, index=False)

Also this doesn't solve the "using CSV engine" part which was part of the question but might me useful as well.

同样，这并不能解决“使用CSV引擎”的问题，这也是问题的一部分，但我可能也会很有用。

#1