sqlite3 import带引号 [英] sqlite3 import with quotes

问题描述

我尝试导入在字段中包含引号的数据集合。

根据文档（ http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles ），sqlite shell应该解释字面上的引号，我认为这意味着我不应该有一个问题。 p>

我在这一行上遇到了问题：

  1193782372 Lips Like Sugar（12Mix）Echo& the Bunnymen 80's / 12：The Extended Collection a76d9b04-51d9-4672-801f-356ab36dbae7 ccd4879c-5e88-4385-b131-bf65296bf245 1abb270a-e791-407f-a989-ff3ad6f8401c 
  

由于不清楚标签的位置，我已将它们包含在以下行中。 p>

  1193782372\tLips Like Sugar（12Mix）\tEcho& the Bunnymen \t80's / 12 \ta76d9b04-51d9-4672-801f-356ab36dbae7\tccd4879c-5e88-4385-b131-bf65296bf245\t1abb270a-e791-407f-a989-ff3ad6f8401c 
  

我尝试执行以下操作但出现错误。

  sqlite> ; .separator \t 
 sqlite> .import ./file.txt表
错误：./file.txt第n行：预期的7列数据，但找到5 
  

它没有双引号，但引号很重要。我似乎不能用 \转义引号。

如何正确导入我想要的数据？

解决方案

您引用的网页是旧的（注意网址的cvstrac部分，赠品; sqlite现在使用化石，而不是cvs）。该网页的较新版本是此处。

由于SQLite是公共领域的软件，你的问题的一个解决方案是修复sqlite的shell.c来正确处理你的文件格式问题是行1861， / p>

  if（c ==''）inQuote =！inQuote; 
  

如果您不想要引用分隔，只需注释掉这一行。

另一种方法是使用支持SQLite的数据库管理器;有很多，并且大多数声称支持文件导入/导出。

I am trying to import a collection of data that has quotes within the fields. They are currently tab separated.

From what I can understand according to the docs (http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles), the sqlite shell should interpret quotes literally and I assume that means I shouldn't have a problem.

I've been running into a problem on this line:

1193782372  Lips Like Sugar (12" Mix)   Echo & the Bunnymen 80's/12": The Extended Collection   a76d9b04-51d9-4672-801f-356ab36dbae7    ccd4879c-5e88-4385-b131-bf65296bf245    1abb270a-e791-407f-a989-ff3ad6f8401c

Since it isn't clear where the tabs are, I've included them in this following line.

1193782372\tLips Like Sugar (12" Mix)\tEcho & the Bunnymen\t80's/12": The Extended Collection\ta76d9b04-51d9-4672-801f-356ab36dbae7\tccd4879c-5e88-4385-b131-bf65296bf245\t1abb270a-e791-407f-a989-ff3ad6f8401c

I'm trying to do the following but getting an error.

sqlite> .separator \t
sqlite> .import ./file.txt table
Error: ./file.txt line n: expected 7 columns of data but found 5

It works without the double quotes, but the quotes are important. I don't seem to be able to escape the quotes with \" either.

How can I properly import the data I want?

解决方案

The web page you reference is old (note the cvstrac portion of the URL, which is the giveaway; sqlite uses fossil now, not cvs). The newer version of that web page is here.

Since SQLite is public domain software, one solution to your problem is to fix sqlite's shell.c to handle your file format correctly. The problem is around line 1861 that does

if( c=='"' ) inQuote = !inQuote;

If you don't want quote delimiting, just comment out this line. The purpose of the line is so you can embed delimiters in your columns by quoting the column.

Another approach is to use a Database Manager that supports SQLite; there are many of them, and most claim to support file import/export.

这篇关于sqlite3 import带引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！