使用 CSV 存储引擎直接从 CSV 文件创建 mysql 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9998596/
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
Create mysql table directly from CSV file using the CSV Storage engine?
提问by Jonathan dos Santos
I just learned that MySQL has a native CSV storage enginewhich 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
where users.csvis uploaded by the user?
users.csv用户上传到哪里了？
采纳答案by Shiplu Mokaddim
This is not possible. To create a table you need a table schema. What you have is a data file. A schema cannot be created with it.
这不可能。要创建表，您需要一个表架构。您拥有的是一个数据文件。不能用它创建模式。
What you can do is check if your file has a header row, and, in that case, you can manually create a table using that header row.
您可以做的是检查您的文件是否有标题行，在这种情况下，您可以使用该标题行手动创建一个表格。
However, there is a way to generate a create table statement using a batch file as described by John Swapceinski in the comment section of the 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 ( "
head -1 | sed -e 's/,/ varchar(255),\n/g'
echo " varchar(255) );"
回答by prototype
I just discovered csvkit, which is a set of Unix command-line tools for CSV files. I installed it on my Mac with pip install csvkit. The command was:
我刚刚发现了csvkit，它是一组用于 CSV 文件的 Unix 命令行工具。我将它安装在我的 Mac 上pip install 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.
您也可以提供一个数据库连接字符串，它可以直接加载表。
回答by Jan Damek
I'm recommended use MySQL Workbench where is import data. Workbench allows the user to create a new table from a file in CSV or JSON format. It handles table schema and data import in just a few clicks through the wizard.
我建议使用 MySQL Workbench，其中导入数据。Workbench 允许用户从 CSV 或 JSON 格式的文件创建新表。它通过向导只需单击几下即可处理表架构和数据导入。
In MySQL Workbench, use the context menu on table list and click Table Data Import Wizard.
在 MySQL Workbench 中，使用表列表上的上下文菜单并单击Table Data Import Wizard。
More from the MySQL Workbench 6.5.1 Table Data Export and Import Wizarddocumentation. Download MySQL Workbench here.
更多来自 MySQL Workbench 6.5.1 表数据导出和导入向导文档。在此处下载 MySQL Workbench。
回答by Bill Isaacs
There is an easier way if you are using phpMyAdmin as your MySQL front end:
如果您使用 phpMyAdmin 作为 MySQL 前端，则有一种更简单的方法：
- Create a database with the default settings.
- Select the database.
- Click "Import" at the top of the screen.
- Select "CSV" under "Format".
- Choose the options appropriate to your CSV file, open the CSV file in a text editor and reference it to get the "appropriate" options.
- 使用默认设置创建数据库。
- 选择数据库。
- 单击屏幕顶部的“导入”。
- 在“格式”下选择“CSV”。
- 选择适合您的 CSV 文件的选项，在文本编辑器中打开 CSV 文件并引用它以获得“适当的”选项。
If you have problems, no problem, simply drop the database and try again.
如果您有问题，没问题，只需删除数据库并重试。
回答by pal4life
"Convert CSV to SQL" helped me. Add your CSV file and you are good to go.
“将 CSV 转换为 SQL”帮助了我。添加您的 CSV 文件，一切顺利。
回答by Tim Green
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 导入选项，它工作得很好并且很灵活——您可以在导入时更改列名和字段类型。选择新表，否则初始对话会显得有些令人沮丧。
Sequel Pro- database management application for working with MySQL databases.
Sequel Pro- 用于处理 MySQL 数据库的数据库管理应用程序。
回答by rjanjic
If someone is looking for a PHP solution see "PHP_MySQL_wrapper":
如果有人正在寻找 PHP 解决方案，请参阅“ 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();
回答by Johann Horvat
I adopted the script from shiplu.mokadd.im to fit my needs. Whom it interests:
我采用了 shiplu.mokadd.im 中的脚本来满足我的需求。对谁感兴趣：
#!/bin/bash
if [ "$#" -lt 2 ]; then
if [ "$#" -lt 1 ]; then
echo "usage: C:\Temp>csv2ddl.exe mysql test.csv test.sql
[path to csv file] <table name> > [sql filename]"
exit 1
fi
TABLENAME=
else
TABLENAME=
fi
echo "CREATE TABLE $TABLENAME ( "
FIRSTLINE=$(head -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));"
回答by Jacob
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 advanced doublequote comma test.csv test.sql
Or
或者
##代码##回答by fancyPants
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 下工作。
