Most recent edit on 2005-12-04 05:19:52 by GroupsMBM

Additions:
P.S. iBolt is way cool!!

Deletions:
P.S. iBolt is really cool!!

---

Edited on 2005-10-21 02:08:41 by GroupsMBM [demo'd a change]

Additions:
P.S. iBolt is really cool!!

---

Edited on 2005-07-01 17:56:17 by GroupsMBM [enahnced the note]

Additions:
This solution needs Magic Open Client License. The "Desktop" versions of Magic do not include the ODBC Gateway.

Deletions:
This solution needs Magic Open Client License

---

Edited on 2005-07-01 15:04:26 by NorbertGutscher [note on license]

Additions:

Note
This solution needs Magic Open Client License

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

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

---

Edited on 2005-07-01 03:25:48 by GroupsMBM [fixed small formatting error]

Additions:
1) Change the Database Name from "MAGIC" to the name of the DSN you created.

Deletions:
1) Change the Database Name from "MAGIC" to the name of the DSN you

1. created.
   
   

   ---

   
   Edited on 2005-07-01 03:13:02 by GroupsMBM [another stab at formatting]
   
   Additions:
   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)
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.
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. 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.
2. 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.
3. 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.
4. 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.

MGDB19={magicpath}\Gateways\mgodbc.dll

Now you can start Magic!

On Settings -> Databases, create a new line.

1. Change the Database Name from "MAGIC" to the name of the DSN you
2. created.

Open the Tables repository <Shift-F2>.

1. Change "Tag tables" to "All"
2. 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.)
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.

• Simultaneous access by magic and excel -- open the file first in excel! (This may provide some really cool "dashboard" opportunities with excel graphs.)
• 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! :(
  
  Deletions:
  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)
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.
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. 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.

1. 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.
IMPORTANT NOTE: You can not create an Excel file through ODBC! It
must already exist.
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.
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.

Now for Magic eDeveloper

Once you have the System DSN created, we can move to Magic.
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.)

MGDB19={magicpath}\Gateways\mgodbc.dll

Now you can start Magic!
On Settings -> Databases,

1. Create a new line.
2. Change the Database Name from "MAGIC" to the name of the DSN you created.
3. Open the Tables repository <Shift-F2>.
4. Change "Tag tables" to "All", and 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.)
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.

• Simultaneous access by magic and excel -- open the file first in excel!

(This may provide some really cool "dashboard" opportunities with excel
graphs.)

• 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! :(

---

Edited on 2005-07-01 03:04:29 by GroupsMBM [additional formatting]

Additions:
1) Select the Driver you want to use. In this case, "Microsoft Excel Driver (.xls)". Click the "Finish" button.

Deletions:
1) Select the Driver you want to use. In this case, "Microsoft Excel Driver
(.xls)". Click the "Finish" button.

---

Oldest known version of this page was edited on 2005-07-01 03:03:16 by GroupsMBM [First pass]
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.

1. Select the Driver you want to use. In this case, "Microsoft Excel Driver

(.xls)". Click the "Finish" button.

1. 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.

1. 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.

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

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.

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.

Now for Magic eDeveloper

Once you have the System DSN created, we can move to Magic.

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,

1. Create a new line.
2. Provide a (magic) name for your (magic) database.
3. Change the DBMS from "Btrieve" to "ODBC".
4. Change the Database Name from "MAGIC" to the name of the DSN you created.
5. Leave the Magic Server property blank.
6. 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.... :)

1. Open the Tables repository <Shift-F2>.
2. Make sure you are parked on the top line ABOVE the first table entry.
3. Select the menu item Options -> Get Definition.
4. Zoom to the Database list and select the database you just created.
5. Change "Tag tables" to "All", and 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.

Categories

CategoryHowTo