macos 如何将文件导入sqlite？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/455606/
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 file into sqlite?
提问by 4thSpace
On a Mac, I have a txt file with two columns, one being an autoincrement in an sqlite table:
在 Mac 上，我有一个包含两列的 txt 文件，其中一列是 sqlite 表中的自动增量：
, "mytext1"
, "mytext2"
, "mytext3"
When I try to import this file, I get a datatype mismatch error:
当我尝试导入此文件时，出现数据类型不匹配错误：
.separator ","
.import mytextfile.txt mytable
How should the txt file be structured so that it uses the autoincrement?
应该如何构造 txt 文件以使其使用自动增量？
Also, how do I enter in text that will have line breaks? For example:
另外，如何输入有换行符的文本？例如：
"this is a description of the code below.
The text might have some line breaks and indents. Here's
the related code sample:
foreach (int i = 0; i < 5; i++){
//do some stuff here
}
this is a little more follow up text."
I need the above inserted into one row. Is there anything special I need to do to the formatting?
我需要将上述内容插入一行。我需要对格式做些什么特别的事情吗？
For one particular table, I want each of my rows as a file and import them that way. I'm guessing it is a matter of creating some sort of batch file that runs multiple imports.
对于一个特定的表，我希望我的每一行都作为一个文件并以这种方式导入它们。我猜这是创建某种运行多个导入的批处理文件的问题。
Edit
编辑
That's exactly the syntax I posted, minus a tab since I'm using a comma. The missing line break in my post didn't make it as apparent. Anyways, that gives the mismatch error.
这正是我发布的语法，因为我使用的是逗号，所以减去了一个制表符。我帖子中缺少的换行符并没有让它变得明显。无论如何，这会导致不匹配错误。
回答by alexandroid
I was looking on the same problem. Looks like I've found an answeron the first part of your question — about importing a file into a table with ID field.
我正在寻找同样的问题。看起来我在问题的第一部分找到了答案- 关于将文件导入带有 ID 字段的表。
So yes, create a temporary table without ID, import your file into it, then do insert..select to copy its data into your target table. (Remove leading commas from mytextfile.txt).
所以是的，创建一个没有 ID 的临时表，将您的文件导入其中，然后执行 insert..select 将其数据复制到您的目标表中。（从 mytextfile.txt 中删除前导逗号）。
-- assuming your table is called Strings and
-- was created like this:
-- create table Strings( ID integer primary key, Code text )
create table StringsImport( Code text );
.import mytextfile.txt StringsImport
insert into Strings ( Code ) select * from StringsImport;
drop table StringsImport;
Do not know what to do with newlines. I've read some mentions that importing in CSV mode will do the trick (.mode csv), but when I tried it did not seem to work.
不知道换行怎么办。我读过一些提到以 CSV 模式导入可以解决问题（.mode csv），但是当我尝试时它似乎不起作用。
回答by Chris
I'm in the process of moving data containing long text fields with various punctuation marks (they are actually articles on coding) into SQLite and I've been experimenting with various text imports.
我正在将包含带有各种标点符号的长文本字段（它们实际上是关于编码的文章）的数据移动到 SQLite 中，并且我一直在尝试各种文本导入。
I created a database in SQLite with a table:
我在 SQLite 中创建了一个带有表的数据库：
CREATE TABLE test (id PRIMARY KEY AUTOINCREMENT, textfield TEXT);
then do a backup with .dump.
然后用 .dump 做一个备份。
I then add the text below the "CREATE TABLE" line manually in the resulting .dump file as such:
然后，我在生成的 .dump 文件中手动添加“CREATE TABLE”行下方的文本，如下所示：
INSERT INTO test textfield VALUES (1,'Is''t it great to have
really long text with various punctaution marks and
newlines');
Change any single quotes to two single quotes (change ' to ''). Note that an index number needs to be added manually (I'm sure there is an AWK/SED command to do it automatically). Change the auto increment number in the "sequence" line in the dump file to one above the last index number you added (I don't have SQLite in front of me to give you the exact line, but it should be obvious).
With the new file, I can then do a restore onto the database
将任何单引号更改为两个单引号（将 ' 更改为 ''）。请注意，需要手动添加索引号（我确定有一个 AWK/SED 命令可以自动添加）。将转储文件中“序列”行中的自动增量编号更改为您添加的最后一个索引编号上方的一个（我前面没有 SQLite 来为您提供确切的行，但应该很明显）。
使用新文件，然后我可以对数据库进行还原
回答by karmafunk
In case anyone is still having issues with this you can download an SQLLite manager.
如果有人对此仍有问题，您可以下载 SQLLite 管理器。
There are several that allow importing from a CSV file.
有几个允许从 CSV 文件导入。
Here is one but a google search should reveal a few: http://sqlitemanager.en.softonic.com/
这是一个，但谷歌搜索应该会显示一些：http: //sqlitemanager.en.softonic.com/
