Re: [sqlite] Tedious CSV import question

[Nuno Lucas]

Emyr Thomas wrote:
> On Oct 6, 12:50 pm, Pavel Ivanov  wrote:
>> I think you need to change your last compilation command line to this:
>>
>> gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib
>> -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o
> 
> That didn't quite do it, but after a little bit of fiddling I finally
> got it to compile/link on Mac OS X by doing:
> 
> gcc -dynamiclib -flat_namespace -undefined suppress -shared -o
> virtcsv.so -O2 -L/opt/local/lib -liconv -lsqlite3 virtual_csv.o
> csvfile.o ext_init.o
> 
> Thanks for all your help!

Glad you got help from others, as I really have no idea on how to compile 
things 
on Mac OS.


Regards,
~Nuno Lucas

> 
> --Emyr
> ___
> 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

Re: [sqlite] Tedious CSV import question

[Jay A. Kreibich]

On Tue, Oct 06, 2009 at 07:50:52AM -0400, Pavel Ivanov scratched on the wall:
> I think you need to change your last compilation command line to this:
> 
> gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib
> -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o

  Neither -shared nor -soname are supported on Mac OS X.  You need to
  use -dynamiclib.

  Also, since OS X doesn't use ELF, the correct dynamic library
  extension is .dylib, not .so.  The file format is more robust
  and allows things like multiple architectures.
  
   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Emyr Thomas]

On Oct 6, 12:50 pm, Pavel Ivanov  wrote:
> I think you need to change your last compilation command line to this:
>
> gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib
> -lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o

That didn't quite do it, but after a little bit of fiddling I finally
got it to compile/link on Mac OS X by doing:

gcc -dynamiclib -flat_namespace -undefined suppress -shared -o
virtcsv.so -O2 -L/opt/local/lib -liconv -lsqlite3 virtual_csv.o
csvfile.o ext_init.o

Thanks for all your help!

--Emyr
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Pavel Ivanov]

I think you need to change your last compilation command line to this:

gcc -shared -Wl,-soname,virtcsv.so -o virtcsv.so -O2 -L/opt/local/lib
-lsqlite3 -liconv virtual_csv.o csvfile.o ext_init.o


Pavel

On Tue, Oct 6, 2009 at 6:16 AM, Emyr Thomas  wrote:
> On Oct 6, 10:52 am, Dan Kennedy  wrote:
>> You have the most recent version installed. But perhaps the pre-
>> processor
>> is finding an older sqlite3.h. Try adding -I/usr/local/include to the
>> gcc command.
>
> Thanks Dan. Macports puts things in /opt/local, so I added -I/usr/
> local/include and -L/opt/local/lib to the make file. It gets further,
> but now I get the following error:
>
> macbook:csvfile emyr$ make
> gcc -c -o virtual_csv.o -s -O2 -Wall -Wextra -I/opt/local/include
> virtual_csv.c
> gcc -c -o csvfile.o -s -O2 -Wall -Wextra -I/opt/local/include
> csvfile.c
> gcc -c -o ext_init.o -s -O2 -Wall -Wextra -I/opt/local/include
> ext_init.c
> gcc -shared -o virtcsv.so -O2 -L/opt/local/lib virtual_csv.o csvfile.o
> ext_init.o
> Undefined symbols:
>  "_libiconv_open", referenced from:
>      _csv_connect in virtual_csv.o
>  "_main", referenced from:
>      start in crt1.10.5.o
>  "_libiconv_close", referenced from:
>      _csv_disconnect in virtual_csv.o
>  "_sqlite3_result_null", referenced from:
>      _csv_column in virtual_csv.o
>  "_sqlite3_result_text", referenced from:
>      _csv_column in virtual_csv.o
>  "_sqlite3_declare_vtab", referenced from:
>      _csv_connect in virtual_csv.o
>  "_sqlite3_create_module", referenced from:
>      _sq3_register_virtual_csv in virtual_csv.o
>  "_libiconv", referenced from:
>      _csv_column in virtual_csv.o
>  "_sqlite3_mprintf", referenced from:
>      _csv_connect in virtual_csv.o
>      _csv_connect in virtual_csv.o
> ld: symbol(s) not found
> collect2: ld returned 1 exit status
> make: *** [virtcsv.so] Error 1
> ___
> 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

Re: [sqlite] Tedious CSV import question

[Emyr Thomas]

On Oct 6, 11:16 am, Emyr Thomas  wrote:
> Thanks Dan. Macports puts things in /opt/local, so I added -I/usr/
> local/include and -L/opt/local/lib to the make file. It gets further,
> but now I get the following error:

Of course I meant -I/opt/local/include, not -I/usr/local/include.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Emyr Thomas]

On Oct 6, 10:52 am, Dan Kennedy  wrote:
> You have the most recent version installed. But perhaps the pre-
> processor
> is finding an older sqlite3.h. Try adding -I/usr/local/include to the
> gcc command.

Thanks Dan. Macports puts things in /opt/local, so I added -I/usr/
local/include and -L/opt/local/lib to the make file. It gets further,
but now I get the following error:

macbook:csvfile emyr$ make
gcc -c -o virtual_csv.o -s -O2 -Wall -Wextra -I/opt/local/include
virtual_csv.c
gcc -c -o csvfile.o -s -O2 -Wall -Wextra -I/opt/local/include
csvfile.c
gcc -c -o ext_init.o -s -O2 -Wall -Wextra -I/opt/local/include
ext_init.c
gcc -shared -o virtcsv.so -O2 -L/opt/local/lib virtual_csv.o csvfile.o
ext_init.o
Undefined symbols:
  "_libiconv_open", referenced from:
  _csv_connect in virtual_csv.o
  "_main", referenced from:
  start in crt1.10.5.o
  "_libiconv_close", referenced from:
  _csv_disconnect in virtual_csv.o
  "_sqlite3_result_null", referenced from:
  _csv_column in virtual_csv.o
  "_sqlite3_result_text", referenced from:
  _csv_column in virtual_csv.o
  "_sqlite3_declare_vtab", referenced from:
  _csv_connect in virtual_csv.o
  "_sqlite3_create_module", referenced from:
  _sq3_register_virtual_csv in virtual_csv.o
  "_libiconv", referenced from:
  _csv_column in virtual_csv.o
  "_sqlite3_mprintf", referenced from:
  _csv_connect in virtual_csv.o
  _csv_connect in virtual_csv.o
ld: symbol(s) not found
collect2: ld returned 1 exit status
make: *** [virtcsv.so] Error 1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Dan Kennedy]

On Oct 6, 2009, at 4:40 PM, Emyr Thomas wrote:

> On Oct 5, 8:09 pm, Nuno Lucas  wrote:
>> Emyr Thomas wrote:
>>> Could you please provide some info as to how you would build this as
>>> an extension module on Linux?
>>
>> "make" ?
>>
>> It would be easier if you stated why that doesn't work for you.
>
> Apologies, my question was very vague. Here are the details...
>
> I've tried to get the CSV virtual table extensions to compile on 3
> different architectures (OS X, RHEL5 and Cygwin) without luck. I'm no
> expert at this, so I may be missing something obvious.
>
> I tried first on Mac OS X with version 3.6.18 of sqlite installed via
> MacPorts.
>
> macbook:~ emyr$ sqlite3 -version
> 3.6.18
> macbook:~ emyr$ otool -L `which sqlite3`
> /opt/local/bin/sqlite3:
>   /opt/local/lib/libsqlite3.0.dylib (compatibility version 9.0.0,
> current version 9.6.0)
>   /opt/local/lib/libreadline.6.dylib (compatibility version 6.0.0,
> current version 6.0.0)
>   /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current
> version 111.1.4)
>   /opt/local/lib/libncurses.5.dylib (compatibility version 5.0.0,
> current version 5.0.0)
>   /usr/lib/libgcc_s.1.dylib (compatibility version 1.0.0, current
> version 1.0.0)
>
> So looks like I'm using the most recent version of sqlite...

You have the most recent version installed. But perhaps the pre- 
processor
is finding an older sqlite3.h. Try adding -I/usr/local/include to the
gcc command.


> macbook:~ emyr$ cd csvfile
> macbook:csvfile emyr$ make
> gcc -c -o virtual_csv.o -s -O2 -Wall -Wextra virtual_csv.c
> virtual_csv.c:377: error: syntax error before ‘sqlite3_int64’
> virtual_csv.c: In function ‘csv_rowid’:
> virtual_csv.c:379: error: ‘pCursor’ undeclared (first use in this
> function)
> virtual_csv.c:379: error: (Each undeclared identifier is reported only
> once
> virtual_csv.c:379: error: for each function it appears in.)
> virtual_csv.c:381: error: ‘pRowid’ undeclared (first use in this
> function)
> make: *** [virtual_csv.o] Error 1
>
> Any ideas what's going wrong here?
>
> Thanks again
>
> --Emyr
> ___
> 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

Re: [sqlite] Tedious CSV import question

[Emyr Thomas]

On Oct 5, 8:09 pm, Nuno Lucas  wrote:
> Emyr Thomas wrote:
> > Could you please provide some info as to how you would build this as
> > an extension module on Linux?
>
> "make" ?
>
> It would be easier if you stated why that doesn't work for you.

Apologies, my question was very vague. Here are the details...

I've tried to get the CSV virtual table extensions to compile on 3
different architectures (OS X, RHEL5 and Cygwin) without luck. I'm no
expert at this, so I may be missing something obvious.

I tried first on Mac OS X with version 3.6.18 of sqlite installed via
MacPorts.

macbook:~ emyr$ sqlite3 -version
3.6.18
macbook:~ emyr$ otool -L `which sqlite3`
/opt/local/bin/sqlite3:
/opt/local/lib/libsqlite3.0.dylib (compatibility version 9.0.0,
current version 9.6.0)
/opt/local/lib/libreadline.6.dylib (compatibility version 6.0.0,
current version 6.0.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current
version 111.1.4)
/opt/local/lib/libncurses.5.dylib (compatibility version 5.0.0,
current version 5.0.0)
/usr/lib/libgcc_s.1.dylib (compatibility version 1.0.0, current
version 1.0.0)

So looks like I'm using the most recent version of sqlite...

macbook:~ emyr$ cd csvfile
macbook:csvfile emyr$ make
gcc -c -o virtual_csv.o -s -O2 -Wall -Wextra virtual_csv.c
virtual_csv.c:377: error: syntax error before ‘sqlite3_int64’
virtual_csv.c: In function ‘csv_rowid’:
virtual_csv.c:379: error: ‘pCursor’ undeclared (first use in this
function)
virtual_csv.c:379: error: (Each undeclared identifier is reported only
once
virtual_csv.c:379: error: for each function it appears in.)
virtual_csv.c:381: error: ‘pRowid’ undeclared (first use in this
function)
make: *** [virtual_csv.o] Error 1

Any ideas what's going wrong here?

Thanks again

--Emyr
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Nuno Lucas]

Roger Binns wrote:
> Nuno Lucas wrote:
>> (in fact, the code doesn't compile using the bundled 
>> 3.4.2 version on my Ubuntu 8.04 machine). 
> 
> Are you sure it is the virtual table api that is the problem and not some
> other ones.  I haven't changed my virtual table code in years - the last
> change was when create_module_v2 was added so I no longer had to keep track
> of when the modules were in use (and hence safe to delete).

I looked into it just a bit and it seems the reason is the use of 
sqlite3_int64 in the xRowid method.

sqlite3_int64 doesn't seem to be defined in 3.4.2. Probably the callback 
prototype was modified to use it although binary speaking there was no 
change.

I also haven't modified the code for a couple of years. I wasn't even 
aware of the create_module_v2() function. Have to take a look at it.


Regards,
~Nuno Lucas

> 
> Roger

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Nuno Lucas wrote:
> All I can say is that the virtual table API is still considered 
> experimental (or was last time i checked), so it's not stable across 
> sqlite versions 

Virtual tables were added mid-2006 (SQLite 3.3.7).  3.3.8 had an
incompatible change.  From my own records a rename method may have been
added later but I can't tell without spending more time on the issue.

> (in fact, the code doesn't compile using the bundled 
> 3.4.2 version on my Ubuntu 8.04 machine). 

Are you sure it is the virtual table api that is the problem and not some
other ones.  I haven't changed my virtual table code in years - the last
change was when create_module_v2 was added so I no longer had to keep track
of when the modules were in use (and hence safe to delete).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrKbrgACgkQmOOfHg372QQvjQCdHrcDN63GO2qtq/fx8utQoW6S
IzkAoNPu62DY4iw0xzsUg73y1CkzbdJo
=cTvg
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Nuno Lucas]

Emyr Thomas wrote:
> On Sep 27, 6:35 pm, Nuno Lucas  wrote:
>> Some time ago I had to do something similar and decided to write a small
>> virtual table implementation to treatCSVfiles as just another table.
>> This works for my uses which is to import Excel and OpenOffice files.
>>
>> I cleaned up the code a bit (removed non-english comments and some use
>> of functions from internal libraries), add a sqlite3_extension_init()
>> and posted the zip for the sqlite3 extension module on the wiki [1].
>>
>> [1]http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles
>>
>> The code is used on both Win32 and Linux platforms, but this zip only
>> includes a makefile for Linux. It uses iconv() to handle character set
>> conversions, so that must be available if compiling for windows (or just
>>   make sure the source text file is already UTF-8).
> 
> Could you please provide some info as to how you would build this as
> an extension module on Linux?

"make" ?

It would be easier if you stated why that doesn't work for you.

All I can say is that the virtual table API is still considered 
experimental (or was last time i checked), so it's not stable across 
sqlite versions (in fact, the code doesn't compile using the bundled 
3.4.2 version on my Ubuntu 8.04 machine). You may need to install a 
recent version of SQLite or modify the Makefile to use your local library.

If your application uses malloc/realloc/free functions other than the 
glibc provided ones, you will probably need to change the code to use 
the sqlite3_malloc/sqlite3_free family of functions (something I forgot 
to do because I use the code statically linked).


Regards,
~Nuno Lucas

> 
> Thanks
> 
> --Emyr

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Emyr Thomas]

On Sep 27, 6:35 pm, Nuno Lucas  wrote:
> Some time ago I had to do something similar and decided to write a small
> virtual table implementation to treatCSVfiles as just another table.
> This works for my uses which is to import Excel and OpenOffice files.
>
> I cleaned up the code a bit (removed non-english comments and some use
> of functions from internal libraries), add a sqlite3_extension_init()
> and posted the zip for the sqlite3 extension module on the wiki [1].
>
> [1]http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles
>
> The code is used on both Win32 and Linux platforms, but this zip only
> includes a makefile for Linux. It uses iconv() to handle character set
> conversions, so that must be available if compiling for windows (or just
>   make sure the source text file is already UTF-8).

Could you please provide some info as to how you would build this as
an extension module on Linux?

Thanks

--Emyr
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[P Kishor]

On Mon, Sep 28, 2009 at 12:13 PM, Petite Abeille
 wrote:
>
> On Sep 27, 2009, at 7:35 PM, Nuno Lucas wrote:
>
>> Some time ago I had to do something similar and decided to write a
>> small
>> virtual table implementation to treat CSV files as just another table.
>
> Very nice :)
>
> Similar to Oracle's "external table":
>
> "External Tables"
> http://www.orafaq.com/node/848


or, alternatively http://search.cpan.org/~jzucker/DBD-CSV-0.22/lib/DBD/CSV.pm



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Petite Abeille]

On Sep 27, 2009, at 7:35 PM, Nuno Lucas wrote:

> Some time ago I had to do something similar and decided to write a  
> small
> virtual table implementation to treat CSV files as just another table.

Very nice :)

Similar to Oracle's "external table":

"External Tables"
http://www.orafaq.com/node/848
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[C. Mundi]

@Nuno: hey that is really nice.  Thank you!

On 9/27/09, Nuno Lucas  wrote:
> C. Mundi wrote:
>> I'm hoping someone will (please) tell me I missed something in the sqlite
>> docs.  Otherwise, I guess I'll be using python's csv module to turn my CSV
>> file into SQL insert statements.  This is likely to be an infequent task,
>> but it has to be done perfectly.  So if someone knows of a command-line
>> tool
>> to turn CSV into SQL inserts, I would appreciate that too.
>
> Some time ago I had to do something similar and decided to write a small
> virtual table implementation to treat CSV files as just another table.
> This works for my uses which is to import Excel and OpenOffice files.
>
> I cleaned up the code a bit (removed non-english comments and some use
> of functions from internal libraries), add a sqlite3_extension_init()
> and posted the zip for the sqlite3 extension module on the wiki [1].
>
> [1] http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles
>
> The code is used on both Win32 and Linux platforms, but this zip only
> includes a makefile for Linux. It uses iconv() to handle character set
> conversions, so that must be available if compiling for windows (or just
>   make sure the source text file is already UTF-8).
>
> After loading the extension, it can be used like this:
>
>   *  CREATE VIRTUAL TABLE [.]
>   *  USING CSVFILE(  [,  [, ] ] );
>   *
>   *  is the character set name to pass to iconv(). The default
>   *  character set is "CP1252" (Windows Western Europe).
>   *  is a string where the first character is the csv file field
>   *  delimiter and the second character is the string delimiter.
>   *  The default field delimiter is the ";" character and the
>   *  default string delimiter is the "\"" character.
>
> Another peculiarity is that it uses the first row fields as the column
> names, so it may need some tweaking if that is not what you need.
>
> A sample session:
>
> SQLite version 3.6.18
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .load './virtcsv.so'
> sqlite> CREATE VIRTUAL TABLE csv USING csvfile('test.csv');
> sqlite> .s
> CREATE VIRTUAL TABLE csv USING csvfile('test.csv');
> sqlite> .mode col
> sqlite> .h 1
> sqlite> select * from csv;
> col1col2col3
> --  --  --
> 123324234   124342  342342
> 232 fsdfsdfsd   erwe32
>
>
> Regards,
> ~Nuno Lucas
>
>>
>> Thanks,
>> Carlos
>
> ___
> 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

Re: [sqlite] Tedious CSV import question

[Nuno Lucas]

C. Mundi wrote:
> I'm hoping someone will (please) tell me I missed something in the sqlite
> docs.  Otherwise, I guess I'll be using python's csv module to turn my CSV
> file into SQL insert statements.  This is likely to be an infequent task,
> but it has to be done perfectly.  So if someone knows of a command-line tool
> to turn CSV into SQL inserts, I would appreciate that too.

Some time ago I had to do something similar and decided to write a small 
virtual table implementation to treat CSV files as just another table.
This works for my uses which is to import Excel and OpenOffice files.

I cleaned up the code a bit (removed non-english comments and some use 
of functions from internal libraries), add a sqlite3_extension_init() 
and posted the zip for the sqlite3 extension module on the wiki [1].

[1] http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

The code is used on both Win32 and Linux platforms, but this zip only 
includes a makefile for Linux. It uses iconv() to handle character set 
conversions, so that must be available if compiling for windows (or just 
  make sure the source text file is already UTF-8).

After loading the extension, it can be used like this:

  *  CREATE VIRTUAL TABLE [.]
  *  USING CSVFILE(  [,  [, ] ] );
  *
  *  is the character set name to pass to iconv(). The default
  *  character set is "CP1252" (Windows Western Europe).
  *  is a string where the first character is the csv file field
  *  delimiter and the second character is the string delimiter.
  *  The default field delimiter is the ";" character and the
  *  default string delimiter is the "\"" character.

Another peculiarity is that it uses the first row fields as the column 
names, so it may need some tweaking if that is not what you need.

A sample session:

SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load './virtcsv.so'
sqlite> CREATE VIRTUAL TABLE csv USING csvfile('test.csv');
sqlite> .s
CREATE VIRTUAL TABLE csv USING csvfile('test.csv');
sqlite> .mode col
sqlite> .h 1
sqlite> select * from csv;
col1col2col3
--  --  --
123324234   124342  342342
232 fsdfsdfsd   erwe32


Regards,
~Nuno Lucas

> 
> Thanks,
> Carlos

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[A. H. Ongun]

I recently had to import an Excel based database into an SQL database.  To 
complicate the matters data was all over the place in each file (3 separate 
tables in a single CSV file), and some numbers had commas (e.g. 8,253.45).

I used the Perl CSV module to read in each file (1500+) line at a time, get rid 
of the commas, and account for empty records/columns, and build the SQL insert 
statement for each table's data, and write all out to a single file of SQL 
statements.  Afterward, just ran the SQL statements against the database.

Using the CSV module to handle the heavy lifting made it trivial.  The most 
challenging part of the whole thing was to figure out the line numbers, and 
column numbers of where the data was, used Vim for that :).

andy



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Joe Bennett]

Hi,

Please send me a copy if you would. Been looking at doing this exact same thing!



-Joe


On Thu, Sep 24, 2009 at 7:42 PM, C. Mundi  wrote:
> On Thu, Sep 24, 2009 at 12:37 PM, C. Mundi  wrote:
>
>>
>> Hi.  I have scanned the list archives and Googled.  I may have missed
>> something, but what I found was not sufficiently general or robust for my
>> needs.
>>
>> Happily, sqlite has a very nice .output mode for CSV.  It correctly
>> double-quotes fields when they contain a space or a comma, and it correctly
>> 'escapes' double-quote literals as "".  Great!  I need to go the other way.
>>
>> I don't see an inverse .import mode in sqlite to handle the completely
>> general CSV syntax.  As anyone who has ever tried knows, parsing compliant
>> CSV is a lot harder than writing compliant CSV.
>>
>> I'm hoping someone will (please) tell me I missed something in the sqlite
>> docs.  Otherwise, I guess I'll be using python's csv module to turn my CSV
>> file into SQL insert statements.  This is likely to be an infequent task,
>> but it has to be done perfectly.  So if someone knows of a command-line tool
>> to turn CSV into SQL inserts, I would appreciate that too.
>>
>> Thanks,
>> Carlos
>>
>
> Following up my own post:
>
> I just bit the bullet and did it.  Python has an excellent csv module,
> capable of handling just about any dialect you're likely to encounter.  I am
> so grateful I did not have to write a parser for CSV.  In just a few lines I
> can read the csv right into sqlite.  If anyone wants the code I will post it
> here if deemed appropriate.
>
> Carlos
> ___
> 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

Re: [sqlite] Tedious CSV import question

[Petite Abeille]

On Sep 25, 2009, at 9:03 PM, Wilson, Ronald wrote:

> Yeah.  The clearest thing in the RFC is the ABNF grammar.  However,  
> even
> that leaves out common cases like white space outside of quoted  
> fields,
> which most people would expect to be trimmed.  Also, I think most  
> people
> would expect leading/trailing white space to be trimmed, even in
> unquoted fields.

Hmmm... this sounds like mission creep... simply handling the RFC  
would be a major step forward for humanity (or at least for the import  
function)... the rest can be left to the user discretion...

> I think I'll break out my flex/lemon tools tonight and make a CSV
> parser.

Cool :)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[BareFeet]

On 26/09/2009, at 3:33 AM, Wilson, Ronald wrote:

> the RFC says that everything between the commas is supposed to be  
> part of the field, including white space.  Normally I trim the white  
> space unless it's quoted.

You can certainly offer the option to trim whitespace or change case  
or correct spelling mistakes, but that's after doing the CSV import  
technically correctly.

> Still, the RFC does not address how to handle rows like this:
>
> 1234,abc"123",abc
> 1235,""123,abc
>
> What are you supposed to do with those?  It is not clear.

You should generate an error, in the same way as you would generate an  
error if an XML tag missed a closing tag or if SQL was missing a  
closing bracket before the end of line. All syntax has definitions  
within which the data must conform or be rejected.

> Also, are you supposed to strip the quotes upon consuming the field?

Yes, in the same way as you strip the XML tags when pulling XML data  
into an array.

> Are you supposed to un-escape escaped quotes?

Yes, that's the point.

> "1234" -> 1234 or "1234" ?

1234

> "15""" -> 15" or 15"" or "15""" or "15"" ?

15"

> Seems to me if you strip quotes, you have to un-escape any escaped  
> quotes in the field.

Correct.

> Then there is the matter of white space outside the quotes.  The RFC  
> seems silent on all these issues, though the ABNF grammar implies  
> that white space outside quotes is not tolerated, which could lead  
> to considerable user surprise.

Technically:

1234,abc"123",abc

does not conform to CSV, as answered above. Similarly:

1234,   "123",abc

does not conform, but I think most importers will tolerate white space  
outside the quotes and ignore it.

Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[John Machin]

On 26/09/2009 5:38 AM, Petite Abeille wrote:
> On Sep 25, 2009, at 9:21 PM, C. Mundi wrote:
> 
>> Your post neatly articulates virtually every facet of this issue.
>> Thank you.  I wish we could get everyone to stop using csv.  I hate to
>> look at xml but I often wish everyone would use it instead of csv.
> 
> In fact, in Switzerland, there is a federal law mandating the use of  
> XML 5NF for all data transmission, irrespectively of their purpose.  
> Namespaces have to be properly encoded in the 4 officially recognized  
> languages.

"encoded in a language" is a novel concept. In how many? All 4? Or one 
of the sender's choice? Sending all of your data out with namespaces 
"encoded" only in Romansch seems like a brilliant idea :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[C. Mundi]

Cool.  Where do I send my application (for citizenship)? :)

Carlos

On 9/25/09, Petite Abeille  wrote:
>
> On Sep 25, 2009, at 9:21 PM, C. Mundi wrote:
>
>> Your post neatly articulates virtually every facet of this issue.
>> Thank you.  I wish we could get everyone to stop using csv.  I hate to
>> look at xml but I often wish everyone would use it instead of csv.
>
> In fact, in Switzerland, there is a federal law mandating the use of
> XML 5NF for all data transmission, irrespectively of their purpose.
> Namespaces have to be properly encoded in the 4 officially recognized
> languages.
>
> ___
> 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

Re: [sqlite] Tedious CSV import question

[John Machin]

On 26/09/2009 5:03 AM, Wilson, Ronald wrote:
>
> 
> Yeah.  The clearest thing in the RFC is the ABNF grammar.  However, even
> that leaves out common cases like white space outside of quoted fields,
> which most people would expect to be trimmed.  Also, I think most people
> would expect leading/trailing white space to be trimmed, even in
> unquoted fields.

No, thanks.

(1) Whitespace (space? CR? TAB? LF? NBSP??) outside of a quoted string 
is a violation of the writing rules for de-facto standard CSV. If the 
writer has used a different set of rules, then the reader should use 
that set of rules.

(2) Trimming leading/trailing whitespace from unquoted fields? Excuse 
me, if CSV is the only format I can get data in, I don't want any 
wiseguy messing with it -- I want to see what exactly is in the data. Do 
XML parsers strip whitespace? Stripping leading/trailing whitespace (and 
compressing whitespace runs into a single space) should be /optional/ 
functionality to be applied (if desired) /after/ input, irrespective of 
the input format.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Dan Bishop]

C. Mundi wrote:
> On 9/25/09, Jay A. Kreibich  wrote:
>   
>> ...
>>
>>   CSV is a great quick and dirty format to move data.  But it isn't
>>   "simple" and it isn't nearly as universal as many assume.  It works
>>   great if you're just moving simple numbers and strings that don't
>>   include commas, but becomes a mess when you get into exceptions.
>>
>>   Personally, I'd rather have the SQLite team working on core database
>>   features than trying to build a better CSV parser.  The problem
>>   is non-trivial and borders on unobtainable and, as Carlos
>>   proved so clearly, there are better, easier, faster ways.
>> 
> Jay...
>
> Your post neatly articulates virtually every facet of this issue.
> Thank you.  I wish we could get everyone to stop using csv.  I hate to
> look at xml but I often wish everyone would use it instead of csv.  I
> would hate to see any of the sqlite core devs  waste time on csv.
>
> Carlos
>   

But XML has its own difficulties with SQL data sets.  For example, how 
do you distinguish between NULL and the empty string?  And then there's 
the obvious inefficiency in writing the column name twice for each row.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Simon Slavin]

On 26 Sep 2009, at 1:40am, BareFeet wrote:

> As above, I think it is pretty simple. The complexity arrives when
> people expect CSV to provide more than a simple array of strings.

One of my free programs has to read CSV files  (

http://www.hearsay.demon.co.uk/mac/MacGarminTools/csv2gpx.html

if you care).  The documentation for the program doesn't mention any  
of the bizarre things people expect to work when they use it.  For  
about a year after I released the program I got the occasional email  
from people who had been trying to use it with a CSV file which JUST  
SHOULD WORK, DAMMIT.  They had all sorts of bizarre things in their  
files, from methods of escaping characters which I'd never heard of,  
including return characters in fields (For describing GPS points ?   
You really need more than one line of text ?), and marking header and  
comment lines in all sorts of weird ways.  Quite a lot of them could  
point to some big data publisher which was using their weird format as  
if it was well-known.  Also some files are in Latin 1 and some are in  
UTF-8.

Consequently, my software deals with all sorts of weird things in CSV  
files and very few of them are mentioned in the documentation.  I have  
no doubt that much commercial software does the same thing: contains  
tweaks wanted by one user which would be too ridiculous to document  
and create too many opportunities for requests for backward  
compatibility.

I'm fortunate that that software is seldom used these days, now that  
the manufacturer supplies software which performs that function.  I  
suspect that three years later I'd still be getting requests to  
include a new CSV dodge.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[BareFeet]

On 26/09/2009, at 12:00 AM, Jay A. Kreibich wrote:

> On Fri, Sep 25, 2009 at 10:24:15AM +1000, BareFeet scratched on the  
> wall:
>
>> In reality, in the thousands of CSV files I've dealt with
>> over the years, they all follow the same standard:
>>
>> 1. Rows are delimited by a new line (return and/or line feed).
>> 2. Columns are delimited by a comma.
>> 3. "Quoted text" is treated as one value item, including any commas  
>> or
>> new lines within it.
>> 4. A double quote "" is used to put a quote within quotes.
>>
>> That's it.
>
> This is more or less the standard put forth by RFC 4180.  And if  
> this is all you've encountered

Yes.

> you're not using very many different applications

I guess "many" is subjective but I would say I have used many,  
including Tandem main frame outputs, SQL server outputs, various  
product list suppliers, Excel Exports, electron microscope date, etc.  
I don't doubt that you've dealt with some strange CSV files, but I  
think the number of files defined as "strange" reduces if you better  
define the CSV format.

Please understand that I am not arguing that CSV should be used. But  
the fact is that CSV is a commonly used data export/import format (I'd  
say around 50%) and therefore supporting it is a significant  
requirement.

> or you're primarily dealing with numbers and simple strings that  
> don't contain quotes or commas.  CSV works very very well if you  
> never get into the question of escapes, but details count.

As I mentioned previously in points 3 and 4 above, I do deal with  
strings that contain quotes and commas. Dealing with them is well  
defined in practical use (or my experience of it).

> Reading the RFC only proves my point.  SQLite v3 is older than that  
> doc, and it pretty much admits the cat was out of the bag a long  
> time ago.  There are a ton of optional and might/may/could sections  
> that event

I accept that some earlier CSV implementations varied and that the RFC  
may have been in catch up mode. But I summarise that as a delay in  
setting the standard and thankfully not so much a problem with  
everyday use since most/all major current implementations use the  
specifications I outlined above (4 point summary).

> the format they define has a lot of holes in it

I don't think there are holes, within the scope of the data with which  
it deals. For instance, CSV doesn't deal with blob data, pictures,  
related tables, column type (even the distinction between a numeric  
and text value) and therefore has no definition for it.

> (i.e. headers, or no headers?).

Again, as I understand it, headers are outside of the CSV scope.

CSV only defines an array of cells of string data, not column  
definitions. If headers are included within CSV they are just another  
row in the array. CSV doesn't know what they are but an importer  
should import them into the array. After importing the raw data into  
an array, the program can then interpret the data in ways that makes  
sense to it. If the program understands headers then it should look  
for them in the array. If it understands column types, it should apply  
them, etc.

Since we're coming from the SQLite perspective, data means more to us  
than what CSV defines. Concepts such as column type, cell type are  
everyday definitions for SQLite but are meaningless to CSV. In most  
cases, we pre-define a table within SQLite specifically for receiving  
data from a particular CSV data file. CSV defines just the array of  
data.

>> Everything I've seen uses this.
>
> According to the RFC Excel doesn't use double-quotes for anything.  
> You might not care about Excel, but I'm willing to bet it is one of  
> the most-- if not the most-- common exporters of CSV.  The question  
> of getting data from Excel into SQLite shows up on the list every  
> now and then.

Yes, Excel is a major consideration and I have to deal with data from  
Excel exporters often. In my experience, though, the specification I  
outlined above deals with Excel exports fine. I can't comment on the  
RFC which may or may not match common use.

>> Some don't need delimiters in values, so they don't need quotes,  
>> but the encompassing specification works for all cases.
>
> No, it doesn't.  Working on a large website that provided CSV  
> exports for a number of data sources, I've seen plenty of examples  
> that don't work.  Finding a common format that could be exported  
> into a handful of common desktop apps was so pointless we seriously  
> considered getting rid of CSV all together, because we got tired of  
> our users telling us how simple CSV was, and why couldn't we just do  
> this one thing differently so it would work on *their* application.

This surprises me. Did you cater for escapes as mentioned in points 3  
and 4 above? You mentioned:

> CSV works very very well if you never get into the question of escapes

So I wonder if you did.

Also, perhaps the users were asking for specific data within 

Re: [sqlite] Tedious CSV import question

[Fred Williams]

If you have a file that conforms fully to the RFC, I honestly don't see the
problem.  Your examples do not meet the RFC rules.  If the rule does not
address leading spaces outside the quoted text, then the input file should
not contain spaces there as they will be ignored.

Over the years I have written several CSV parsers in three of four different
languages at least and have never had a problem parsing a VALID CSV file.  I
have never used the SQLite command line utility for such so cannot judge its
functionally.  I use my own parser or someone else's'.

Fred

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Wilson, Ronald
Sent: Friday, September 25, 2009 1:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Tedious CSV import question


> > Still, the RFC does not
> > address how to handle rows like this:
> >
> > 1234,abc"123",abc
> > 1235,""123,abc
>
> Both violate the format as defined.  Generate an error message or
> interpret them any way that suits you.
>
> > What are you supposed to do with those?  It is not clear.  Also, are
> > you
> > supposed to strip the quotes upon consuming the field?  Are you
> > supposed
> > to un-escape escaped quotes?
> >
> > "1234" -> 1234 or "1234" ?
>
> 1234
>
> > "15""" -> 15" or 15"" or "15""" or "15"" ?
>
> 15"
>
> Simon.

I can appreciate common sense answers, but the RFC doesn't specify those
answers.  The RFC seems to only address the formatting of the CSV file,
not the consumption of it.
___
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

Re: [sqlite] Tedious CSV import question

[Petite Abeille]

On Sep 25, 2009, at 9:21 PM, C. Mundi wrote:

> Your post neatly articulates virtually every facet of this issue.
> Thank you.  I wish we could get everyone to stop using csv.  I hate to
> look at xml but I often wish everyone would use it instead of csv.

In fact, in Switzerland, there is a federal law mandating the use of  
XML 5NF for all data transmission, irrespectively of their purpose.  
Namespaces have to be properly encoded in the 4 officially recognized  
languages.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[C. Mundi]

Jay...

Your post neatly articulates virtually every facet of this issue.
Thank you.  I wish we could get everyone to stop using csv.  I hate to
look at xml but I often wish everyone would use it instead of csv.  I
would hate to see any of the sqlite core devs  waste time on csv.

Carlos


On 9/25/09, Jay A. Kreibich  wrote:
> On Fri, Sep 25, 2009 at 10:24:15AM +1000, BareFeet scratched on the wall:
>
>
>> In reality, in the thousands of CSV files I've dealt with
>> over the years, they all follow the same standard:
>>
>> 1. Rows are delimited by a new line (return and/or line feed).
>> 2. Columns are delimited by a comma.
>> 3. "Quoted text" is treated as one value item, including any commas or
>> new lines within it.
>> 4. A double quote "" is used to put a quote within quotes.
>>
>> That's it.
>
>   This is more or less the standard put forth by RFC 4180.  And if this
>   is all you've encountered, you're not using very many different
>   applications or you're primarily dealing with numbers and simple
>   strings that don't contain quotes or commas.  CSV works very very
>   well if you never get into the question of escapes, but details count.
>
>   Reading the RFC only proves my point.  SQLite v3 is older than that
>   doc, and it pretty much admits the cat was out of the bag a long time
>   ago.  There are a ton of optional and might/may/could sections that
>   event the format they define has a lot of holes in it (i.e. headers,
>   or no headers?).
>
>> Everything I've seen uses this.
>
>   According to the RFC Excel doesn't use double-quotes for anything.
>   You might not care about Excel, but I'm willing to bet it is one of
>   the most-- if not the most-- common exporters of CSV.  The question
>   of getting data from Excel into SQLite shows up on the list every now
>   and then.
>
>> Some don't need delimiters
>> in values, so they don't need quotes, but the encompassing
>> specification works for all cases.
>
>   No, it doesn't.  Working on a large website that provided CSV exports
>   for a number of data sources, I've seen plenty of examples that don't
>   work.  Finding a common format that could be exported into a handful
>   of common desktop apps was so pointless we seriously considered
>   getting rid of CSV all together, because we got tired of our users
>   telling us how simple CSV was, and why couldn't we just do this one
>   thing differently so it would work on *their* application.
>
>> It's not that big a deal for SQLite to support it, so it should.
>
>   If it is so simple, and you know where the code is...
>
>   This is, perhaps, the biggest fallacy of CSV... people think it
>   is a "simple" format (it isn't), and assume that code support to
>   "correctly" (whatever that is) read it is simple.  It isn't.  The RFC
>   has a formal grammar that requires over a dozen elements to define!
>
>   Most people setting out to build a CSV reader never think to use a
>   full grammar and parser-- after all, it is such a "simple" format--
>   and find themselves in a mess of code soon enough.  Seriously, give
>   it a try.
>
>   Carlos's Python script (nice!) is a great example.  His comment "I am
>   so grateful I did not have to write a parser for CSV" is dead on.
>   And, as he points out, the reason the Python module is so good is
>   that it is adaptive, and really reads five or six different variants
>   of CSV (something a reader can do but a writer cannot).  He was
>   also able to clobber it all together in a few hours or less (because
>   someone else spent a few hundred hours on the CSV module), further
>   proving that advanced support of this kind of thing is really outside
>   of the scope of SQLite3.  After all, the .import command is part of
>   the shell, not part of the core library.
>
>
>
>
>   CSV is a great quick and dirty format to move data.  But it isn't
>   "simple" and it isn't nearly as universal as many assume.  It works
>   great if you're just moving simple numbers and strings that don't
>   include commas, but becomes a mess when you get into exceptions.
>
>   Personally, I'd rather have the SQLite team working on core database
>   features than trying to build a better CSV parser.  The problem
>   is non-trivial and borders on unobtainable and, as Carlos
>   proved so clearly, there are better, easier, faster ways.
>
>-j
>
>
> --
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We have
>  a protractor."   "I'll go home and see if I can scrounge up a ruler
>  and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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

Re: [sqlite] Tedious CSV import question

[Wilson, Ronald]

> > I read the RFC last night
> 
> Oh, my...
> 
> Programming in Lua has a nice, concise example regarding CSV parsing
> (near the end of the page):
> 
> http://www.lua.org/pil/20.4.html
> 
> Quote:
> 
> To break a CSV into an array is more difficult, because we must avoid
> mixing up the commas written between quotes with the commas that
> separate fields. We could try to escape the commas between quotes.
> However, not all quote characters act as quotes; only quote characters
> after a comma act as a starting quote, as long as the comma itself is
> acting as a comma (that is, it is not between quotes). There are too
> many subtleties. For instance, two quotes may represent a single
> quote, two quotes, or nothing:
> 
> "hello""hello", "",""
> 
> Clear as mud? :D

Yeah.  The clearest thing in the RFC is the ABNF grammar.  However, even
that leaves out common cases like white space outside of quoted fields,
which most people would expect to be trimmed.  Also, I think most people
would expect leading/trailing white space to be trimmed, even in
unquoted fields.

I think I'll break out my flex/lemon tools tonight and make a CSV
parser.

RW 

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division
assuredcommunications(tm)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Petite Abeille]

On Sep 25, 2009, at 7:33 PM, Wilson, Ronald wrote:

> I read the RFC last night

Oh, my...

Programming in Lua has a nice, concise example regarding CSV parsing  
(near the end of the page):

http://www.lua.org/pil/20.4.html

Quote:

To break a CSV into an array is more difficult, because we must avoid  
mixing up the commas written between quotes with the commas that  
separate fields. We could try to escape the commas between quotes.  
However, not all quote characters act as quotes; only quote characters  
after a comma act as a starting quote, as long as the comma itself is  
acting as a comma (that is, it is not between quotes). There are too  
many subtleties. For instance, two quotes may represent a single  
quote, two quotes, or nothing:

"hello""hello", "",""

Clear as mud? :D


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Wilson, Ronald]

> > Still, the RFC does not
> > address how to handle rows like this:
> >
> > 1234,abc"123",abc
> > 1235,""123,abc
> 
> Both violate the format as defined.  Generate an error message or
> interpret them any way that suits you.
> 
> > What are you supposed to do with those?  It is not clear.  Also, are
> > you
> > supposed to strip the quotes upon consuming the field?  Are you
> > supposed
> > to un-escape escaped quotes?
> >
> > "1234" -> 1234 or "1234" ?
> 
> 1234
> 
> > "15""" -> 15" or 15"" or "15""" or "15"" ?
> 
> 15"
> 
> Simon.

I can appreciate common sense answers, but the RFC doesn't specify those
answers.  The RFC seems to only address the formatting of the CSV file,
not the consumption of it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Simon Slavin]

On 25 Sep 2009, at 6:33pm, Wilson, Ronald wrote:

> Still, the RFC does not
> address how to handle rows like this:
>
> 1234,abc"123",abc
> 1235,""123,abc

Both violate the format as defined.  Generate an error message or  
interpret them any way that suits you.

> What are you supposed to do with those?  It is not clear.  Also, are  
> you
> supposed to strip the quotes upon consuming the field?  Are you  
> supposed
> to un-escape escaped quotes?
>
> "1234" -> 1234 or "1234" ?

1234

> "15""" -> 15" or 15"" or "15""" or "15"" ?

15"

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Wilson, Ronald]

>   Reading the RFC only proves my point.  SQLite v3 is older than that
>   doc, and it pretty much admits the cat was out of the bag a long
time
>   ago.  There are a ton of optional and might/may/could sections that
>   event the format they define has a lot of holes in it (i.e. headers,
>   or no headers?).

I read the RFC last night and even took a stab at adding it to the
.import method in the sqlite command line.  But pretty quickly holes
appeared.  For example, the RFC says that everything between the commas
is supposed to be part of the field, including white space.  Normally I
trim the white space unless it's quoted.  Still, the RFC does not
address how to handle rows like this:

1234,abc"123",abc
1235,""123,abc

What are you supposed to do with those?  It is not clear.  Also, are you
supposed to strip the quotes upon consuming the field?  Are you supposed
to un-escape escaped quotes?
 
"1234" -> 1234 or "1234" ?
"15""" -> 15" or 15"" or "15""" or "15"" ?

Seems to me if you strip quotes, you have to un-escape any escaped
quotes in the field.  If you don't strip quotes, then you can't
un-escape anything in the field because it leads to bazaar edge cases.
Then there is the matter of white space outside the quotes.  The RFC
seems silent on all these issues, though the ABNF grammar implies that
white space outside quotes is not tolerated, which could lead to
considerable user surprise.

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division
assuredcommunications(tm)


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Simon Slavin]

On 25 Sep 2009, at 3:00pm, Jay A. Kreibich wrote:

>  Personally, I'd rather have the SQLite team working on core database
>  features than trying to build a better CSV parser

Yet strangely, the command-line tool is one of the best things about  
SQLite.  If I appear to find a bug in a SQLite library I try doing the  
same thing in the command-line tool.  If the behaviour is the same I  
know my software is correct and I study SQL.  If the behaviour is  
different i know the fault is in my programming or in the way I'm  
using the API.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Jay A. Kreibich]

On Fri, Sep 25, 2009 at 10:24:15AM +1000, BareFeet scratched on the wall:


> In reality, in the thousands of CSV files I've dealt with  
> over the years, they all follow the same standard:
> 
> 1. Rows are delimited by a new line (return and/or line feed).
> 2. Columns are delimited by a comma.
> 3. "Quoted text" is treated as one value item, including any commas or  
> new lines within it.
> 4. A double quote "" is used to put a quote within quotes.
> 
> That's it.

  This is more or less the standard put forth by RFC 4180.  And if this
  is all you've encountered, you're not using very many different
  applications or you're primarily dealing with numbers and simple
  strings that don't contain quotes or commas.  CSV works very very
  well if you never get into the question of escapes, but details count.

  Reading the RFC only proves my point.  SQLite v3 is older than that
  doc, and it pretty much admits the cat was out of the bag a long time
  ago.  There are a ton of optional and might/may/could sections that
  event the format they define has a lot of holes in it (i.e. headers,
  or no headers?).

> Everything I've seen uses this.

  According to the RFC Excel doesn't use double-quotes for anything.
  You might not care about Excel, but I'm willing to bet it is one of
  the most-- if not the most-- common exporters of CSV.  The question
  of getting data from Excel into SQLite shows up on the list every now
  and then.

> Some don't need delimiters  
> in values, so they don't need quotes, but the encompassing  
> specification works for all cases.

  No, it doesn't.  Working on a large website that provided CSV exports
  for a number of data sources, I've seen plenty of examples that don't
  work.  Finding a common format that could be exported into a handful
  of common desktop apps was so pointless we seriously considered
  getting rid of CSV all together, because we got tired of our users
  telling us how simple CSV was, and why couldn't we just do this one
  thing differently so it would work on *their* application.

> It's not that big a deal for SQLite to support it, so it should.

  If it is so simple, and you know where the code is...

  This is, perhaps, the biggest fallacy of CSV... people think it
  is a "simple" format (it isn't), and assume that code support to
  "correctly" (whatever that is) read it is simple.  It isn't.  The RFC
  has a formal grammar that requires over a dozen elements to define!

  Most people setting out to build a CSV reader never think to use a
  full grammar and parser-- after all, it is such a "simple" format--
  and find themselves in a mess of code soon enough.  Seriously, give
  it a try.

  Carlos's Python script (nice!) is a great example.  His comment "I am
  so grateful I did not have to write a parser for CSV" is dead on.
  And, as he points out, the reason the Python module is so good is
  that it is adaptive, and really reads five or six different variants
  of CSV (something a reader can do but a writer cannot).  He was
  also able to clobber it all together in a few hours or less (because
  someone else spent a few hundred hours on the CSV module), further
  proving that advanced support of this kind of thing is really outside
  of the scope of SQLite3.  After all, the .import command is part of
  the shell, not part of the core library.




  CSV is a great quick and dirty format to move data.  But it isn't
  "simple" and it isn't nearly as universal as many assume.  It works
  great if you're just moving simple numbers and strings that don't
  include commas, but becomes a mess when you get into exceptions. 

  Personally, I'd rather have the SQLite team working on core database
  features than trying to build a better CSV parser.  The problem
  is non-trivial and borders on unobtainable and, as Carlos
  proved so clearly, there are better, easier, faster ways.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[P Kishor]

On Thu, Sep 24, 2009 at 9:21 PM, C. Mundi  wrote:
> On Thu, Sep 24, 2009 at 7:16 PM,  wrote:
>
>> On Thu, 24 Sep 2009, Rich Shepard wrote:
>>
>> > On Thu, 24 Sep 2009, C. Mundi wrote:
>> >
>> >> I just bit the bullet and did it.  Python has an excellent csv module,
>> >> capable of handling just about any dialect you're likely to encounter.
>>  I
>> >> am so grateful I did not have to write a parser for CSV.  In just a few
>> >> lines I can read the csv right into sqlite.  If anyone wants the code I
>> >> will post it here if deemed appropriate.

and, since there is more than one way to do it, an alternative

http://search.cpan.org/~makamaka/Text-CSV-1.13/lib/Text/CSV.pm

>>
>> Please send me a copy.
>>
>> Chris
>>
>> 
>> Christopher F. Martin
>> School of Medicine
>> Center for Digestive Diseases & Nutrition
>> CB# 7555, 4104 Bioinformatics Bldg.
>> University of North Carolina at Chapel Hill
>> Chapel Hill, North Carolina 27599-7555
>> Phone: 919.966.9340       Fax: 919.966.7592
>> ~~~
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> Here you go, Chris.  Please note the disclaimer, which is as serious if
> somewhat overwrought.  I cannot offer support for this script, but I suspect
> you'll have no problem grokking it and improvements are always appreciated.
> Good luck!
>
> Carlos
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Dan Bishop]

C. Mundi wrote:
> Hi.  I have scanned the list archives and Googled.  I may have missed
> something, but what I found was not sufficiently general or robust for my
> needs.
>
> Happily, sqlite has a very nice .output mode for CSV.  It correctly
> double-quotes fields when they contain a space or a comma, and it correctly
> 'escapes' double-quote literals as "".  Great!  I need to go the other way.
>
> I don't see an inverse .import mode in sqlite to handle the completely
> general CSV syntax.  As anyone who has ever tried knows, parsing compliant
> CSV is a lot harder than writing compliant CSV.
>
> I'm hoping someone will (please) tell me I missed something in the sqlite
> docs.  Otherwise, I guess I'll be using python's csv module to turn my CSV
> file into SQL insert statements.  This is likely to be an infequent task,
> but it has to be done perfectly.  So if someone knows of a command-line tool
> to turn CSV into SQL inserts, I would appreciate that too.
Here's one I just wrote.

#!/usr/bin/env python

"""Utility to import CSV files into an SQLite database."""

from __future__ import division

import csv
import getopt
import os
import sqlite3
import sys

USAGE = 'USAGE: csv2sqlite [-o dbfile] csvfile...'
DEFAULT_OUTPUT_FILE = 'a.db'

_INTEGER_MAX = (1 << 63) - 1
_INTEGER_MIN = -_INTEGER_MAX - 1

def identify_type(value):
"""
Identify the SQLite type for a value.
"""
try:
int_value = int(value)
if int_value < _INTEGER_MIN or int_value > _INTEGER_MAX:
return 'REAL'
return 'INTEGER'
except ValueError:
try:
float(value)
return 'REAL'
except ValueError:
return 'TEXT'

def common_type(values):
"""
Return the common type for a list of values.
"""
types = set(identify_type(value) for value in values if value)
if len(types) == 1:
return types.pop()
elif types == set(['INTEGER', 'REAL']):
return 'NUMERIC'
else:
return 'TEXT'

def add_table_from_file(dbconn, filename):
"""
Read a CSV file and add it as a database table.

dbconn   -- open database connection
filename -- path to the CSV file
"""
table_name = os.path.splitext(os.path.basename(filename))[0]
data = list(csv.reader(open(filename)))
if not data:
return
headers = data.pop(0)
num_columns = len(headers)
# Replace all empty strings with NULL
data = [[value or None for value in row] for row in data]
# Ensure that all rows have the same number of columns
for row in data:
for dummy in xrange(num_columns - len(row)):
row.append(None)
# Determine the appropriate type for each column
column_info = [(column_name, common_type(row[col] for row in data))
   for (col, column_name) in enumerate(headers)]
# Build the SQL statements
sql_create_table = 'CREATE TABLE IF NOT EXISTS "%s" (%s)' % \
(table_name, ', '.join('%s %s' % column for column in column_info))
sql_insert = 'INSERT INTO "%s" VALUES (%s)' % \
(table_name, ', '.join(['?'] * num_columns))
# Build the database table
dbconn.execute(sql_create_table)
for row in data:
dbconn.execute(sql_insert, row)

def _main(argv=None):
"""
Executed when this module is run as a script.
"""
if argv is None:
argv = sys.argv
options, args = getopt.getopt(argv[1:], 'o:', ['help'])
options = dict(options)
if '--help' in options:
print USAGE
else:
dbconn = sqlite3.connect(options.get('-o', DEFAULT_OUTPUT_FILE))
for filename in args:
add_table_from_file(dbconn, filename)
dbconn.commit()
dbconn.close()

if __name__ == '__main__':
_main()

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[C. Mundi]

On Thu, Sep 24, 2009 at 7:16 PM,  wrote:

> On Thu, 24 Sep 2009, Rich Shepard wrote:
>
> > On Thu, 24 Sep 2009, C. Mundi wrote:
> >
> >> I just bit the bullet and did it.  Python has an excellent csv module,
> >> capable of handling just about any dialect you're likely to encounter.
>  I
> >> am so grateful I did not have to write a parser for CSV.  In just a few
> >> lines I can read the csv right into sqlite.  If anyone wants the code I
> >> will post it here if deemed appropriate.
>
> Please send me a copy.
>
> Chris
>
> 
> Christopher F. Martin
> School of Medicine
> Center for Digestive Diseases & Nutrition
> CB# 7555, 4104 Bioinformatics Bldg.
> University of North Carolina at Chapel Hill
> Chapel Hill, North Carolina 27599-7555
> Phone: 919.966.9340   Fax: 919.966.7592
> ~~~
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


Here you go, Chris.  Please note the disclaimer, which is as serious if
somewhat overwrought.  I cannot offer support for this script, but I suspect
you'll have no problem grokking it and improvements are always appreciated.
Good luck!

Carlos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[C. Mundi]

On Thu, Sep 24, 2009 at 6:45 PM, Rich Shepard wrote:

> On Thu, 24 Sep 2009, C. Mundi wrote:
>
> > I just bit the bullet and did it.  Python has an excellent csv module,
> > capable of handling just about any dialect you're likely to encounter.  I
> > am so grateful I did not have to write a parser for CSV.  In just a few
> > lines I can read the csv right into sqlite.  If anyone wants the code I
> > will post it here if deemed appropriate.
>
> Carlos,
>
>   How about sending me a copy?
>
> Thanks,
>
> Rich
>
> --
> Richard B. Shepard, Ph.D.   |  IntegrityCredibility
> Applied Ecosystem Services, Inc.|Innovation
>  Voice: 503-667-4517  Fax:
> 503-667-8863
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


Here you go, Rich.  Please note the disclaimer, which is as serious if
somewhat overwrought.  I cannot offer support for this script, but I suspect
you'll have no problem grokking it and improvements are always appreciated.
Good luck!

Carlos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[cmartin]

On Thu, 24 Sep 2009, Rich Shepard wrote:

> On Thu, 24 Sep 2009, C. Mundi wrote:
>
>> I just bit the bullet and did it.  Python has an excellent csv module,
>> capable of handling just about any dialect you're likely to encounter.  I
>> am so grateful I did not have to write a parser for CSV.  In just a few
>> lines I can read the csv right into sqlite.  If anyone wants the code I
>> will post it here if deemed appropriate.

Please send me a copy.

Chris


Christopher F. Martin
School of Medicine
Center for Digestive Diseases & Nutrition
CB# 7555, 4104 Bioinformatics Bldg.
University of North Carolina at Chapel Hill
Chapel Hill, North Carolina 27599-7555
Phone: 919.966.9340   Fax: 919.966.7592
~~~


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Rich Shepard]

On Thu, 24 Sep 2009, C. Mundi wrote:

> I just bit the bullet and did it.  Python has an excellent csv module,
> capable of handling just about any dialect you're likely to encounter.  I
> am so grateful I did not have to write a parser for CSV.  In just a few
> lines I can read the csv right into sqlite.  If anyone wants the code I
> will post it here if deemed appropriate.

Carlos,

   How about sending me a copy?

Thanks,

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[C. Mundi]

On Thu, Sep 24, 2009 at 12:37 PM, C. Mundi  wrote:

>
> Hi.  I have scanned the list archives and Googled.  I may have missed
> something, but what I found was not sufficiently general or robust for my
> needs.
>
> Happily, sqlite has a very nice .output mode for CSV.  It correctly
> double-quotes fields when they contain a space or a comma, and it correctly
> 'escapes' double-quote literals as "".  Great!  I need to go the other way.
>
> I don't see an inverse .import mode in sqlite to handle the completely
> general CSV syntax.  As anyone who has ever tried knows, parsing compliant
> CSV is a lot harder than writing compliant CSV.
>
> I'm hoping someone will (please) tell me I missed something in the sqlite
> docs.  Otherwise, I guess I'll be using python's csv module to turn my CSV
> file into SQL insert statements.  This is likely to be an infequent task,
> but it has to be done perfectly.  So if someone knows of a command-line tool
> to turn CSV into SQL inserts, I would appreciate that too.
>
> Thanks,
> Carlos
>

Following up my own post:

I just bit the bullet and did it.  Python has an excellent csv module,
capable of handling just about any dialect you're likely to encounter.  I am
so grateful I did not have to write a parser for CSV.  In just a few lines I
can read the csv right into sqlite.  If anyone wants the code I will post it
here if deemed appropriate.

Carlos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[BareFeet]

On 25/09/2009, at 7:47 AM, Jay A. Kreibich wrote:

> On Thu, Sep 24, 2009 at 09:12:20PM +0100, Simon Slavin scratched on  
> the wall:
>
>> The .import command does not handle quoted fields correctly, as has
>> recently been stated on this list.
>
>  I think it would be more fair to say that the .import command has
>  chosen not to support quoted fields.

>  The bigger issue is that CSV isn't really a format, but more of a
>  loose idea.  Yes, many systems that use CSV choose to assign meaning
>  to double quotations, but my first question to that is, "How do you  
> put
>  a double-quote into a value?"  Is it \" in a C-style escape, or maybe
>  double characters ("") like SQL?  If the answer is \", why not just
>  use \, in the first place?
>
>  There are no answers to these questions because there is no standard.

I see this said occasionally as a discussion on theory, but I  
disagree. In reality, in the thousands of CSV files I've dealt with  
over the years, they all follow the same standard:

1. Rows are delimited by a new line (return and/or line feed).
2. Columns are delimited by a comma.
3. "Quoted text" is treated as one value item, including any commas or  
new lines within it.
4. A double quote "" is used to put a quote within quotes.

That's it. Everything I've seen uses this. Some don't need delimiters  
in values, so they don't need quotes, but the encompassing  
specification works for all cases.

It's not that big a deal for SQLite to support it, so it should.

Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Petite Abeille]

On Sep 24, 2009, at 11:47 PM, Jay A. Kreibich wrote:

> The bigger issue is that CSV isn't really a format, but more of a  
> loose idea.

Right, that said, sticking to RFC 4180 is not such a bad bet:
Common Format and MIME Type for Comma-Separated Values (CSV) Files
http://www.rfc-editor.org/rfc/rfc4180.txt

At least, you can always point people back to some kind of  
specification :)

My 2¢.

Cheers,

--
PA.

http://alt.textdrive.com/nanoki/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Jay A. Kreibich]

On Thu, Sep 24, 2009 at 09:12:20PM +0100, Simon Slavin scratched on the wall:

> The .import command does not handle quoted fields correctly, as has  
> recently been stated on this list.  

  I think it would be more fair to say that the .import command has
  chosen not to support quoted fields.  There is a big difference
  between not doing something and not doing something correctly.

  The bigger issue is that CSV isn't really a format, but more of a
  loose idea.  Yes, many systems that use CSV choose to assign meaning
  to double quotations, but my first question to that is, "How do you put
  a double-quote into a value?"  Is it \" in a C-style escape, or maybe
  double characters ("") like SQL?  If the answer is \", why not just
  use \, in the first place?

  There are no answers to these questions because there is no standard.
  You can't even use the modern defacto of asking, "What does Microsoft
  do?"  Different versions of Excel (nevermind different products) have
  different answers to some of these questions.  Heck, IIRC, there are
  cases when the *same* version of Excel on different platforms has 
  different answers.

  It is a hard problem, but I suppose the real answer-- one that has
  been around since mainframes started to spit out CSV files-- is you
  need to either A) avoid CSV files, B) don't use commas and accept you
  can't put your deliminator into values, or C) assume you must
  massage the data if you're moving CSV files from product one to
  product two.

  But blaming the product is kind of pointless because their all
  different.  You might as well blame whatever exported the data for
  using the non-standard quote extension to CSV.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Jimmy Verner]

I had a lot of trouble with trying to import into SQL Manager from csv  
files.  Per some earlier advice, I began inputting through the console  
instead.  But I did figure out a way to cut down on the time.  I've  
been making cookie-cutter iPhone apps (a series of rules of legal  
procedure where the rules are in a sqlite database) so what I did was  
make a template of sorts.  In the left column is the INSERT INTO etc.  
In the middle column is the text I wish to insert. In the right column  
is the ');  When finished, I collapsed the table (plus I had to get  
rid of unwanted returns), then copied the whole thing and pasted it  
into the input box (or whatever it's called) on the console.  Works  
like a charm.  Then I look for error messages and fix things until  
there are no more errors. For the next app, I tinker with the template  
to make any changes required for the new data, then paste the new data  
into the middle column and off we go again.


I've attached a file with a few rows so you can see what I'm talking  
about.


Jimmy Verner
www.vernerlegal.com





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Fred Williams]

Try this:

http://sqliteadmin.orbmu2k.de/

Works great for me.

Fred

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of J Glassy
Sent: Thursday, September 24, 2009 3:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Tedious CSV import question


On c.Mundi's CSV inquiry,
  One way I (interactively) address the need to import CSV data into sqlite
databases is
to use Mrinal Kant's SQL Manager add-in. This offers what I believe are more
robust facilities for performing this type of bulk import. I'm not sure if
SQL Manager is an option for you, but give it a try if you can get by
without a strictly console level scripted solution.

joe

On Thu, Sep 24, 2009 at 2:12 PM, Simon Slavin
<slav...@hearsay.demon.co.uk>wrote:

>
> On 24 Sep 2009, at 8:37pm, C. Mundi wrote:
>
> > Happily, sqlite has a very nice .output mode for CSV.  It correctly
> > double-quotes fields when they contain a space or a comma, and it
> > correctly
> > 'escapes' double-quote literals as "".  Great!  I need to go the
> > other way.
> >
> > I don't see an inverse .import mode in sqlite to handle the completely
> > general CSV syntax.  As anyone who has ever tried knows, parsing
> > compliant
> > CSV is a lot harder than writing compliant CSV.
>
> Sorry, but you didn't miss anything, no such facility exists.
> The .import command does not handle quoted fields correctly, as has
> recently been stated on this list.  Some third-party tools do it fine
> but if you want to import your particular file you'll have to tweak
> your file so that whatever you're using as a separator never appears
> inside a field.
>
> Simon.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Joe Glassy
Research Analyst/Programmer
University of Montana NSF EPSCoR Program
Davidson Honors College Room 013
Missoula, MT 59812
___
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

Re: [sqlite] Tedious CSV import question

[J Glassy]

On c.Mundi's CSV inquiry,
  One way I (interactively) address the need to import CSV data into sqlite
databases is
to use Mrinal Kant's SQL Manager add-in. This offers what I believe are more
robust facilities for performing this type of bulk import. I'm not sure if
SQL Manager is an option for you, but give it a try if you can get by
without a strictly console level scripted solution.

joe

On Thu, Sep 24, 2009 at 2:12 PM, Simon Slavin
wrote:

>
> On 24 Sep 2009, at 8:37pm, C. Mundi wrote:
>
> > Happily, sqlite has a very nice .output mode for CSV.  It correctly
> > double-quotes fields when they contain a space or a comma, and it
> > correctly
> > 'escapes' double-quote literals as "".  Great!  I need to go the
> > other way.
> >
> > I don't see an inverse .import mode in sqlite to handle the completely
> > general CSV syntax.  As anyone who has ever tried knows, parsing
> > compliant
> > CSV is a lot harder than writing compliant CSV.
>
> Sorry, but you didn't miss anything, no such facility exists.
> The .import command does not handle quoted fields correctly, as has
> recently been stated on this list.  Some third-party tools do it fine
> but if you want to import your particular file you'll have to tweak
> your file so that whatever you're using as a separator never appears
> inside a field.
>
> Simon.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Joe Glassy
Research Analyst/Programmer
University of Montana NSF EPSCoR Program
Davidson Honors College Room 013
Missoula, MT 59812
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Simon Slavin]

On 24 Sep 2009, at 8:37pm, C. Mundi wrote:

> Happily, sqlite has a very nice .output mode for CSV.  It correctly
> double-quotes fields when they contain a space or a comma, and it  
> correctly
> 'escapes' double-quote literals as "".  Great!  I need to go the  
> other way.
>
> I don't see an inverse .import mode in sqlite to handle the completely
> general CSV syntax.  As anyone who has ever tried knows, parsing  
> compliant
> CSV is a lot harder than writing compliant CSV.

Sorry, but you didn't miss anything, no such facility exists.   
The .import command does not handle quoted fields correctly, as has  
recently been stated on this list.  Some third-party tools do it fine  
but if you want to import your particular file you'll have to tweak  
your file so that whatever you're using as a separator never appears  
inside a field.

Simon.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tedious CSV import question

[Wilson, Ronald]

Sadly, the sqlite3 command line does not handle quoted fields when importing 
CSV very well at all.  Commas inside quotes get treated as column separators, 
and newlines inside quotes get treated as row separators.  I had this problem 
earlier this year and ended up using excel to create insert statements.

I don't recall this feature being fixed since then; correct me if I'm wrong.  I 
haven't had time to fix it myself either, so I don't complain.

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division 
assuredcommunications(tm)


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of C. Mundi
> Sent: Thursday, September 24, 2009 3:37 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Tedious CSV import question
> 
> 
> Hi.  I have scanned the list archives and Googled.  I may have missed
> something, but what I found was not sufficiently general or robust for
> my
> needs.
> 
> Happily, sqlite has a very nice .output mode for CSV.  It correctly
> double-quotes fields when they contain a space or a comma, and it
> correctly
> 'escapes' double-quote literals as "".  Great!  I need to go the other
> way.
> 
> I don't see an inverse .import mode in sqlite to handle the completely
> general CSV syntax.  As anyone who has ever tried knows, parsing
> compliant
> CSV is a lot harder than writing compliant CSV.
> 
> I'm hoping someone will (please) tell me I missed something in the
> sqlite
> docs.  Otherwise, I guess I'll be using python's csv module to turn my
> CSV
> file into SQL insert statements.  This is likely to be an infequent
> task,
> but it has to be done perfectly.  So if someone knows of a command-line
> tool
> to turn CSV into SQL inserts, I would appreciate that too.
> 
> Thanks,
> Carlos
> ___
> 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

[sqlite] Tedious CSV import question

[C. Mundi]

Hi.  I have scanned the list archives and Googled.  I may have missed
something, but what I found was not sufficiently general or robust for my
needs.

Happily, sqlite has a very nice .output mode for CSV.  It correctly
double-quotes fields when they contain a space or a comma, and it correctly
'escapes' double-quote literals as "".  Great!  I need to go the other way.

I don't see an inverse .import mode in sqlite to handle the completely
general CSV syntax.  As anyone who has ever tried knows, parsing compliant
CSV is a lot harder than writing compliant CSV.

I'm hoping someone will (please) tell me I missed something in the sqlite
docs.  Otherwise, I guess I'll be using python's csv module to turn my CSV
file into SQL insert statements.  This is likely to be an infequent task,
but it has to be done perfectly.  So if someone knows of a command-line tool
to turn CSV into SQL inserts, I would appreciate that too.

Thanks,
Carlos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users