Nope -- that doesn't work. Seems to me if the import is going to assume the field is text it should also recognize that if it doesn't start with a quote it shouldn't assume that all quotes are delimiters.
Plus, it should recognize that any quotes that aren't at the beginning or end-of-field aren't delimiters either. Only quotes at both ends of the fields may be removed. So these should all work when quotes are NOT the separator but the pipe symbol is: 1|"this is a test"|1 -- quotes removed field inserted 2|'this is a test'|2 -- single quotes removed and field inserted 3|'this"is"a"test'|3 -- singled quotes removed but double quotes remain. Or is there some standard that we ought to be following? C:\sqlite>more data2.txt 1|TEXT LINE 1 (72"""")'|43721 2|TEXT LINE 2 (72"""")'|43721 3|TEXT LINE 3 (72"""")'|43721 4|TEXT LINE 4 (72"""")'|43721 5|TEXT LINE 5 (72"""")'|43721 6|TEXT LINE 6 (72"""")'|43721 7|TEXT LINE 7 (72"""")'|43721 8|TEXT LINE 8 (72"""")'|43721 9|TEXT LINE 9 (72"""")'|43721 C:\sqlite>sqlite3 data.db SQLite version 3.7.13 2012-06-11 02:05:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE CONFIGURATION ( RECORD_ID NUMERIC NOT NULL, TEXT TEXT, NUMERIC_DATA NUMERIC ); COMMIT; sqlite> .import data2.txt configuration sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE CONFIGURATION ( RECORD_ID NUMERIC NOT NULL, TEXT TEXT, NUMERIC_DATA NUMERIC ); INSERT INTO "CONFIGURATION" VALUES(1,'TEXT LINE 1 (72"""")',43721); INSERT INTO "CONFIGURATION" VALUES(2,'TEXT LINE 2 (72"""")',43721); INSERT INTO "CONFIGURATION" VALUES(3,'TEXT LINE 3 (72"""")',43721); INSERT INTO "CONFIGURATION" VALUES(4,'TEXT LINE 4 (72"""")',43721); INSERT INTO "CONFIGURATION" VALUES(5,'TEXT LINE 5 (72"""")',43721); INSERT INTO "CONFIGURATION" VALUES(6,'TEXT LINE 6 (72"""")',43721); INSERT INTO "CONFIGURATION" VALUES(7,'TEXT LINE 7 (72"""")',43721); INSERT INTO "CONFIGURATION" VALUES(8,'TEXT LINE 8 (72"""")',43721); INSERT INTO "CONFIGURATION" VALUES(9,'TEXT LINE 9 (72"""")',43721); COMMIT; Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: [email protected] [[email protected]] on behalf of Richard Hipp [[email protected]] Sent: Monday, July 23, 2012 1:40 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data On Mon, Jul 23, 2012 at 2:28 PM, Kevin Benson <[email protected]>wrote: > On Mon, Jul 23, 2012 at 12:05 PM, Richard Hipp <[email protected]> wrote: > > > On Mon, Jul 23, 2012 at 8:37 AM, Hayes, Michael - IS < > > [email protected]> wrote: > > > > > > > > The documentation says that the separator will be honored even inside > of > > > quotes. ("The SQLite shell will always split fields on the separator > > > character, no matter what comes before or after it. Quotes or > backslashes > > > won't escape them.). > > > > > > I'm not able to find this statement anywhere in the SQLite documentation. > > Can you send a link? > > > > -- > > > He's quoted from the wiki: > http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles > Yeah. That wiki is really old. Don't believe it.... The CVS import for the command-line shell treats " as a quoting characters. All content between "..." is considered to be part of a single field of the CVS, even if that content includes newline characters. I think it will work to escape your isolated " characters by replacing them with four double-quotes in a row: """" > > -- > -- > -- > --Ô¿Ô-- > K e V i N > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
