blocks|key|3318401|text|要从SQL文件导入，请使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3318402|sqlite>+.read+<filename>|code-block|syntax|javascript|3318403|要从CSV文件导入，您需要指定文件类型和目标表：|3318404|sqlite>+.mode+csv+<table>
sqlite>+.import+<filename>+<table>|3318405|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

To import from an SQL file use the following:

<pre><code>sqlite&gt; .read &lt;filename&gt;
</code></pre>

To import from a CSV file you will need to specify the file type and destination table:

<pre><code>sqlite&gt; .mode csv &lt;table&gt;
sqlite&gt; .import &lt;filename&gt; &lt;table&gt;
</code></pre>

blocks|key|337882|text|尝试在命令中执行此操作，如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|337883|cat+dump.sql+%7C+sqlite3+database.db|code-block|syntax|javascript|337884|这显然只适用于dump.sql中的SQL语句。我不确定如何导入CSV。|337885|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|$]]

Try doing it from the command like:

<pre><code>cat dump.sql | sqlite3 database.db
</code></pre>

This will obviously only work with SQL statements in dump.sql. I'm not sure how to import a CSV.

blocks|key|341395|text|要使用SQLite+DB从头开始将CSV导入到表中，请执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|341396|341397|从网站获取SQLite。|unordered-list-item|341398|在命令提示符下运行sqlite3+<your_db_file_name>+*它将被创建为一个空文件。|offset|length|style|CODE|341399|在您的新数据库中创建一个新表。表必须与您的CSV字段匹配才能导入。通过SQL命令CREATE+TABLE+<table_Name>+(<field_name1>+<Type>,+<field_name2>+<type>);|341400|执行|341401|操作：|341402|341403|一旦您创建了表，并且列与文件中的数据相匹配，那么您可以执行上述操作...|341404|.mode+csv+<table_name>
.import+<filename>+<table_name>|code-block|syntax|javascript|341405|entityMap^0|0|0|0|9|R|0|14|1Z|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|13|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|14|8|@$H|15|I|16|J|K]]|9|@]|A|$]]|$1|L|3|M|5|E|7|17|8|@$H|18|I|19|J|K]]|9|@]|A|$]]|$1|N|3|O|5|6|7|1A|8|@]|9|@]|A|$]]|$1|P|3|Q|5|E|7|1B|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1D|8|@]|9|@]|A|$]]|$1|U|3|V|5|W|7|1E|8|@]|9|@]|A|$X|Y]]|$1|Z|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|10|$]]

To go from SCRATCH with SQLite DB to importing the CSV into a table:

<ul>
<li>Get SQLite from the website.</li>
<li>At a command prompt run <code>sqlite3 &lt;your_db_file_name&gt;</code> *It will be created as an empty file.</li>
<li>Make a new table in your new database. The table must match your CSV fields for import.</li>
<li>You do this by the SQL command: <code>CREATE TABLE &lt;table_Name&gt; (&lt;field_name1&gt; &lt;Type&gt;, &lt;field_name2&gt; &lt;type&gt;);</code></li>
</ul>

Once you have the table created and the columns match your data from the file then you can do the above...

<pre><code>.mode csv &lt;table_name&gt;
.import &lt;filename&gt; &lt;table_name&gt;
</code></pre>

blocks|key|3105760|text|csv+.import命令不适用于普通的csv数据，因为它将任何逗号视为分隔符，即使在带引号的字符串中也是如此。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3105761|这包括尝试重新导入由shell创建的csv文件：|3105762|Create+table+T+(F1+integer,+F2+varchar);
Insert+into+T+values+(1,+'Hey!');
Insert+into+T+values+(2,+'Hey,+You!');

.mode+csv
.output+test.csv
select+*+from+T;

Contents+of+test.csv:
1,Hey!
2,"Hey,+You!"

delete+from+T;

.import+test.csv+T
Error:+test.csv+line+2:+expected+2+columns+of+data+but+found+3|code-block|syntax|javascript|3105763|似乎我们必须将csv转换为一组Insert语句，或者使用不同的分隔符。|3105764|在SuperUser，我看到了使用LogParser处理csv文件的建议，我将对此进行调查。|3105765|entityMap^0|0|0|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|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

The sqlite3 .import command won't work for ordinary csv data because it treats any comma as a delimiter even in a quoted string. 

This includes trying to re-import a csv file that was created by the shell:

<pre><code>Create table T (F1 integer, F2 varchar);
Insert into T values (1, 'Hey!');
Insert into T values (2, 'Hey, You!');

.mode csv
.output test.csv
select * from T;

Contents of test.csv:
1,Hey!
2,"Hey, You!"

delete from T;

.import test.csv T
Error: test.csv line 2: expected 2 columns of data but found 3
</code></pre>

It seems we must transform the csv into a list of Insert statements, or perhaps a different delimiter will work.

Over at SuperUser I saw a suggestion to use LogParser to deal with csv files, I'm going to look into that.

blocks|key|336546|text|如果您乐于使用(+python+)脚本，那么在https://github.com/rgrp/csv2sqlite上有一个python脚本可以自动执行此操作|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|336547|这将为您自动创建表，并为您执行一些基本的类型猜测和数据转换(例如，它将计算出某个值是一个数字，并将列类型设置为"real")。|336548|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/rgrp/csv2sqlite^0|N|Y|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

If you are happy to use a (python) script then there is a python script that automates this at: <a href="https://github.com/rgrp/csv2sqlite">https://github.com/rgrp/csv2sqlite</a>

This will auto-create the table for you as well as do some basic type-guessing and data casting for you (so e.g. it will work out something is a number and set the column type to "real").

blocks|key|337972|text|请记住，SQLite的默认分隔符是管道"%7C“|type|unstyled|depth|inlineStyleRanges|entityRanges|data|337973|sqlite>+.separator+";"

sqlite>+.import+path/filename.txt+tablename+|code-block|syntax|javascript|337974|http://sqlite.awardspace.info/syntax/sqlitepg01.htm#sqlite010|offset|length|337975|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|1P|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|T|8|@]|9|@$I|U|J|V|1|W]]|A|$]]|$1|K|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|H]]]]

Remember that the default delimiter for SQLite is the pipe "|"

<pre><code>sqlite&gt; .separator ";"

sqlite&gt; .import path/filename.txt tablename 
</code></pre>

<a href="http://sqlite.awardspace.info/syntax/sqlitepg01.htm#sqlite010" rel="noreferrer">http://sqlite.awardspace.info/syntax/sqlitepg01.htm#sqlite010</a>

blocks|key|3105803|text|查看termsql。https://gitorious.org/termsql+https://gitorious.org/termsql/pages/Home|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3105804|它在命令行上将文本转换为SQL。(CSV只是文本)|3105805|示例：|3105806|cat+textfile+%7C+termsql+-o+sqlite.db|code-block|syntax|javascript|3105807|默认情况下，分隔符是空格，所以要使其与使用逗号的CSV一起工作，您可以这样做：|3105808|cat+textfile+%7C+termsql+-d+','+-o+sqlite.db|3105809|或者，您可以执行以下操作：|3105810|termsql+-i+textfile+-d+','+-o+sqlite.db|3105811|默认情况下，它将生成列名"COL0"，"COL1"，如果您希望它使用第一行作为列名，请执行以下操作：|3105812|termsql+-i+textfile+-d+','+-1+-o+sqlite.db|3105813|如果要设置自定义列名，请执行以下操作：|3105814|termsql+-i+textfile+-d+','+-c+'id,name,age,color'+-o+sqlite.db|3105815|entityMap|0|LINK|mutability|MUTABLE|url|https://gitorious.org/termsql|1|https://gitorious.org/termsql/pages/Home^0|A|T|0|14|14|1|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@]|9|@$A|1D|B|1E|1|1F]|$A|1G|B|1H|1|1I]]|C|$]]|$1|D|3|E|5|6|7|1J|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|1K|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|1L|8|@]|9|@]|C|$K|L]]|$1|M|3|N|5|6|7|1M|8|@]|9|@]|C|$]]|$1|O|3|P|5|J|7|1N|8|@]|9|@]|C|$K|L]]|$1|Q|3|R|5|6|7|1O|8|@]|9|@]|C|$]]|$1|S|3|T|5|J|7|1P|8|@]|9|@]|C|$K|L]]|$1|U|3|V|5|6|7|1Q|8|@]|9|@]|C|$]]|$1|W|3|X|5|J|7|1R|8|@]|9|@]|C|$K|L]]|$1|Y|3|Z|5|6|7|1S|8|@]|9|@]|C|$]]|$1|10|3|11|5|J|7|1T|8|@]|9|@]|C|$K|L]]|$1|12|3|-4|5|6|7|1U|8|@]|9|@]|C|$]]]|13|$14|$5|15|16|17|C|$18|19]]|1A|$5|15|16|17|C|$18|1B]]]]

Check out termsql. <a href="https://gitorious.org/termsql" rel="nofollow">https://gitorious.org/termsql</a> <a href="https://gitorious.org/termsql/pages/Home" rel="nofollow">https://gitorious.org/termsql/pages/Home</a>

It converts text to SQL on the command line. (CSV is just text)

Example:

<pre><code>cat textfile | termsql -o sqlite.db
</code></pre>

By default the delimiter is whitespace, so to make it work with CSV that is using commata, you'd do it like this:

<pre><code>cat textfile | termsql -d ',' -o sqlite.db
</code></pre>

alternatively you can do this:

<pre><code>termsql -i textfile -d ',' -o sqlite.db
</code></pre>

By default it will generate column names "COL0", "COL1", if you want it to use the first row for the columns names you do this:

<pre><code>termsql -i textfile -d ',' -1 -o sqlite.db
</code></pre>

If you want to set custom column names you do this:

<pre><code>termsql -i textfile -d ',' -c 'id,name,age,color' -o sqlite.db
</code></pre>

blocks|key|3318536|text|SQLite非常灵活，因为它还允许在SQL语法中使用特定于SQLite的dot+commands+(尽管它们是由CLI解释的)。这意味着你可以做这样的事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3318537|创建一个sms表，如下所示：|style|CODE|3318538|#+sqlite3+mycool.db+'.schema+sms'
CREATE+TABLE+sms+(_id+integer+primary+key+autoincrement,+Address+VARCHAR,+Display+VARCHAR,+Class+VARCHAR,+ServiceCtr+VARCHAR,+Message+VARCHAR,+Timestamp+TIMESTAMP+NOT+NULL+DEFAULT+current_timestamp);|code-block|syntax|javascript|3318539|然后是两个文件：|3318540|#+echo+"1,ADREZZ,DizzPlay,CLAZZ,SMSC,DaTestMessage,2015-01-24+21:00:00">test.csv

#+cat+test.sql
.mode+csv
.header+on
.import+test.csv+sms|3318541|要使用SQL文件测试CSV文件的导入，请运行：|3318542|#+sqlite3+-csv+-header+mycool.db+'.read+test.sql'|3318543|总而言之，这意味着您可以在RDB中使用.import语句，就像在任何其他SQLite中一样，比如使用MySQL和LOAD+DATA+INFILE等。|3318544|entityMap|0|LINK|mutability|MUTABLE|url|https://www.sqlite.org/cli.html^0|10|C|0|0|4|3|0|0|0|0|0|0|J|7|1K|G|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|D|3|E|5|6|7|18|8|@$A|19|B|1A|F|G]]|9|@]|C|$]]|$1|H|3|I|5|J|7|1B|8|@]|9|@]|C|$K|L]]|$1|M|3|N|5|6|7|1C|8|@]|9|@]|C|$]]|$1|O|3|P|5|J|7|1D|8|@]|9|@]|C|$K|L]]|$1|Q|3|R|5|6|7|1E|8|@]|9|@]|C|$]]|$1|S|3|T|5|J|7|1F|8|@]|9|@]|C|$K|L]]|$1|U|3|V|5|6|7|1G|8|@$A|1H|B|1I|F|G]|$A|1J|B|1K|F|G]]|9|@]|C|$]]|$1|W|3|-4|5|6|7|1L|8|@]|9|@]|C|$]]]|X|$Y|$5|Z|10|11|C|$12|13]]]]

SQLite is extremely flexible as it also allows the SQLite specific <a href="https://www.sqlite.org/cli.html" rel="nofollow">dot commands</a> in the SQL syntax, (although they are interpreted by CLI.) This means that you can do things like this.

Create a <code>sms</code> table like this:

<pre><code># sqlite3 mycool.db '.schema sms'
CREATE TABLE sms (_id integer primary key autoincrement, Address VARCHAR, Display VARCHAR, Class VARCHAR, ServiceCtr VARCHAR, Message VARCHAR, Timestamp TIMESTAMP NOT NULL DEFAULT current_timestamp);
</code></pre>

Then two files: 

<pre><code># echo "1,ADREZZ,DizzPlay,CLAZZ,SMSC,DaTestMessage,2015-01-24 21:00:00"&gt;test.csv

# cat test.sql
.mode csv
.header on
.import test.csv sms
</code></pre>

To test the import of the CSV file using the SQL file, run:

<pre><code># sqlite3 -csv -header mycool.db '.read test.sql'
</code></pre>

In conclusion, this means that you can use the <code>.import</code> statement in SQLite SQL, just as you can do in any other RDB, like MySQL with <code>LOAD DATA INFILE</code> etc. However, this is not recommended.

blocks|key|3318561|text|如果您在windows中使用它，请确保将数据库的路径添加到"“中，并在路径中使用双斜杠\，以确保windows能够理解它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3318562|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|$]]

if you are using it in windows, be sure to add the path to the db in "" and also to use double slash \ in the path to make sure windows understands it.

blocks|key|3318592|text|以下是插入到标识列中的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3318593|CREATE+TABLE+my_table+(id+INTEGER+PRIMARY+KEY+AUTOINCREMENT,+name+COLLATE+NOCASE);
CREATE+TABLE+temp_table+(name+COLLATE+NOCASE);

.import+predefined/myfile.txt+temp_table+
insert+into+my_table+(name)+select+name+from+temp_table;|code-block|syntax|javascript|3318594|myfile.txt是C：\代码\db\预定义\中的文件|3318595|代码位于C：\+data.db+\db\|3318596|myfile.txt包含由换行符分隔的字符串。|3318597|如果想要添加更多列，使用竖线字符分隔它们会更容易，这是默认设置。|3318598|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

This is how you can insert into an identity column:

<pre><code>CREATE TABLE my_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name COLLATE NOCASE);
CREATE TABLE temp_table (name COLLATE NOCASE);

.import predefined/myfile.txt temp_table 
insert into my_table (name) select name from temp_table;
</code></pre>

myfile.txt is a file in C:\code\db\predefined\

data.db is in C:\code\db\

myfile.txt contains strings separated by newline character.

If you want to add more columns, it's easier to separate them using the pipe character, which is the default.

blocks|key|3318546|text|用phpLiteAdmin把你的csv或sql导入sqlite，这是非常好的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3318547|entityMap|0|LINK|mutability|MUTABLE|url|https://www.phpliteadmin.org/^0|1|C|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]]]]

Import your csv or sql to sqlite with <a href="https://www.phpliteadmin.org/" rel="nofollow">phpLiteAdmin</a>, it is excellent.

I need to dump a .sql or .csv file into SQLite (I'm using SQLite3 API). I've only found documentation for importing/loading tables, not entire databases. Right now, when I type:

<pre><code>sqlite3prompt&gt; .import FILENAME TABLE 
</code></pre>

I get a syntax error, since it's expecting a table and not an entire DB.

How to import load a .sql or .csv file into SQLite?

我需要将.sql或.csv文件转储到SQLite (我使用的是SQLite3 API)。我只找到了有关导入/加载表的文档，而没有找到整个数据库。现在，当我输入：sqlite3prompt> .import FILENAME TABLE 我得到一个语法错误，因为它需要一个表，而不是整个DB。

问如何将.sql或.csv文件导入到SQLite中？
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将.sql或.csv文件导入到SQLite中？EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将.sql或.csv文件导入到SQLite中？
EN