Special

Introducing the “Welcome to Xojo” Bundle!

New to Xojo and looking for guidance? We've put together a terrific bundle to welcome you! Xojo Bundle

This bundle includes six back issues of the magazine -- all of year 15 in printed book and digital formats -- plus a one-year subscription so you'll be learning all about Xojo for the next year. It's the perfect way to get started programming with Xojo. And you save as much as $35 over the non-bundle price!

This offer is only available for a limited time as supplies are limited, so hurry today and order this special bundle before the offer goes away!

Article Preview


Buy Now

Issue 16.5 ('Spreadsheet Databases')
Instant purchase and download via GumRoad!

FEATURE

An Excel Database

When Is a Spreadsheet Not a Spreadsheet?

Issue: 16.5 (September/October 2018)
Author: Paul Budd
Author Bio: Paul first learned to program on a Vic-20 and spent many hours typing in listings from magazines during that 8-bit era. Although he works full time in a non-computer related industry, he finds time to write software and provide computer consultancy services to friends and colleagues. While owning an Apple Mac, he is predominantly a windows programmer.
Article Description: er>26 Article Length (in bytes): 17,310
Starting Page Number: rt
Article Number: 16503
Resource File(s):

Download Icon project16503.zip Updated: 2018-08-31 11:09:52

Related Web Link(s):

ch (http
http://www.mockaroo.com

Excerpt of article text...

In this article I am going to demonstrate how to use Xojo to utilise an Excel workbook as if it were a database and manipulate it using SQL. Using Excel is not something I would recommend for databases, but there are many examples of people using Excel workbooks, or even worse, Word documents to store their client contact information and one day you may need to work with this directly. My suggestion would be, however, to import it into a "proper" database, such as sqlite which comes with Xojo. There is also a great example that ships with Xojo in the Database\Samples folder.

This is a Windows-based article. The principles should work the same on Apple Mac, but to use these features on your Mac you will need some ODBC drivers and an ODBC manager. A good place to look for these is Actual Tech (http://www.actualtech.com). The ACESS/Excel driver costs $39.95 at the time I wrote this article.

The first thing we are going to need is a quantity of mock data to play about with and I used Mockaroo (http://www.mockaroo.com) to create the test Excel spreadsheet for this article (see Figure 1).

Mockaroo lets you generate up to 1,000 rows of realistic test data in CSV, JSON, SQL, and Excel formats for free, and larger data sets for $50 a year. It also has other tools to allow you to create and test APIs, but that's beyond the scope of this article.

The range and breadth of the available recordset fields generation is quite staggering, but we will concern ourselves with a relatively simple example which contains 500 records, constructed as follows:

...End of Excerpt. Please purchase the magazine to read the full article.


Home | Store | Browse Published Issues | Customer Service | Contribute | xDevLibrary

Site Copyright 2002-2018 by xDev Magazine and DesignWrite.
Xojo is a trademark of Xojo, Inc..