The following notes are offered, regarding the use of ODBC to connect Magic to an Excel workbook/spreadsheet.

Set up the ODBC DSN

(ODBC = Open Database Connectivity)
(DSN = Data Source Name)

From Windows XP: Programs -> Administrative Tools -> Data Sources (ODBC)

From older O/Ss: You may find "ODBC" or "32-bit ODBC" on the Control Panel

Create a DSN

A DSN (Data Source Name) associates a particular ODBC driver with the data you want to access through that driver. Drivers are the components that process ODBC requests and return data to the application.

There are 3 types of DSNs. User and System DSNs are stored in the registry of a single machine. File DSNs are stored in files, and can be shareable among multiple users and/or machines. During this tutorial, all DSNs will be of the "System" type.

Microsoft Doc on DSN types∞

There are a lot of Drivers available, but they might not be installed on your computer. Check out the Drivers Tab to see what you have! Some of these drivers came with you O/S, and others were installed with other programs. In particular, PVSW drivers (probably) weren't there until you installed the PVSW Server or Workgroup engine. At this time, make sure you have the "Microsoft Excel Driver (.xls)" driver installed.

1. Select the "System DSN" tab, and click the "Add" button.
2. Select the Driver you want to use. In this case, "Microsoft Excel Driver (.xls)". Click the "Finish" button.
3. On the "ODBC Microsoft Excel Setup" dialog, provide a "Data Source Name". Technically, you can have spaces in this name; but I would HIGHLY suggest that life is much easier if you do NOT use spaces. The DSN name should be descriptive of the data set, like "Payroll" or "Accounting". Whatever you name it, keep track of this name because you will need it later when setting up the "database" in Magic.
4. Still on the "ODBC Microsoft Excel Setup" dialog, verify the "Version" of Excel that has created the file you wish to access. Then click the "Select Workbook" button and use the "Select Workbook" dialog to choose the file.
5. If you intend to WRITE any data to this workbook, make sure you uncheck the "Read Only" box. Click "OK" to return to the previous dialog.
6. Finally, take a look at the "Options". "Rows to Scan" is an important property that will be discussed later, and "Read Only" is the same checkbox that was on the "Select Workbook" dialog. You can set the "Read Only" property in either location.

IMPORTANT NOTE: You can not create an Excel file through ODBC! It must already exist.

Now, for Magic eDeveloper

Once you have the System DSN created, we can move to Magic. However, don't start Magic yet!

First of all, make sure that your Magic ODBC Gateway is installed and accessible. For eDev 9.4, you should have a file "MGodbc.dll" in your {magicpath}\Gateways directory.

Secondly, you should edit your Magic.ini file and find the following entry in your [MAGIC_GATEWAYS] section, and make sure there is NOT a semi-colon in front of it! The semi-colon is a comment indicator, and if it is there in front of this line, your ODBC Gateway will not load.

Now you can start Magic!

Make sure that no Application is open.

On Settings -> Databases, create a new line.

1. Provide a (magic) name for your (magic) database.
2. Change the DBMS from "Btrieve" to "ODBC".
3. Change the Database Name from "MAGIC" to the name of the DSN you created.
4. Leave the Magic Server property blank.
5. Leave the Location property blank.

Open your application. It may not be obvious, but I would suggest that you play with ODBC for awhile in a "test" application.... :)

Open the Tables repository <Shift-F2>.

1. Make sure you are parked on the top line ABOVE the first table entry.
2. Select the menu item Options -> Get Definition.
3. Zoom to the Database list and select the database you just created.
4. Change "Tag tables" to "All"
5. Click the "OK" button.

WE ARE GETTING CLOSE, BUT DON'T TRY IT YET! I know you probably have an itch to hit CTRL-G and see how it works, but you aren't quite ready.

You will have a Table repository entry for each Excel worksheet AND "named range" in your Excel workbook. The Table -> Columns will be created with the values from the first row sheet / named range. (Blanks will have a system generated name like "F1".) What you will NOT have is any index defined, and ODBC in Magic REQUIRES you to have a Unique Index for each table.

Your next step, then, it to create an Index in your Magic Table definition, choosing one or more columns that will provide unique values.

(As I write this, I do NOT know of a way to use the row number directly. It would certainly seem useful for this; but I haven't figure this out yet. What I have done, however, is to insert a column in the spreadsheet, populate the cell with "ROW(A1)", and copy it down for all the rows in the data set.)

NOW!! You can CTRL-G execute (or generate a program for) this table.


There is quite a bit more information I intend to share on this topic, but in the interest of getting some of the information out, I'm going to put that off for awhile.

Some of these topics are:

• Simultaneous access by magic and excel -- open the file first in excel! (This may provide some really cool "dashboard" opportunities with excel graphs.)
• Named ranges in excel -- allows separate data sets on the same worksheet.
• Rows to Scan property in the DSN -- affects how magic does the "get definition".
• The Unique Index actually filters out the returned result set from ODBC. Magic online and batch tasks may not work with the same data set! :(

I hope you have found this tutorial useful!

Good day.

P.S. iBolt is way cool!!

Categories

CategoryHowTo