database 如何将 .sql 或 .csv 文件导入 SQLite？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1045910/
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
How to import load a .sql or .csv file into SQLite?
提问by happythenewsad
I need to dump a .sqlor .csvfile 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.
我收到一个语法错误，因为它需要一个表而不是整个数据库。
回答by Jay Igor
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>
回答by Kyle Cronin
Try doing it from the command like:
尝试通过以下命令执行此操作：
cat dump.sql | sqlite3 database.db
This will obviously only work with SQL statements in dump.sql. I'm not sure how to import a CSV.
这显然只适用于 dump.sql 中的 SQL 语句。我不确定如何导入 CSV。
回答by Jake Wyman
To go from SCRATCH with SQLite DB to importing the CSV into a table:
要从 SQLite DB 的 SCRATCH 到将 CSV 导入到表中：
- Get SQLite from the website.
- At a command prompt run
sqlite3 <your_db_file_name>*It will be created as an empty file. - Make a new table in your new database. The table must match your CSV fields for import.
- You do this by the SQL command:
CREATE TABLE <table_Name> (<field_name1> <Type>, <field_name2> <type>);
- 从网站获取 SQLite。
- 在命令提示符下运行
sqlite3 <your_db_file_name>*它将被创建为一个空文件。 - 在新数据库中创建一个新表。该表必须与您的 CSV 字段匹配才能导入。
- 您可以通过 SQL 命令执行此操作：
CREATE TABLE <table_Name> (<field_name1> <Type>, <field_name2> <type>);
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>
回答by blairxy
The sqlite3 .import command won't work for ordinary csv data because it treats any comma as a delimiter even in a quoted string.
sqlite3 .import 命令不适用于普通的 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.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
It seems we must transform the csv into a list of Insert statements, or perhaps a different delimiter will work.
看来我们必须将 csv 转换为 Insert 语句列表，或者可能使用不同的分隔符。
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 文件的建议，我将对此进行研究。
回答by Rufus Pollock
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").
这将自动为您创建表格，并为您进行一些基本的类型猜测和数据转换（例如，它将计算出数字并将列类型设置为“真实”）。
回答by DnD
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
回答by user3573558
Check out termsql. https://gitorious.org/termsqlhttps://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.db
By default the delimiter is whitespace, so to make it work with CSV that is using commata, you'd do it like this:
默认情况下，分隔符是空格，因此要使其与使用逗号的 CSV 一起使用，您可以这样做：
cat textfile | termsql -d ',' -o sqlite.db
alternatively you can do this:
或者你可以这样做：
termsql -i textfile -d ',' -o sqlite.db
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:
默认情况下，它将生成列名“COL0”、“COL1”，如果您希望它使用第一行作为列名，您可以这样做：
termsql -i textfile -d ',' -1 -o sqlite.db
If you want to set custom column names you do this:
如果要设置自定义列名称，请执行以下操作：
termsql -i textfile -d ',' -c 'id,name,age,color' -o sqlite.db
回答by not2qubit
SQLite is extremely flexible as it also allows the SQLite specific dot commandsin the SQL syntax, (although they are interpreted by CLI.) This means that you can do things like this.
SQLite 非常灵活，因为它还允许在 SQL 语法中使用 SQLite 特定的点命令（尽管它们由 CLI 解释）。这意味着您可以执行此类操作。
Create a smstable 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 sms
To 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 .importstatement in SQLite SQL, just as you can do in any other RDB, like MySQL with LOAD DATA INFILEetc. However, this is not recommended.
总之，这意味着您可以.import在 SQLite SQL 中使用该语句，就像在任何其他 RDB 中一样，例如 MySQL withLOAD DATA INFILE等。但是，不推荐这样做。
回答by MahD
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 中使用它，请确保将路径添加到 "" 中的数据库，并在路径中使用双斜杠 \ 以确保 Windows 理解它。
回答by live-love
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\predefined\ 中的一个文件
data.db is in C:\code\db\
data.db 在 C:\code\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.
如果要添加更多列，使用竖线字符将它们分开会更容易，这是默认设置。
