1. SuiteAnalytics
  2. Connect
  3. Accessing the Connect Service Using an ODBC Driver
  4. Accessing the Connect Service Using Microsoft Excel

Accessing the Connect Service Using Microsoft Excel

You can use the SuiteAnalytics Connect ODBC driver to load your NetSuite data to Microsoft Excel workbooks.

There are several ways to do this:

Before you can configure Microsoft Excel to pull data from the SuiteAnalytics Connect data source, you need to download and install the latest SuiteAnalytics Connect ODBC driver and make sure it is connected to your NetSuite data source. To learn how to download and install the driver, see Downloading and Installing the ODBC Driver for Windows. To learn how to test your connection, see Configuring the ODBC Data Source on Windows.

Using a query

One of the most convenient ways to run a database query in Microsoft Excel is to use the New Query option. This option is available starting with Microsoft Excel version 2016. However, if you use Microsoft Excel 2010 or 2013, you can install the Power Query add-in to be able to use this option. For details, see https://www.microsoft.com/en-us/download/details.aspx?id=39379.

Important:

The following instructions are based on a 32-bit SuiteAnalytics Connect ODBC driver used in conjunction with 32-bit Microsoft Excel 2016. Please note that the examples in these instructions are provided for illustrative purposes only and may vary based on your driver version and your version of Microsoft Excel. Additionally, the tables available in your NetSuite account may vary.

To load data using a query:

  1. In Microsoft Excel, go to the Data tab and select New Query > From Other Sources > From ODBC.

    Excel query setup.

  2. In the connection string field, type DSN=NetSuite; to use the Connect driver DSN for connection. To learn how to check your NetSuite DSN, see Configuring the ODBC Data Source on Windows. For DSN-less connection string options, see Connecting Using a Connection String.

  3. Expand the SQL Statement area and enter your SQL query. For example, to run a query over all columns in the Service Items table, enter select * from service_items;.

    To explore the tables available in the Connect schema, you can use the Connect Browser. To learn more about the Connect Browser, see Working with the Connect Browser.

    If you don’t add any query in the SQL Statement field, you can click OK and select a table or a set of tables you want to open in the Query Editor. However, there can be Excel limitations to using this option. In this case, try running the query again, adding an explicit query statement in the SQL Statement field.

    SQL statement example.

  4. Click OK.

  5. Provide your NetSuite login and password, if prompted, and click Connect.

  6. The Query Editor opens. You can rearrange the columns in your query, removing those you do not need.

    To learn more about the Query Editor, see https://support.office.com/en-us/article/Introduction-to-the-Query-Editor-Power-Query.

    Query Editor example.

  7. When ready, click Close & Load to load your query data to your Excel workbook. By default, your data will be loaded to a new worksheet. For more options, expand this menu and click Close & Load To.

    Close & Load icon
Note:

Due to an Oracle limitation, queries over SuiteAnalytics Connect schema tables including more than 999 fields will not run. This may happen when querying over tables that have many custom fields or when joining multiple tables in a single query and trying to retrieve all their fields. For example, if you add too many custom fields to the Transaction record type, exceeding the 999 fields per table limit, you may get the following error: “Error: Could not find any column information for table:transactions”.

Using Data Connection Wizard or Microsoft Query

You can also connect to the NetSuite data source using the Data Connection Wizard or Microsoft Query and select the tables you would like to import into your Excel workbook. In this case, you can choose whether to display the imported data as a table, a PivotTable report, or a PivotChart. To learn more about Data Connection Wizard and Microsoft Query, please refer to https://support.office.com/en-us/article/Overview-of-connecting-to-importing-data.

Related Topics

General Notices