I use SQLite extensively as a data source in Excel and have never come across this problem. Is use Olaf Schmidt's VB wrapper vbRichClient4 and vb_cairo_sqlite. If you can send me a workbook that clearly demonstrates the problem then I can see if I can deal with it with the above wrapper. I am sure there will be no problem at all.
RBS On Thu, Oct 13, 2011 at 5:48 PM, Frank Missel <i...@missel.sg> wrote: > I am trying to use data from an SQLite database as a data source for a > Pivottable in an Excel sheet. > > > > By use of Micrsoft Query and the SQLite ODBC driver I can reference a table > from an SQLite database either as a list in a worksheet or as basis for a > Pivottable. This apparently works fine. However, there is an issue with the > decimal data type which is not recognized, i.e. the cells are of the type > "General" whereas the integer and date fields are represented with the > correct cell format and function. Even if one does change the format of the > cells containing data from a decimal field to "Number", they still do not > work properly as numbers, i.e. the sum function does not work correctly. > > > > I have emailed with the author of the SQLite ODBC driver, Christian Werner, > about the problem. He writes: > > > > "The problem is the typelessness of SQLite. In order to obtain column > information early, a SELECT is prepared twice. The first gives the column > names and potential type information. > > In the second phase the second select retrieves data. For computed columns, > SQLite usually reports SQLITE_TEXT or even SQLITE_NULL in > sqlite3_column_type. As long as an ODBC application retrieves in advance the > correct typed values of a rowset, everything is fine. But that seems not to > be the case for Excel/Query." > > > > I have also installed the System.SQlite.Data ADO.NET driver in the hope that > perhaps it could be used as an OLE DB data source or other type of data > source that could be chosen from Excel. However, it does not show in the > various list of data sources so perhaps that is not possible. > > > > Does anyone have experience and/or ideas about how to use SQLite as a proper > data source that can be accessed from Excel besides the ODBC driver which > has the mentioned problems with decimal fields? > > > > > > /Frank Missel > > _______________________________________________ > 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
