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
