@Nuno: hey that is really nice. Thank you!
On 9/27/09, Nuno Lucas <ntlu...@gmail.com> 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 [<database>.]<table_name> > * USING CSVFILE( <filename> [, <charset> [, <delims>] ] ); > * > * <charset> is the character set name to pass to iconv(). The default > * character set is "CP1252" (Windows Western Europe). > * <delims> 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; > col1 col2 col3 > ---------- ---------- ---------- > 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
