Is it possible to tell Sqlite to ignore trailing field separator when using import command?

可以告诉SQLite忽略尾字段分隔符使用导入命令的时候?

问题 (Question)

I want to load a table with a text file delimited by pipes

Table
--------------------------

Id + Employee + Department

--------------------------

File

0|Some Name|Some department|
1|Another Name|Another department|

I use the .separator command to set the "|" separator and then execute the .import command.

I receive the following error Error: EMP.TXT line 1: expected 3 columns of data but found 4.

I want to know if ther is some option in sqlite3 to ignore the trailing pipe.

我想加载一个表格和文本文件分隔管

Table
--------------------------

Id + Employee + Department

--------------------------

文件

0|Some Name|Some department|
1|Another Name|Another department|

我用。分离器命令来设置“|“分离器然后执行导入命令。

我收到以下错误错误:emp.txt线条1:预期的3列的数据却发现4。

我想知道,如果是一些期权sqlite3忽略尾管。

最佳答案 (Best Answer)

You could import into a temporary table with a dummy column:

CREATE TEMP TABLE DummyTable(id, emp, dep, nothing);
.import importfile DummyTable
INSERT INTO MyTable SELECT id, emp, dep FROM DummyTable;
DROP TABLE DummyTable;

你可以导入到一个临时表是一个虚拟的柱:

CREATE TEMP TABLE DummyTable(id, emp, dep, nothing);.import importfile DummyTableINSERT INTO MyTable SELECT id, emp, dep FROM DummyTable;DROP TABLE DummyTable;

答案 (Answer) 2

You could remove the superfluous pipe before importing:

< importfile sed 's/|$//' | sqlite3 my.db ".import /dev/stdin MyTable" 

你可以在导入前移除多余的管:

< importfile sed 's/|$//' | sqlite3 my.db ".import /dev/stdin MyTable" 

本文翻译自StackoverFlow,英语好的童鞋可直接参考原文:http://stackoverflow.com/questions/16925924