“未转义"字符"将文本数据导入 SQLite 时 [英] "unescaped " character" when importing text data into SQLite

问题描述

我正在尝试导入一个以分号分隔的文本文件，其中每一行都以 CRLF 结尾.第一行包含字段，数据从第 2 行开始:

I'm trying to import a semicolon-separated text file where each line ends in CRLF. The first line contains the fields, and the data start at line 2:

"Field1";"Field2"
"123";"Foo"
"456";"Bar"

我尝试了以下操作，但失败并显示input.csv:23: unescaped" character":

I tried the following, but it fails with "input.csv:23: unescaped " character":

sqlite3.exe
sqlite> .separator ";"
sqlite> .mode csv MyTable
sqlite> .import input.csv MyTable

知道我做错了什么吗?

谢谢.

推荐答案

对于sqlite，当要导入的数据可能包含双引号字符()时，不要使用csv模式.读取每个 CSV 字段的代码 csv_read_one_field寻找它，当它找到它时，确保它被终止或期望它被引用.

For sqlite, when the data to be imported may contain the double-quote character ("), do not use csv mode. The code that reads each CSV field csv_read_one_field looks for it and when it finds it, ensures that it is terminated or expects it to be quoted.

将列分隔符更改为;"不会有帮助，因为该代码仍将被执行.

Changing the column separator to ';' won't help because that code will still be executed.

另一方面，以ascii方式读取每个字段的代码ascii_read_one_field 仅使用列和行分隔符来确定字段内容.

On the other hand, the code that reads each field in ascii mode ascii_read_one_field only uses the column and row separators to determine the field contents.

因此，使用 ascii 模式并将列和行分隔符设置为分号和行尾，如下所示:

So, Use ascii mode and set the column and row separators to semi-colon and end of line like this:

*尼克斯:

sqlite> .mode ascii
sqlite> .separator ";" "\n"
sqlite> .import input.csv MyTable

窗口:

sqlite> .mode ascii
sqlite> .separator ";" "\r\n"
sqlite> .import input.csv MyTable

但是，这不会去掉围绕数据的双引号；它们被视为您数据的一部分.

However, that will not strip out the double-quotes surrounding your data; they are considered part of your data.

这篇关于“未转义"字符"将文本数据导入 SQLite 时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！