SQLite - 从csv文件将带换行符的字符串插入数据库 [英] SQLite - inserting a string with newlines into database from csv file

问题描述

全部，

我试图在TEXT字段中将长文本条目注入SQLite数据库。

I am trying to inject a long text entry into a SQLite database, in a TEXT field. This text has new lines in it (i.e. it spans multiple paragraphs).

如果我手动执行INSERT，我可以得到新的行显示：

I can get the new lines to show up if I do the INSERT manually:

INSERT INTO "LOGENTRY" VALUES(5,40,'PLACE','line1
line2

line4
',1283990533315,'4A','TEXT','',NULL);

但是如果我有一个CSV文件中的等效文本并尝试将其导入表中，我得到一个错误，它期望更多的列比存在（一旦遇到新行，程序假设它是输入的结束，因此它的缺少列）。

but if I have the equivalent text in a CSV file and attempt to .import it into the table, I get an error that it's expecting more columns than exist (as soon as the new line is encountered, the program assumes it's the end of the input and thus it's missing columns).

这是可能吗？

推荐答案

您的问题是csv格式：sqlite不支持包含

Your problem is the csv format: sqlite doesn't support values containing newlines.

在sqlite方言中：

In the sqlite dialect:

• 逗号分隔字段

• Commas separate fields

双引号可用于对包含逗号的字段进行分组。

Double quotes can be used to group a field which contains commas.

strong>

New lines delimit records

以下（sed）常规表达式将删除不需要的换行符

The following (sed) regular expresion will "delete" unwanted line feeds

s/\(,"[^",]*\)$/\1\1/g

这篇关于SQLite - 从csv文件将带换行符的字符串插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！