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
