Monday, December 16, 2013

Can you get data from SQL Anywhere 16 using MS Excel?

The answer is yes. SQL Anywhere installation adds a ODBC DSN to the computer. MS Query is MS Excel can connect to this data source as shown in this post.

In a number of previous posts we have seen how to access data on SQL Anywhere Server using either ODBC or ADO.NET connectivity. Follow the links here:

1. Start the SQL Anywhere Server from its link as shown.

The Server starts up as shown.

2. Launch MS Access (in the present case MS Access 2010 Professional Plus).
3. Click menu item Data. Click From Other sources and from the drop-down menu
   click From Microsoft Query as shown.

4. Choose Data Source window gets displayed as shown.

5. Click demo* and click OK.
Query Wizard - Choose Columns window  is displayed as shown.

6. Click Contacts (for example) and using the > button transfer all columns to the Columns in your query window.

7. Click Next
Query wizard - Filter Data window is displayed is displayed as shown.

8. If you need to filter data go ahead and choose what to filter. Here no filtering is done.

9.Click Next.
Query Wizard - Sort Order window is displayed as shown.

10. Click Next.
Query wizard - Finish is displayed.

11. Accept default and click Finish. (not shown)

The Import Data window is displayed as shown.

12. If you accept the default location and click OK, the program brings data beginning the cell A1 as shown.

That is all there is to it.

No comments: