[sqlite] How to use SQLite as a data source in Excel (tables and pivottables)
Frank Missel
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...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Simon Slavin
On 13 Oct 2011, at 5:48pm, Frank Missel wrote:
> "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."
would something like
SELECT CAST (theValue AS REAL) AS thisValueReal FROM ...
force the driver to recognise that the value it was getting was REAL ?
Simon.
Bart Smissaert
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
Frank Missel
> From: sqlite-use...@sqlite.org [mailto:sqlite-users-
> bou...@sqlite.org] On Behalf Of Simon Slavin
>
> would something like
>
> SELECT CAST (theValue AS REAL) AS thisValueReal FROM ...
>
> force the driver to recognise that the value it was getting was REAL ?
>
> Simon.
I did not have high hopes, but it did work!
That led me to wonder why that could be. I found out that it has to do with
the declared type name of the column.
"DECIMAL" is no good. However, if you declare a column as "REAL" it works
without any CAST function.
If you column is an expression, however, you still have to use the CAST
function. But at least it can be brought to work.
Worth mentioning is that the cell format is still "General" but it now
really works as a decimal (i.e. you can sum on it).
The above is surprising as Christian Werner writes the following in the
documentation:
" Since October 14th, 2001, the driver supports the data types SQL_INTEGER,
SQL_TINYINT, SQL_SMALLINT, SQL_FLOAT, SQL_DOUBLE, SQL_DATE, SQL_TIME,
SQL_TIMESTAMP, and SQL_VARCHAR."
However, for it to work in Excel, you have to define columns as one of the
following:
INTEGER
REAL
DATE
VARCHAR
I'll let him know.
------
Still, if anyone knows how to use the ADO.NET driver that could also be
interesting, since the ODBC driver is a bit bothersome in that you
apparently have to define a Data Source Name for each individual database
that you want to access -- I haven't found any way to work around that. If
one chooses just the "SQLite3 Datasource" as data source there is an error
message to the effect that the data source contains no visible tables. For
other data sources a particular database can be specified as part of the
selection process.
/Frank Missel
Frank Missel
data type naming of the columns.
Have you found a way to avoid having to define DSN's for each individual
database?
/Frank
> -----Original Message-----
> From: sqlite-use...@sqlite.org [mailto:sqlite-users-
> bou...@sqlite.org] On Behalf Of Bart Smissaert
> Sent: 14 October 2011 01:35
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
and
> pivottables)
>
> 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
>
_______________________________________________
Bart Smissaert
This VB wrapper is not an ODBC driver, so there is no DSN.
The database file is set in the connection string:
Function OpenDB([FileName As String],
[EncrKey As String],
[EnableVBFunctions As Boolean = True]) As Boolean
Member of vbRichClient4.cConnection
RBS
Frank Missel
Okay, but I thought that the wrapper was just for working with the SQLite
database and then later when you wanted to use the database as a data source
that you would then still use the ODBC driver.
But are you saying that you are use the wrapper itself as a data source in
Excel?
And if so, how do you specify the wrapper as a data source?
/Frank
> -----Original Message-----
> From: sqlite-use...@sqlite.org [mailto:sqlite-users-
> bou...@sqlite.org] On Behalf Of Bart Smissaert
> Sent: 14 October 2011 02:21
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
and
> pivottables)
>
> Hi Frank,
>
> This VB wrapper is not an ODBC driver, so there is no DSN.
> The database file is set in the connection string:
>
> Function OpenDB([FileName As String],
> [EncrKey As String],
> [EnableVBFunctions As Boolean = True]) As
Boolean
>
> Member of vbRichClient4.cConnection
>
>
> RBS
>
>
> On Thu, Oct 13, 2011 at 7:11 PM, Frank Missel <in...@missel.sg> wrote:
> > Bart, thanks for the offer, but we found the cause of the problems in
> > the data type naming of the columns.
> >
> > Have you found a way to avoid having to define DSN's for each
> > individual database?
> >
> > /Frank
> >
_______________________________________________
Bart Smissaert
> But are you saying that you are use the wrapper itself as a data source in
Excel?
No, the wrapper is not used that way and I don't think it can be used that way.
The SQLite database is dealt with in VBA or VB6 code via this wrapper.
I suppose you could compare it to using ADO with a DSN-less connection.
RBS
Frank Missel
> bou...@sqlite.org] On Behalf Of Bart Smissaert
> No, the wrapper is not used that way and I don't think it can be used that
> way.
> The SQLite database is dealt with in VBA or VB6 code via this wrapper.
> I suppose you could compare it to using ADO with a DSN-less connection.
>
> RBS
Okay, that's what I thought.
I did try the wrapper a couple of years ago and found it very well designed
and performing; I can also recommend it for VB 6, VBA or VBScript.
I now use the C API directly -- sort of my own wrapper for some special
purposes.
So when referencing an SQLite database from Excel you also use the ODBC
driver I guess.
This brings me to my main remaining issue which is to avoid having to create
an individual data source for each SQLite database.
If anyone have any solution for this or any other, easier alternative way of
accessing an SQLite database as a data source programmatically through the
Excel COM object model (in order to e.g. create a Pivottable), I would be
very eager to hear about it :-).
Bart Smissaert
> So when referencing an SQLite database from Excel you also use the ODBC
driver I guess.
I don't access SQLite this way. Only access through this VB wrapper.
> I now use the C API directly
Interesting. Why, if you had a well performing VB wrapper, did you go
this route?
> If anyone have any solution for this or any other, easier alternative way of
accessing an SQLite database as a data source programmatically
This is exactly what I do and no problem at all for example to produce
a pivot table
based on data from SQLite.
RBS
Frank Missel
> Interesting. Why, if you had a well performing VB wrapper, did you go this
> route?
1. Implementing the wrapper in the project code would also take some coding,
and I found that using the C API would not be that much extra work. Thus I
could save a layer, which was good as the project had several other layers
already.
2. The project required heavy data loads. I thought that I could get better
performance and control of data validation, i.e. I could decide exactly how
much and what to have.
3. It was a bit fascinating to get to work close to engine -- minimalistic
and effective is always fascinating :-).
> > If anyone have any solution for this or any other, easier alternative
> > way of
> accessing an SQLite database as a data source programmatically
>
> This is exactly what I do and no problem at all for example to produce a
pivot
> table based on data from SQLite.
Interesting, how do you get the data from the table or view into Excel to be
the basis of the Pivottable?
Do you paste it to a worksheet (perhaps as arrays) that then becomes the
basis of the Pivottable?
My problem is that the data basis of the Pivottable will sometimes be
millions of rows, i.e. many more than can be contained in a worksheet.
But when referencing the data source directly as a proper data source the
number of rows are not limited to the maximum number of allowed rows in a
worksheet.
Bart Smissaert
millions of rows
OK, I haven't got that problem and my pivots are based on a sheet range.
Sheet range is based on a variant array obtained from SQLite.
I will need to check, but I think you can use an array for the basis of a pivot.
RBS
Petite Abeille
On Oct 13, 2011, at 9:36 PM, Frank Missel wrote:
> Interesting, how do you get the data from the table or view into Excel to be
> the basis of the Pivottable?
> Do you paste it to a worksheet (perhaps as arrays) that then becomes the
> basis of the Pivottable?
Pivot tables can be populated from another worksheet, a data source, an OLAP cube, etc...
> My problem is that the data basis of the Pivottable will sometimes be
> millions of rows, i.e. many more than can be contained in a worksheet.
> But when referencing the data source directly as a proper data source the
> number of rows are not limited to the maximum number of allowed rows in a
> worksheet.
That number is pretty high these days, but yes there is no point using an intermediary worksheet. Get the data directly from source. Also, you may want to pre-process, e.g. summarize, your data in the database already as much as you can before hand. Excel is not a speed daemon when confronted with a truck load of data.
You can also create and save the pivot table as an offline OLAP cube, with hierarchical dimensions & all (Microsoft Query + OLAP Cube Wizard).
Bart Smissaert
What you could do though is write the array to a text file and base the array on
that file as an external data source via a text driver.
Another option is build your pivot table in code, not using the Excel
pivot table
object.
RBS
Frank Missel
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
and
> pivottables)
>
> What you could do though is write the array to a text file and base the
array
> on that file as an external data source via a text driver.
> Another option is build your pivot table in code, not using the Excel
pivot
> table object.
>
> RBS
I do sometimes use the option of building the Pivottable through usage of
the SELECT and GROUP BY. However, it is then frozen in that form. The nice
thing about Pivottables is that the row and column fields can be changed on
the spur to get a new view of the data.
As for text files I find that they introduce yet another layer. The data is
already coming from somewhere else then stored in SQLite, and now they then
have to go to a text file to then be imported to Excel. Also, this may
create new challenges with the data types being recognized correctly, and
more importantly: I could not find a way to programmatically get Excel to
take a text file as basis of a Pivottable. Sure, doing it manually is no
problem at all but back with Excel 2003, I and some others tried to get it
done through Automation (Excels COM object model) -- it simple could not be
done (I almost suspect this was so by design from MS). Perhaps it is
different in Excel 2007 / 2010, but I could imagine not.
All in all it would be nice to just use the SQLite database as a proper data
source like you can with Oracle, SQL server and a number of other databases
/ data sources. But I guess the best for now will be to just accept creating
a DSN for each individual database and use the ODBC driver. Its a bit messy
programmatically as you have to access the Registry but it can be done.
Frank Missel
> From: sqlite-use...@sqlite.org [mailto:sqlite-users-
> bou...@sqlite.org] On Behalf Of Petite Abeille
> Sent: 14 October 2011 03:58
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables
and
> pivottables)
>
> OLAP cube, etc...
Yes, indeed.
> That number is pretty high these days, but yes there is no point using an
> intermediary worksheet. Get the data directly from source. Also, you may
> want to pre-process, e.g. summarize, your data in the database already as
> much as you can before hand. Excel is not a speed daemon when confronted
> with a truck load of data.
>
> You can also create and save the pivot table as an offline OLAP cube, with
> hierarchical dimensions & all (Microsoft Query + OLAP Cube Wizard).
Row max is slightly more than a million rows now which is a lot better than
in Excel 2003.
However, the data sets that I use often go above that number of rows.
Pre-processing is an option that I sometimes use. But it then freezes the
representation of the data in that form. The ability to change the view of
the data in Pivottables is a very nice feature.
I find that Excel has decent performance in Pivottable calculations,
especially so with the newer versions.
I might look into OLAP cubes, although I find it adds yet another
computational layer.
Probably the best option for now is to stick with the ODBC driver and just
accept the hassle of creating a new DSN for every individual database which
requires some registry manipulation which is a bit messy but can be done.
I am still dreaming and hoping that someone might have the perfect solution
for just using the SQLite database as a proper OLE DB data source which
seems to be the way to do it nowadays or using the generic ODBC driver as
the data source without having the create an individual DSN.
/Frank
Bart Smissaert
> But I guess the best for now will be to just accept creating
a DSN for each individual database and use the ODBC driver.
Looks that is your best option then, yes.
You could create DSN's in code via the Windows API.
Can't see any great problem with that.
RBS
Alek Paunov
You can take a look at my start-up file for excel 2000/2003:
http://source.declera.com/excel/personal.xls
(I am also attaching contained VBA module db.bas)
With started personal.xls [1], one can open empty sheet named "sqlite",
enter the path to sqlite database file in cell A1, and then use the
following (defined in personal.xls) shortcuts:
* Ctrl-D: pressed (for example) in A6: Executes SQL in B6 storing
results in new worksheet named as A6
* Ctrl-Shift-D: in A3, same as above, but looks for SQL in A4 and stores
the result starting from A5
* Ctrl-T: like Ctrl-D but opens Pivot table instead of Query Table
You can see these in following example:
http://source.declera.com/excel/packages.xls
The example workbook uses this database (part of the Fedora packaging
system yum):
http://source.declera.com/excel/packages.zip
As you will see (in the VBA code), this VBA glue lies on the ODBC driver
for sqlite (Thank you Mr.Werner!), but can be used with OLEDB sources too.
Cheers,
Alek
[1] Excel personal.xls from the location specified in:
Tools/Options/General/At startup, open all files in
Frank Missel
I did get it to work without the hassle of creating DSNs for every database but instead using the generic DSN for SQLite by recording a VBA macro and then substituting the name of DSN reference from the one mapped to a particular database to the name "SQLite3 Datasource" system DSN (from the ODBC driver) and changing the SQL code so it matches the actual database.
However, it calls the Connections.Add method with arrays and thus doesn't seem as simple as your code which has a better structure and thus will be easier to adapt, so I will use that as the basis for my external code calling the Excel COM objects.
Thanks a lot, this is very helpful.
Best regards,
Frank
> -----Original Message-----
> From: Alek Paunov [mailto:al...@declera.com]
> Sent: 15 October 2011 00:13
> To: General Discussion of SQLite Database
> Cc: Frank Missel
> Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables and
> pivottables)
>