gmane.comp.db.sqlite.general

Wilson, Ron P | 19 Aug 22:45

.import with .separator and quoted strings

I'm trying to import a table using the command line tool.

sqlite> .separator ,

sqlite> .import export.csv library

export.csv line 1: expected 53 columns of data but found 77

sqlite> .mode csv

sqlite> .import export.csv library

export.csv line 1: expected 53 columns of data but found 77

All entries are quoted strings, but some of them have commas within the
strings.  It appears that SQLite is ignoring the string quoting and
taking all commas literally.  Is this intended?  The same import works
fine in Excel with 53 columns resulting.  I have also tried tab
delimited and apparently some of the strings in this dataset also
contain tabs.

sqlite> .mode tabs

sqlite> .import export.txt library

export.txt line 162: expected 53 columns of data but found 55

I don't control the data source, and I would really like to avoid
pre-munging the data.

(Continue reading)

headers

Wilson, Ron P | 21 Aug 22:36

Re: .import with .separator and quoted strings

Here is an easy way to reproduce the symptom.  Given the following file
as input for the .import command:

---csvtest.csv---
"1","wilson, ron"
"2","momma, your"
-----------------

Here is the sqlite output:

SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table names (id integer, name);
sqlite> .mode csv
sqlite> .import csvtest.csv names
csvtest.csv line 1: expected 2 columns of data but found 3
sqlite> .quit

Clearly it is parsing the comma in the name column as a record
delimiter.  Is there a mode that causes the .import command to honor
quoted entries?

RW

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

-----Original Message-----
From: sqlite-users-bounces@...
[mailto:sqlite-users-bounces@...] On Behalf Of Wilson, Ron P

(Continue reading)

headers

Griggs, Donald | 21 Aug 23:38

Re: .import with .separator and quoted strings

Hi Ron,

I've encountered that as well.

You're using the sqlite3 commandline interface program, I'm sure.  
I think it was intended as a test and demo utility, but it's found its
way into a number of released products.

I'm not aware of a way to make this work with the current utility.
Since the source is available, you may want to modify it as needed.  I
know you prefer not to pre-process your input file, so enhancing the
source may be your best option.

In my case, *all* the fields were quoted in the input file, and so I
replaced occurances of:
    ","    (quote comma quote)
with a vertical bar  |
and trimmed the quotes from the beginning and end of each line.

You can even perform this using an sqlite3 script itself if you don't
mind a bit of madness.
  -Set the separator to something very odd such as '@$%'
  -Import the original text to a temporary table with a single field to
contain the entire row.
  -UPDATE each row, using REPLACE() to change  ","  to  |   (perhaps
after first checking for any actual virgules in the original data).
  -Use SUBSTR() to remove the two remaining quotes at each end of the
line.
  -Set the separator to | and export to a temp file. Delete the
temporary table and .import the data into your real table.

(Continue reading)

headers

Wilson, Ron P | 22 Aug 16:46

Re: .import with .separator and quoted strings

Hmm.  Ok I'll think about munging the data.  If I find some time perhaps
I'll submit some code to support quoted values in the sqlite3 command
line tool.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

-----Original Message-----
From: sqlite-users-bounces@...
[mailto:sqlite-users-bounces@...] On Behalf Of Griggs, Donald
Sent: Thursday, August 21, 2008 5:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] .import with .separator and quoted strings

Hi Ron,

I've encountered that as well.

You're using the sqlite3 commandline interface program, I'm sure.  
I think it was intended as a test and demo utility, but it's found its
way into a number of released products.

I'm not aware of a way to make this work with the current utility.
Since the source is available, you may want to modify it as needed.  I
know you prefer not to pre-process your input file, so enhancing the
source may be your best option.

In my case, *all* the fields were quoted in the input file, and so I
replaced occurances of:

(Continue reading)