[sqlite] VBA Sqllite blob data
Hi Preston, That's good news. It would be really interesting to hear how you solved it. Din you hit any particular obstacles? BR Daniel Thank you for all of your suggestions and tips. I have been able to use the code samples and extract the data that I needed from my database in a test scenario. I am now modifying it to work with the live database. Thanks again. Preston King, NHCPM NH Department of Information Technology at NHDOT - TMC 110 Smokey Bear Blvd. Concord NH 03302 (603) 271-6862 www.nh.gov/doit Statement of Confidentiality: The contents of this message are confidential. Any unauthorized disclosure, reproduction, use or dissemination (either whole or in part) is prohibited. If you are not the intended recipient of this message, please notify the sender immediately and delete the message from your system. -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto: sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Kevin Benson Sent: Tuesday, May 12, 2015 10:15 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] VBA Sqllite blob data On Tue, May 12, 2015 at 7:41 AM, Preston King wrote: > I have been able to use SQLite2009 Pro Management Studio to export the > specific record from the DB3 file into Excel without any issues, the > entire record appears correctly. I was trying to create VBA code > within Excel to do the same thing. The blob record is a form of html > code, it doesn't follow any typical code pattern so I had to write > code to extract the data that I needed for the report(s). I have > successfully created the routines and the report works well. > > So the only part that I am having difficulty with is automating the > importing of the record from the DB3 file into my worksheet. SQLite > Studio exports the record that I need into an Excel worksheet just > fine. I then copy the record into the worksheet that I created and > execute my routines to produce the reports that have been requested. I > am trying to have as little user interaction as possible. > > Thanks, > Preston > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Daniel > Sj?din > Sent: Monday, May 11, 2015 12:22 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] VBA Sqllite blob data > > Hi, > > Have a look at the last post in this thread. Never tested this but if > true then maybe it's an idea to try to implement it in vba part of > sqliteforexcel. > > > http://stackoverflow.com/questions/2516702/getting-around-the-max-stri > ng-size-in-a-vba-function > > Regards, > Daniel > Den 11 maj 2015 18:17 skrev "Bart Smissaert" : > > > What is in the blob? > > What are you trying to see when you dump it to the sheet? > > > > RBS > > > > On Mon, May 11, 2015 at 2:27 PM, Preston King > > > > wrote: > > > > > I have a db3 config file that contains several different records. > > > I need to select one specific record and store it in an excel > > > worksheet cell. I have been able to create a routine to extract > > > the information that I need once I get the record into a worksheet cell. > > > The problem that I have run into is the record is longer than 255 > > > characters and gets truncated at > > that > > > limit. > > > > > > > > > -Original Message- > > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > > > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Bart > > Smissaert > > > Sent: Friday, May 08, 2015 5:23 PM > > > To: General Discussion of SQLite Database > > > Subject: Re: [sqlite] VBA Sqllite blob data > > > > > > What do you mean with: into Excel? Into a cell in the worksheet, > > > into a VBA variable? > > > What code did you try? I never use blobs, but I don't think it > > > should be > > a > > > problem. > > > > > > RBS > > > > > > > > > > > > On Fri, May 8, 2015 at 8:15 PM, Preston King > > > > > > wrote: > > > > > > > Does anyone have an example of how to read sqlite blob records, > > > > that are not pictures, into Excel? I have been trying to find > > > > some VBA code to do this but am not having much luck. Thanks I, too, believe SQLite for Excel is your best bet (as others have suggested) so to add to the information supplied... I noticed mention of: *XLSQLite.xlam * http://www.gatekeeperforexcel.com/other-freebies.html "It uses SQLite for Excel to interact with SQLite databases and provides a simple GUI that facilitates the creation and manipulation of SQLite databases directly from within Excel. It also provides functionality to execute SELECT statements directly from within an Excel array formula and return the result in a range of cells." -- -- -- --???-- K e V i N ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VBA Sqllite blob data
Hi Preston, my last suggestion to try to expand the variable size on vba side of sqliteforexcel is really not worth it. Looks like it have been taken care of already (well, sort of). https://sqliteforexcel.codeplex.com/discussions/266048 If I understand this right you will get a byte array when using the SQLite3ColumnBlob function. Then you need to convert it into text. Have a look at the end of the thread. Suppose you will use StrConv function. Daniel 2015-05-12 13:41 GMT+02:00 Preston King : > I have been able to use SQLite2009 Pro Management Studio to export the > specific record from the DB3 file into Excel without any issues, the entire > record appears correctly. I was trying to create VBA code within Excel to > do the same thing. The blob record is a form of html code, it doesn't > follow any typical code pattern so I had to write code to extract the data > that I needed for the report(s). I have successfully created the routines > and the report works well. > > So the only part that I am having difficulty with is automating the > importing of the record from the DB3 file into my worksheet. SQLite Studio > exports the record that I need into an Excel worksheet just fine. I then > copy the record into the worksheet that I created and execute my routines > to produce the reports that have been requested. I am trying to have as > little user interaction as possible. > > Thanks, > Preston > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Daniel Sj?din > Sent: Monday, May 11, 2015 12:22 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] VBA Sqllite blob data > > Hi, > > Have a look at the last post in this thread. Never tested this but if true > then maybe it's an idea to try to implement it in vba part of > sqliteforexcel. > > > http://stackoverflow.com/questions/2516702/getting-around-the-max-string-size-in-a-vba-function > > Regards, > Daniel > Den 11 maj 2015 18:17 skrev "Bart Smissaert" : > > > What is in the blob? > > What are you trying to see when you dump it to the sheet? > > > > RBS > > > > On Mon, May 11, 2015 at 2:27 PM, Preston King > > wrote: > > > > > I have a db3 config file that contains several different records. I > > > need to select one specific record and store it in an excel > > > worksheet cell. I have been able to create a routine to extract the > > > information that I need once I get the record into a worksheet cell. > > > The problem that I have run into is the record is longer than 255 > > > characters and gets truncated at > > that > > > limit. > > > > > > > > > -Original Message- > > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > > > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Bart > > Smissaert > > > Sent: Friday, May 08, 2015 5:23 PM > > > To: General Discussion of SQLite Database > > > Subject: Re: [sqlite] VBA Sqllite blob data > > > > > > What do you mean with: into Excel? Into a cell in the worksheet, > > > into a VBA variable? > > > What code did you try? I never use blobs, but I don't think it > > > should be > > a > > > problem. > > > > > > RBS > > > > > > > > > > > > On Fri, May 8, 2015 at 8:15 PM, Preston King > > > wrote: > > > > > > > Does anyone have an example of how to read sqlite blob records, > > > > that are not pictures, into Excel? I have been trying to find some > > > > VBA code to do this but am not having much luck. Thanks > > > > ___ > > > > sqlite-users mailing list > > > > sqlite-users at mailinglists.sqlite.org > > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-use > > > > rs > > > > > > > ___ > > > sqlite-users mailing list > > > sqlite-users at mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > > > sqlite-users mailing list > > > sqlite-users at mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Daniel Sj?din +46 706 65 22 86
[sqlite] VBA Sqllite blob data
Hi, Have a look at the last post in this thread. Never tested this but if true then maybe it's an idea to try to implement it in vba part of sqliteforexcel. http://stackoverflow.com/questions/2516702/getting-around-the-max-string-size-in-a-vba-function Regards, Daniel Den 11 maj 2015 18:17 skrev "Bart Smissaert" : > What is in the blob? > What are you trying to see when you dump it to the sheet? > > RBS > > On Mon, May 11, 2015 at 2:27 PM, Preston King > wrote: > > > I have a db3 config file that contains several different records. I need > > to select one specific record and store it in an excel worksheet cell. I > > have been able to create a routine to extract the information that I need > > once I get the record into a worksheet cell. The problem that I have run > > into is the record is longer than 255 characters and gets truncated at > that > > limit. > > > > > > -Original Message- > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Bart > Smissaert > > Sent: Friday, May 08, 2015 5:23 PM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] VBA Sqllite blob data > > > > What do you mean with: into Excel? Into a cell in the worksheet, into a > > VBA variable? > > What code did you try? I never use blobs, but I don't think it should be > a > > problem. > > > > RBS > > > > > > > > On Fri, May 8, 2015 at 8:15 PM, Preston King > > wrote: > > > > > Does anyone have an example of how to read sqlite blob records, that > > > are not pictures, into Excel? I have been trying to find some VBA code > > > to do this but am not having much luck. Thanks > > > ___ > > > sqlite-users mailing list > > > sqlite-users at mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome
Hi, There is also a VBA wrapper that I've tested. Maybe the usage is a bit narrow but it shows how broad sqlite is. https://sqliteforexcel.codeplex.com/ Regards, Daniel Hello, Thanks for the additions to the awesome-sqlite [1] list. I added the ODBC and JDBC drivers to a new middleware section, and the R driver to a new language binding section, and the R data frame package to a misc section. Note: For now commercial only tools (e.g. SQLite Analyzer) will NOT get include - sorry. If you want to get it included, buy a sponsored link or a beer for everyone on the mailing list! Just kidding ;-) Cheers. [1] https://github.com/planetopendata/awesome-sqlite ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
