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:
我需要将.sql或.csv文件转储到SQLite中(我使用的是SQLite3 API)。我只找到了用于导入/加载表的文档，而不是整个数据库。现在，当我输入:
sqlite3prompt> .import FILENAME TABLE I get a syntax error, since it's expecting a table and not an entire DB.
我得到一个语法错误，因为它期望的是一个表而不是整个DB。
11 个解决方案
#1
152
To import from an SQL file use the following:
要从SQL文件导入，请使用以下命令:
sqlite> .read <filename>To import from a CSV file you will need to specify the file type and destination table:
要从CSV文件导入，您需要指定文件类型和目标表:
sqlite> .mode csv <table>sqlite> .import <filename> <table>#2
24
Try doing it from the command like:
尝试从以下命令执行:
cat dump.sql | sqlite3 database.dbThis will obviously only work with SQL statements in dump.sql. I'm not sure how to import a CSV.
这显然只适用于dump.sql中的SQL语句。我不知道如何导入CSV。
#3
21
To go from SCRATCH with SQLite DB to importing the CSV into a table:
使用SQLite DB从头开始，将CSV导入到表中:
- Get SQLite from the website.
- 从网站上获取SQLite。
- At a command prompt run
sqlite3 <your_db_file_name>*It will be created as an empty file. -
在命令提示符下运行sqlite3
*，它将被创建为一个空文件。 - Make a new table in your new database. The table must match your CSV fields for import.
- 在新数据库中创建一个新表。该表必须匹配用于导入的CSV字段。
- You do this by the SQL command:
CREATE TABLE <table_Name> (<field_name1> <Type>, <field_name2> <type>); -
您可以通过SQL命令来执行:创建表
( ， );
Once you have the table created and the columns match your data from the file then you can do the above...
一旦创建了表，列与文件中的数据匹配，那么您就可以执行上面的操作……
.mode csv <table_name>.import <filename> <table_name>#4
11
The sqlite3 .import command won't work for ordinary csv data because it treats any comma as a delimiter even in a quoted string.
导入命令不能用于普通的csv数据，因为它把任何逗号都当作分隔符，即使在引用的字符串中也是如此。
This includes trying to re-import a csv file that was created by the shell:
这包括尝试重新导入由shell创建的csv文件:
Create table T (F1 integer, F2 varchar);Insert into T values (1, 'Hey!');Insert into T values (2, 'Hey, You!');.mode csv.output test.csvselect * from T;Contents of test.csv:1,Hey!2,"Hey, You!"delete from T;.import test.csv TError: test.csv line 2: expected 2 columns of data but found 3It seems we must transform the csv into a list of Insert statements, or perhaps a different delimiter will work.
似乎我们必须将csv转换成插入语句的列表，或者使用不同的分隔符。
Over at SuperUser I saw a suggestion to use LogParser to deal with csv files, I'm going to look into that.
在SuperUser上，我看到了使用LogParser处理csv文件的建议，我将对此进行研究。
#5
10
If you are happy to use a (python) script then there is a python script that automates this at: https://github.com/rgrp/csv2sqlite
如果您喜欢使用(python)脚本，那么有一个python脚本可以在以下实现自动化:https://github.com/rgrp/csv2sqlite
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").
这将自动为您创建表，以及为您做一些基本的类型猜测和数据转换(例如，它将计算出一个数字，并将列类型设置为“real”)。
#6
6
Remember that the default delimiter for SQLite is the pipe "|"
记住，SQLite的默认分隔符是管道“|”
sqlite> .separator ";"sqlite> .import path/filename.txt tablename http://sqlite.awardspace.info/syntax/sqlitepg01.htm#sqlite010
http://sqlite.awardspace.info/syntax/sqlitepg01.htm sqlite010
#7
1
SQLite is extremely flexible as it also allows the SQLite specific dot commands in the SQL syntax, (although they are interpreted by CLI.) This means that you can do things like this.
SQLite非常灵活，因为它还允许SQL语法中使用特定于SQLite的点命令(尽管CLI对它们进行了解释)。这意味着你可以做这样的事情。
Create a sms table like this:
创建这样的sms表:
# 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);Then two files:
然后两个文件:
# 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 smsTo test the import of the CSV file using the SQL file, run:
要使用SQL文件测试CSV文件的导入，请运行:
# sqlite3 -csv -header mycool.db '.read test.sql'In conclusion, this means that you can use the .import statement in SQLite SQL, just as you can do in any other RDB, like MySQL with LOAD DATA INFILE etc. However, this is not recommended.
总之，这意味着您可以在SQLite SQL中使用.import语句，就像在任何其他RDB中使用。
#8
1
Check out termsql. https://gitorious.org/termsql https://gitorious.org/termsql/pages/Home
查看termsql。https://gitorious.org/termsql https://gitorious.org/termsql/pages/Home
It converts text to SQL on the command line. (CSV is just text)
它将文本转换为命令行上的SQL。(CSV是文本)
Example:
例子:
cat textfile | termsql -o sqlite.dbBy default the delimiter is whitespace, so to make it work with CSV that is using commata, you'd do it like this:
默认情况下，分隔符是空格，所以要让它与使用commata的CSV兼容，您应该这样做:
cat textfile | termsql -d ',' -o sqlite.dbalternatively you can do this:
你也可以这样做:
termsql -i textfile -d ',' -o sqlite.dbBy default it will generate column names "COL0", "COL1", if you want it to use the first row for the columns names you do this:
默认情况下，它将生成列名“COL0”、“COL1”，如果您希望它为列名使用第一行，您可以这样做:
termsql -i textfile -d ',' -1 -o sqlite.dbIf you want to set custom column names you do this:
如果您想设置自定义列名，您可以这样做:
termsql -i textfile -d ',' -c 'id,name,age,color' -o sqlite.db#9
1
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.
如果您在windows中使用它，请确保将路径添加到“”中的db中，并在路径中使用双斜杠\，以确保windows理解它。
#10
0
This is how you can insert into an identity column:
这就是如何插入标识列:
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;myfile.txt is a file in C:\code\db\predefined\
myfile。txt是一个C:\code\db\预定义\ \ \ \ \的文件
data.db is in C:\code\db\
数据。db是C:\ \ db \代码
myfile.txt contains strings separated by newline character.
myfile。txt包含由换行字符分隔的字符串。
If you want to add more columns, it's easier to separate them using the pipe character, which is the default.
如果您想要添加更多的列，那么使用管道字符(这是默认的)就可以更容易地将它们分开。
#11
-1
Import your csv or sql to sqlite with phpLiteAdmin, it is excellent.
用phpLiteAdmin将csv或sql导入到sqlite，非常棒。
#1
152
To import from an SQL file use the following:
要从SQL文件导入，请使用以下命令:
sqlite> .read <filename>To import from a CSV file you will need to specify the file type and destination table:
要从CSV文件导入，您需要指定文件类型和目标表:
sqlite> .mode csv <table>sqlite> .import <filename> <table>#2
24
Try doing it from the command like:
尝试从以下命令执行:
cat dump.sql | sqlite3 database.dbThis will obviously only work with SQL statements in dump.sql. I'm not sure how to import a CSV.
这显然只适用于dump.sql中的SQL语句。我不知道如何导入CSV。
#3
21
To go from SCRATCH with SQLite DB to importing the CSV into a table:
使用SQLite DB从头开始，将CSV导入到表中:
- Get SQLite from the website.
- 从网站上获取SQLite。
- At a command prompt run
sqlite3 <your_db_file_name>*It will be created as an empty file. -
在命令提示符下运行sqlite3
*，它将被创建为一个空文件。 - Make a new table in your new database. The table must match your CSV fields for import.
- 在新数据库中创建一个新表。该表必须匹配用于导入的CSV字段。
- You do this by the SQL command:
CREATE TABLE <table_Name> (<field_name1> <Type>, <field_name2> <type>); -
您可以通过SQL命令来执行:创建表
( ， );
Once you have the table created and the columns match your data from the file then you can do the above...
一旦创建了表，列与文件中的数据匹配，那么您就可以执行上面的操作……
.mode csv <table_name>.import <filename> <table_name>#4
11
The sqlite3 .import command won't work for ordinary csv data because it treats any comma as a delimiter even in a quoted string.
导入命令不能用于普通的csv数据，因为它把任何逗号都当作分隔符，即使在引用的字符串中也是如此。
This includes trying to re-import a csv file that was created by the shell:
这包括尝试重新导入由shell创建的csv文件:
Create table T (F1 integer, F2 varchar);Insert into T values (1, 'Hey!');Insert into T values (2, 'Hey, You!');.mode csv.output test.csvselect * from T;Contents of test.csv:1,Hey!2,"Hey, You!"delete from T;.import test.csv TError: test.csv line 2: expected 2 columns of data but found 3It seems we must transform the csv into a list of Insert statements, or perhaps a different delimiter will work.
似乎我们必须将csv转换成插入语句的列表，或者使用不同的分隔符。
Over at SuperUser I saw a suggestion to use LogParser to deal with csv files, I'm going to look into that.
在SuperUser上，我看到了使用LogParser处理csv文件的建议，我将对此进行研究。
#5
10
If you are happy to use a (python) script then there is a python script that automates this at: https://github.com/rgrp/csv2sqlite
如果您喜欢使用(python)脚本，那么有一个python脚本可以在以下实现自动化:https://github.com/rgrp/csv2sqlite
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").
这将自动为您创建表，以及为您做一些基本的类型猜测和数据转换(例如，它将计算出一个数字，并将列类型设置为“real”)。
#6
6
Remember that the default delimiter for SQLite is the pipe "|"
记住，SQLite的默认分隔符是管道“|”
sqlite> .separator ";"sqlite> .import path/filename.txt tablename http://sqlite.awardspace.info/syntax/sqlitepg01.htm#sqlite010
http://sqlite.awardspace.info/syntax/sqlitepg01.htm sqlite010
#7
1
SQLite is extremely flexible as it also allows the SQLite specific dot commands in the SQL syntax, (although they are interpreted by CLI.) This means that you can do things like this.
SQLite非常灵活，因为它还允许SQL语法中使用特定于SQLite的点命令(尽管CLI对它们进行了解释)。这意味着你可以做这样的事情。
Create a sms table like this:
创建这样的sms表:
# 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);Then two files:
然后两个文件:
# 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 smsTo test the import of the CSV file using the SQL file, run:
要使用SQL文件测试CSV文件的导入，请运行:
# sqlite3 -csv -header mycool.db '.read test.sql'In conclusion, this means that you can use the .import statement in SQLite SQL, just as you can do in any other RDB, like MySQL with LOAD DATA INFILE etc. However, this is not recommended.
总之，这意味着您可以在SQLite SQL中使用.import语句，就像在任何其他RDB中使用。
#8
1
Check out termsql. https://gitorious.org/termsql https://gitorious.org/termsql/pages/Home
查看termsql。https://gitorious.org/termsql https://gitorious.org/termsql/pages/Home
It converts text to SQL on the command line. (CSV is just text)
它将文本转换为命令行上的SQL。(CSV是文本)
Example:
例子:
cat textfile | termsql -o sqlite.dbBy default the delimiter is whitespace, so to make it work with CSV that is using commata, you'd do it like this:
默认情况下，分隔符是空格，所以要让它与使用commata的CSV兼容，您应该这样做:
cat textfile | termsql -d ',' -o sqlite.dbalternatively you can do this:
你也可以这样做:
termsql -i textfile -d ',' -o sqlite.dbBy default it will generate column names "COL0", "COL1", if you want it to use the first row for the columns names you do this:
默认情况下，它将生成列名“COL0”、“COL1”，如果您希望它为列名使用第一行，您可以这样做:
termsql -i textfile -d ',' -1 -o sqlite.dbIf you want to set custom column names you do this:
如果您想设置自定义列名，您可以这样做:
termsql -i textfile -d ',' -c 'id,name,age,color' -o sqlite.db#9
1
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.
如果您在windows中使用它，请确保将路径添加到“”中的db中，并在路径中使用双斜杠\，以确保windows理解它。
#10
0
This is how you can insert into an identity column:
这就是如何插入标识列:
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;myfile.txt is a file in C:\code\db\predefined\
myfile。txt是一个C:\code\db\预定义\ \ \ \ \的文件
data.db is in C:\code\db\
数据。db是C:\ \ db \代码
myfile.txt contains strings separated by newline character.
myfile。txt包含由换行字符分隔的字符串。
If you want to add more columns, it's easier to separate them using the pipe character, which is the default.
如果您想要添加更多的列，那么使用管道字符(这是默认的)就可以更容易地将它们分开。
#11
-1
Import your csv or sql to sqlite with phpLiteAdmin, it is excellent.
用phpLiteAdmin将csv或sql导入到sqlite，非常棒。