Hmmm....your data import works just fine in 3.7.9...but you're correct that
3.7.13 burps with that error message doing the same import.
So something changed....
C:\sqlite>sqlite3 data.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> CREATE TABLE CONFIGURATION
...> (
...> RECORD_ID NUMERIC NOT NULL,
...> TEXT TEXT,
...> NUMERIC_DATA NUMERIC
...> );
sqlite>
sqlite> .import data.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")','43721S');
INSERT INTO "CONFIGURATION" VALUES(2,'TEXT LINE 2 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(3,'TEXT LINE 3 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(4,'TEXT LINE 4 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(5,'TEXT LINE 5 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(6,'TEXT LINE 6 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(7,'TEXT LINE 7 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(8,'TEXT LINE 8 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(9,'TEXT LINE 9 (72")','43721S');
COMMIT;
Works in 3.7.10 too.
Reuse the db file for 3.7.13
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
);
INSERT INTO "CONFIGURATION" VALUES(1,'TEXT LINE 1 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(2,'TEXT LINE 2 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(3,'TEXT LINE 3 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(4,'TEXT LINE 4 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(5,'TEXT LINE 5 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(6,'TEXT LINE 6 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(7,'TEXT LINE 7 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(8,'TEXT LINE 8 (72")','43721S');
INSERT INTO "CONFIGURATION" VALUES(9,'TEXT LINE 9 (72")','43721S');
COMMIT;
sqlite> .import data.txt configuration
Error: data.txt line 10: expected 3 columns of data but found 2
sqlite> .separator |
sqlite> .import data.txt configuration
Error: data.txt line 10: expected 3 columns of data but found 2
Even putting single quotes around field 2 & 3 gives the same error.
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 Hayes, Michael - IS [michael.ha...@exelisinc.com]
Sent: Monday, July 23, 2012 7:37 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes
When Importing Data
I've got input data that uses double quotes to mean inches. So I have records
with a single double quote character in a record. In some cases, SQLite is
ignoring separators that are after the quotes.
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.). However, the SQLite shell seems to be behaving differently when
there is a single quote in the record.
I'm using "sqlite-shell-win32-x86-3071300.zip" and
"sqlite-dll-win32-x64-3071300.zip" on Windows XP.
To reproduce, create this table and import the attached data file:
CREATE TABLE CONFIGURATION
(
RECORD_ID NUMERIC NOT NULL,
TEXT TEXT,
NUMERIC_DATA NUMERIC
);
If there are an odd number of quotes in the file, the error message is "Error:
Separator_Ignored_Inside_Quotes.txt line 10: expected 3 columns of data but
found 2".
If there are an even number of quotes in the file, every other record is
imported and the data within the quotes is imported into the column including
separators. Edit the attached file to remove the last line and you'll see this
behavior.
Thanks for looking it this bug and for SQLite.
Mike Hayes
Exelis Inc., Bowie, MD.
________________________________
Email addresses of ITT Exelis employees have changed from itt.com to
exelisinc.com. Please update your favorites and contact information to reflect
these changes.
This e-mail and any files transmitted with it may be proprietary and are
intended solely for the use of the individual or entity to whom they are
addressed. If you have received this e-mail in error please notify the sender.
Please note that any views or opinions presented in this e-mail are solely
those of the author and do not necessarily represent those of Exelis Inc. The
recipient should check this e-mail and any attachments for the presence of
viruses. Exelis Inc. accepts no liability for any damage caused by any virus
transmitted by this e-mail.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
