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: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Richard Hipp [d...@sqlite.org]
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 <kevin.m.ben...@gmail.com>wrote:
> On Mon, Jul 23, 2012 at 12:05 PM, Richard Hipp <d...@sqlite.org> wrote:
>
> > On Mon, Jul 23, 2012 at 8:37 AM, Hayes, Michael - IS <
> > michael.ha...@exelisinc.com> 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
