如何将 .sql 或 .csv 文件导入到 SQLite 中?
我需要将 .sql 或 .csv 文件转储到 SQLite 中(我正在使用 SQLite3 API)。 我只找到了导入/加载表的文档,而不是整个数据库。 现在,当我输入:
sqlite3prompt> .import FILENAME TABLE
我收到语法错误,因为它需要一个表而不是整个数据库。
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:
sqlite3prompt> .import FILENAME TABLE
I get a syntax error, since it's expecting a table and not an entire DB.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(11)
要从 SQL 文件导入,请使用以下命令:
要从 CSV 文件导入,您需要指定文件类型和目标表:
To import from an SQL file use the following:
To import from a CSV file you will need to specify the file type and destination table:
尝试通过以下命令执行此操作:
这显然仅适用于 dump.sql 中的 SQL 语句。 我不知道如何导入 CSV。
Try doing it from the command like:
This will obviously only work with SQL statements in dump.sql. I'm not sure how to import a CSV.
要使用 SQLite DB 从 SCRATCH 到将 CSV 导入表中:
sqlite3
*它将创建为空文件。CREATE TABLE(,);
创建表并且列与文件中的数据匹配后,您可以执行上述操作...
To go from SCRATCH with SQLite DB to importing the CSV into a table:
sqlite3 <your_db_file_name>
*It will be created as an empty file.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...
sqlite3 .import 命令不适用于普通 csv 数据,因为即使在带引号的字符串中,它也会将任何逗号视为分隔符。
这包括尝试重新导入由 shell 创建的 csv 文件:
看来我们必须将 csv 转换为 Insert 语句列表,或者可能使用不同的分隔符。
在 SuperUser 中,我看到了使用 LogParser 处理 csv 文件的建议,我将对此进行研究。
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:
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.
如果您愿意使用(python)脚本,那么有一个 python 脚本可以自动执行此操作:https://github.com/rgrp/ csv2sqlite
这将为您自动创建表格,并为您进行一些基本的类型猜测和数据转换(例如,它将计算出某个数字并将列类型设置为“真实”)。
If you are happy to use a (python) script then there is a python script that automates this at: 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").
请记住,SQLite 的默认分隔符是管道“|”
http://sqlite.awardspace.info/syntax/sqlitepg01.htm#sqlite010
Remember that the default delimiter for SQLite is the pipe "|"
http://sqlite.awardspace.info/syntax/sqlitepg01.htm#sqlite010
查看termsql。 https://gitorious.org/termsql https://gitorious.org/termsql/pages/Home
它在命令行上将文本转换为 SQL。 (CSV 只是文本)
示例:
默认情况下分隔符是空格,因此要使其与使用逗号的 CSV 一起使用,您可以这样做:
或者您可以这样做:
默认情况下它将生成列名称“COL0” ", "COL1",如果您希望它使用第一行作为列名称,您可以这样做:
如果您想设置自定义列名称,您可以这样做:
Check out termsql. https://gitorious.org/termsql https://gitorious.org/termsql/pages/Home
It converts text to SQL on the command line. (CSV is just text)
Example:
By default the delimiter is whitespace, so to make it work with CSV that is using commata, you'd do it like this:
alternatively you can do this:
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:
If you want to set custom column names you do this:
SQLite 非常灵活,因为它还允许在 SQL 语法中使用 SQLite 特定的点命令(尽管它们由 CLI 解释。)这意味着您可以执行类似的操作。
创建一个
sms
表,如下所示:然后创建两个文件:
要使用 SQL 文件测试 CSV 文件的导入,请运行:
总之,这意味着您可以使用
.import 语句,就像您可以在任何其他 RDB 中执行的操作一样,例如 MySQL 中的 LOAD DATA INFILE 等。但是,不建议这样做。
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.
Create a
sms
table like this:Then two files:
To test the import of the CSV file using the SQL file, run:
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 withLOAD DATA INFILE
etc. However, this is not recommended.如果您在 Windows 中使用它,请务必在“”中添加数据库的路径,并在路径中使用双斜杠 \ 以确保 Windows 能够理解它。
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.
这是插入标识列的方法:
myfile.txt 是 C:\code\db\predefined\ 中的文件
data.db 位于 C:\code\db\
myfile.txt 包含由换行符分隔的字符串。
如果您想添加更多列,使用管道字符(默认值)分隔它们会更容易。
This is how you can insert into an identity column:
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.
使用 phpLiteAdmin 将您的 csv 或 sql 导入到 sqlite,这非常好。
Import your csv or sql to sqlite with phpLiteAdmin, it is excellent.