Creating Database Projects with SQL Anywhere Studio, Part 2 - Importing ASA data into an Excel worksheet
(Page 3 of 5 )
Microsoft Excel is a widely used, extremely popular spreadsheet program. Financial experts love it because of its integration with other Office products, charting, underlying VBA, and number crunching power among others. Before you can connect to the database, it is necessary to prepare a File Data Source Name. In order to prepare this, open a Notepad application (albeit empty) and save it as, for example, Asa.udl. The application (Notepad) will warn you that it may be unusable if the extension is changed, but ignore this objection. Now right click on this icon,
to bring up the Data Link Dialogue. Make sure that under Providers, MS OLEDB PROVIDER FOR ODBC DRIVERS is chosen. For Use Data Source Name choice type in "ASA 9.0 Sample", use dba for User Name and sql for Password. You may test this connection.
Click open MS Excel application (Excel 2002 SP1 in this example), and in the Data menu item, click to show the drop down items. Under Import External Data pick up Import Data...
This brings up a file open dialogue and you may browse to find the Asa.udl that was created earlier (in this example it was saved in a folder called Excel; it depends on where it is saved). Highlight this and click Open.
As was saw in Part 1, this database had a number of tables. The table list from the database now pops up, where you can select the table you want to bring in to Excel.
As soon as you pick a table, you will be asked the location where it may be displayed. You can choose a location. In this screen shot, it starts from first cell (default) in the worksheet.
If you click on the Properties button in the above-imaged screen, you can access the External Data Range properties. You can choose formatting options; refresh the background; save query definitions and/or passwords; and so on.
Now the table data is nicely positioned in the Excel Worksheet. From here it can be processed within Excel as shown in the following picture, where autosort is turned on.
Next: Creating a VB application >>
More Database Articles
More By Jayaram Krishnaswamy
/ 11 
