How to get the actual data into excel?

Oct 4, 2012 at 7:39 PM

Hi all.

New here.  I found this really great library, however I can't quite figure out how to use it.  the examples show how to do very specif things in vba, then spit the results out to the debug console, which is great.  However, I really want to use this tool to grab result sets from sqlite, and put into a excel page (table, pivot, whatever).

 

Does anyone have some examples of doing so?  Something like:

Select x,count(y),sum(z) from datatable where owner ="somebody" group by x

to the following imagined rows in excel on some sheet somewhere:

A      |  B |  C  |

xval1  10  100

xval2   5   5000

....

 

I see how to grab the data, what I don't see is how to put it into an array then to table range, or directly to cells even.

 

Thanks in advance.   And thanks for the cool tool.  This will allow me to distribute with zero dependencies (not even the odbc driver) which is key.  the alternative is access, which I despise...

I picked this example as it covers the extent of the types of things i need t do (select, groub by, and count/sums)

Oct 4, 2012 at 8:16 PM

Hi,

You'd have to learn about the Excel object model, and how to use it from VBA. That's a much broader topic than that covered by this library (which is just about getting to SQLite data from VBA). A good start might be this: http://msdn.microsoft.com/en-us/library/office/ee814737.aspx. You'll also find lots of general introductions to VBA programming by searching the web for "Excel VBA introduction" or something like that.

Anyway, to write to Excel, you'd make calls on a 'Range' object that represents the part of the sheet you want to modify. In a macro, this is as easy as:

    Range("A1").Value = "asd"

 

But it's likely you'd have many follow-up questions, which are probably better addressed in a more general forum, like the Excel for Developers forum here: http://social.msdn.microsoft.com/Forums/is/exceldev/threads, or even StackOverflow: http://stackoverflow.com/

I hope that gets you started.

Regards,

Govert

Oct 4, 2012 at 8:51 PM

thank you for your quick response.  however, i guess I was unclear.  I am very familiar with the excel object model, and how to set Range("A1").Value = "asd".

What I can't seem to see from the examples is how to get "asd" out of the sqliteforexcel return value.  the only way to access the data seems be the SQLite3Column* family of functions.  (from the examples anyway).  maybe i need to look into the api more I guess.  I just thought someone would of already done a SELECT * from sometable -> rows in excel with this Library, and was looking for an example that did more then print values out.  Or more aptly, how to select the resultant row as an array would be more efficient rather than one value at a time.  In other words, what is the sqliteforexcel object model?  There doesn't seem to be a way to do, where "returned_result" is what comes back from the query:

r=0

for rowdata in returned_result

  c=0

  for coldata in rowdata

Range("A1").offset(r,c).Value=coldata

        c =c+1

   next coldata

   r= r+1

next rowdata

which wold set A1 to the first column of the first row returned in the data set, A2 to the second column, A3, third column...

B1,  the first col in the second row returned...  Primitive, and would be better as an an Array, or a Collection, but you get the idea

 

Again thanks, i guess I'll have to look into the api more to see how return data is accessed, the examples don't quite make it clear to me how if the returned result is lets say two rows:

Name   Age   Weight

Him      50     200

Her      25     125

to get to the value 25 programmatically.

Oct 4, 2012 at 10:47 PM
Edited Oct 4, 2012 at 10:49 PM

Hi,

Sorry, OK - I now understand your question better.

SQLite for Excel has no 'object model'. It just exposes (some of) the flat C API of SQLite to VBA - I have written no other wrappers around these. The C functions are described in the SQLite documentation here: http://sqlite.org/c3ref/intro.html. Indeed the only way to get the data out of a result set is to make SQLite3Column*** calls for each column, then call SQLite3Step to get to the next row. This is the API that SQLite provides. (You should find that the many SQLite3Column calls are extremely fast, so the issue of 'efficiency' is really about how much code you need rather than performance.)

In your example you'd SQLite3Prepare the statement, then call SQLite3Step twice to get to the second row, then call SQLite3ColumnDouble(hStmt, 0) to pull out the value from the 1st column. Certainly if you are going to do a lot of this, you'd write a higher-level wrapper that encapsulates these calls, probably with some object model that looks a bit like DAO or ADO. None of that is done here - I just sorted out the glue to get the SQLite C API to work in VBA.

There are other ways to talk to SQLite from VBA, but I had problems with these - the free ones were not open source and had poor performance because of the way they dealt with the data and called SQLite. I wanted to test what the 'raw' performance would be  and so needed to be in control of all the code between VBA and the SQLite library.

If you are able to contribute some higher-level wrappers, that would be very welcome.

I personally use .NET, and integrate with Excel via my Excel-DNA library.

Regards,

Govert

Oct 5, 2012 at 2:29 PM

Thanks again for responding.  I kind of came to that conclusion, and having it confirmed is always nice.  I may very well write some vba wrappers (I can't believe i just said that, the only thing worse than vba is dxl, or maybe perl...)  If I do, I'll post them back.

This is still desirable since I really need a "no install" any dependencies option (yeah, I know, poor grammar).  Originally the plan was to use a true sql database, the problem is connectivity.  We have use cases where the data is needed despite a loss of connectivity.  I may also look again at compact edition, which I have used in the past with .Net and have much success.  Since I am a python guy, sqlite is highly desirable though.

 

Again, I appreciate this library, it is a very nice piece of work.

Mar 29, 2013 at 12:37 PM
Hi Yoheeb, maybe a bit too late as by now you might have figured out how to get data in Excel, but thought of letting you know just in case. Have a look at the demo workbook on this site...might have the answer you are looking for... http://www.gatekeeperforexcel.com/other-freebies.html