blocks|key|604622|text|我刚刚发现了csvkit，它是一组用于CSV文件的Unix命令行工具。我用pip+install+csvkit把它安装在我的苹果机上。命令是：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|604623|csvsql+--dialect+mysql+--snifflimit+100000+bigdatafile.csv+>+maketable.sql|code-block|syntax|javascript|604624|您也可以提供一个DB连接字符串，它可以直接加载表。|604625|entityMap|0|LINK|mutability|MUTABLE|url|http://csvkit.readthedocs.org/en/latest/scripts/csvsql.html^0|11|I|6|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

I just discovered <a href="http://csvkit.readthedocs.org/en/latest/scripts/csvsql.html" rel="noreferrer">csvkit</a>, which is a set of Unix command-line tools for CSV files. I installed it on my Mac with <code>pip install csvkit</code>. The command was:

<pre class="lang-sh prettyprint-override"><code>csvsql --dialect mysql --snifflimit 100000 bigdatafile.csv &gt; maketable.sql
</code></pre>

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

blocks|key|605079|text|我推荐使用导入数据的MySQL工作台。工作台允许用户从CSV或JSON格式的文件创建新表。它只需在向导中单击几下即可处理表架构和数据导入。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|605080|在MySQL工作台中，使用表列表上的上下文菜单并单击Table+Data+Import+Wizard。|offset|length|style|CODE|605081|​|605082|📷|atomic|605083|605084|有关MySQL工作台6.5.1+Table+Data+Export+and+Import+Wizard文档的更多信息。Download+MySQL+Workbench+here。|605085|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/7ed11aa4882665af2d344e3ee455a12c.png|imageAlt|1|LINK|MUTABLE|url|https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html|2|https://dev.mysql.com/downloads/workbench/^0|0|Q|O|0|0|0|1|0|0|0|A|15|1|1N|T|2|0^^$0|@$1|2|3|4|5|6|7|15|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|16|8|@$D|17|E|18|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|19|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1A|8|@]|9|@$D|1B|E|1C|1|1D]]|A|$]]|$1|M|3|I|5|6|7|1E|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1F|8|@]|9|@$D|1G|E|1H|1|1I]|$D|1J|E|1K|1|1L]]|A|$]]|$1|P|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|W|X|-4]]|Y|$5|Z|T|10|A|$11|12]]|13|$5|Z|T|10|A|$11|14]]]]

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.

In MySQL Workbench, use the context menu on table list and click <code>Table Data Import Wizard</code>.

<img src="https://i.stack.imgur.com/yMlSa.png" alt="MySQL Workbench image">

More from the MySQL Workbench <a href="https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html" rel="noreferrer">6.5.1 Table Data Export and Import Wizard</a> documentation. <a href="https://dev.mysql.com/downloads/workbench/" rel="noreferrer">Download MySQL Workbench here</a>.

blocks|key|604696|text|"Convert+CSV+to+SQL“帮了我。添加您的CSV文件，您就可以开始了。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|604697|entityMap|0|LINK|mutability|MUTABLE|url|http://www.convertcsv.com/csv-to-sql.htm^0|1|I|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

"<a href="http://www.convertcsv.com/csv-to-sql.htm" rel="nofollow noreferrer">Convert CSV to SQL</a>" helped me. Add your CSV file and you are good to go.

blocks|key|388896|text|除了提到的其他解决方案之外，Mac用户可能想要注意到，SQL+Pro有一个CSV导入选项，它工作得相当好，而且很灵活-您可以在导入时更改列名和字段类型。选择新表，否则初始对话可能会显得有些令人沮丧。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|388897|Sequel+Pro+-用于处理MySQL数据库的数据库管理应用程序。|offset|length|388898|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sequelpro.com/^0|0|0|A|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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.

<a href="http://www.sequelpro.com" rel="nofollow noreferrer">Sequel Pro</a> - database management application for working with MySQL databases.

blocks|key|604685|text|如果有人正在寻找PHP解决方案，请参阅"PHP_MySQL_wrapper"：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|604686|$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,+"%25d/%25m/%25Y")',+'SOME_INCREMENT'+=>+'@SOME_INCREMENT+%2B+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();|code-block|syntax|javascript|604687|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/uzi88/PHP_MySQL_wrapper^0|K|H|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

If someone is looking for a PHP solution see "<a href="https://github.com/uzi88/PHP_MySQL_wrapper" rel="nofollow noreferrer">PHP_MySQL_wrapper</a>":

<pre><code>$db = new MySQL_wrapper(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db-&gt;connect(); 

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

// this sample generates column names 
$db-&gt;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' =&gt; 'STR_TO_DATE(@SOME_DATE, "%d/%m/%Y")', 'SOME_INCREMENT' =&gt; '@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-&gt;close();
</code></pre>

blocks|key|388971|text|为了满足我的需要，我采用了shiplu.mokadd.im中的脚本。感兴趣的人：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|388972|#!/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+%7C+tr+'[:lower:]'+'[:upper:]')
#+remove+spaces
FIRSTLINE=$(echo+$FIRSTLINE+%7C+sed+-e+'s/+/_/g')
#+add+tab+char+to+the+beginning+of+line
FIRSTLINE=$(echo+"\t$FIRSTLINE")
#+add+tabs+and+newline+characters
FIRSTLINE=$(echo+$FIRSTLINE+%7C+sed+-e+'s/,/,\\n\\t/g')
#+add+VARCHAR
FIRSTLINE=$(echo+$FIRSTLINE+%7C+sed+-e+'s/,/+VARCHAR(255),/g')
#+print+out+result
echo+-e+$FIRSTLINE"+VARCHAR(255));"|code-block|syntax|javascript|388973|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>#!/bin/bash
if [ "$#" -lt 2 ]; then
 if [ "$#" -lt 1 ]; then 
 echo "usage: $0 [path to csv file] &lt;table name&gt; &gt; [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));"
</code></pre>

blocks|key|389131|text|您可以使用此bash脚本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|389132|convert.sh|offset|length|389133|然后运行|389134|./convert.sh+-f+example/mycsvfile.csv|code-block|syntax|javascript|389135|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/lukaneco/CSV-to-MySql/blob/master/convert.sh^0|0|0|A|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@$D|W|E|X|1|Y]]|A|$]]|$1|F|3|G|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|10|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

you can use this bash script
<a href="https://github.com/lukaneco/CSV-to-MySql/blob/master/convert.sh" rel="nofollow noreferrer">convert.sh</a>
and run
<pre><code>./convert.sh -f example/mycsvfile.csv
</code></pre>

blocks|key|388484|text|这是不可能的，但是您可以覆盖现有的表文件。但请确保文件中的行尾是unix样式(仅以\n结尾)，而不是windows样式(以\r\n结尾)，无论您是否在windows下工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|388485|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|605122|text|如果您可以使用Python，那么Pandas对我来说工作得很好(对于我来说，csvsql永远挂起了)。类似于：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|605123|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)|code-block|syntax|javascript|605124|此外，这也没有解决“使用CSV引擎”的部分，这是问题的一部分，但也可能对我有用。|605125|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre class="lang-py prettyprint-override"><code>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)
</code></pre>

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

blocks|key|388678|text|excel插件的MySQL可以帮助你。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|388679|http://dev.mysql.com/doc/refman/5.6/en/mysql-for-excel.html|offset|length|388680|在excel中打开CSV文件。您可以使用此插件将excel数据导出到远程或本地mysql服务器的新表中。它将分析您的数据(前100到1000行)并创建相应的表模式。|388681|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1N|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

MySQL for excel plugin can help you.

<a href="http://dev.mysql.com/doc/refman/5.6/en/mysql-for-excel.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.6/en/mysql-for-excel.html</a>

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.

I just learned that MySQL has a native <a href="http://dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html" rel="noreferrer">CSV storage engine</a> which stores data in a Comma-Separated-Value file per table.

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

<pre><code>CREATE TABLE USERS &lt; PATH/USERS.CSV
</code></pre>

where <code>users.csv</code> is uploaded by the user?

Create mysql table directly from CSV file using the CSV Storage engine?

5位重磅专家聚焦 AI 时代开发者核心成长需求，带你深入解锁全栈进化、人机协作关键路径！点击报名到场：<a href="https://cloud.tencent.com/developer/salon/salon-2403/signup" target="_blank">https://cloud.tencent.com/developer/salon/salon-2403/signup</a> 
 <img src="https://qcloudimg.tencent-cloud.cn/raw/487c82639d2338df3ef66dfb1450601a.png"/>

腾讯技术创作特训营「年度论坛」正在报名中！

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我刚刚了解到MySQL有一个本机，它将数据存储在每个表的逗号分隔值文件中。是否可以直接从上载的CSV文件创建表，例如：CREATE TABLE USERS < PATH/USERS.CSV用户上传users.csv的位置？

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

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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