Testing an ODBC DSN Using Excel

You can use Microsoft Excel to verify that an application can connect to an ODBC data source or other ODBC application.

  1. Open Microsoft Excel, and select Data > Get External Data > From Other Sources > From Microsoft Query.
  2. When the Choose Data Source dialog box opens:
    1. Select New Data Source, and click OK.
    1. Enter the name of the data source.

    1. Select the Vertica driver.

    1. Click Connect.

  3. When the Vertica Connection Dialog box opens, enter the connection information for the DSN, and click OK.
  4. Click OK on the Create New Data Source dialog box to return to the Choose Data Source dialog box.
  5. Select VMart_Schema*, and verify that the Use the Query Wizard check box is deselected. Click OK.

  6. When the Add Tables dialog box opens, click Close.

  7. When the Microsoft Query window opens, click the SQL button.

  8. In the SQL window, write any simple query to test your connection. For example:

    SELECT DISTINCT calendar_year FROM date_dimension; 
    • If you see the caution, "SQL Query can't be represented graphically. Continue anyway?" click OK.
    • The data values 2003, 2004, 2005, 2006, 2007 indicate that you successfully connected to and ran a query through ODBC.

  1. Select File > Return Data to Microsoft Office Excel.

  2. In the Import Data dialog box, click OK.

    The data is now available for use in an Excel worksheet.