> boun...@sqlite.org] On Behalf Of Bart Smissaert > Sent: 14 October 2011 04:05 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables and > pivottables) > > It looks you can't make a pivot table directly from an array. > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
