Use the CData ODBC Driver for Carbone in Microsoft Power Query
The CData ODBC Driver for Carbone enables you to link to Carbone data in Microsoft Power Query, ensuring that you see any updates. This article details how to use the ODBC driver to import Carbone data into Microsoft Power Query.
Connect to Carbone as an ODBC Data Source
If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.
Start by setting the Profile connection property to the location of the Carbone Profile on disk (e.g. C:\profiles\Carbone.apip). Next, set the ProfileSettings connection property to the connection string for Carbone (see below).
Carbone API Profile Settings
Carbone uses API key authentication via a Bearer token in the Authorization header. To obtain an API key:
- Log in to your Carbone account at https://account.carbone.io
- Copy the API key shown on the account home page
After obtaining your API key, set the following connection properties:
- AuthScheme: Set this to APIKey.
- APIKey: Set this to your Carbone API key (Bearer token).
- CarboneVersion: (Optional) The major version of the Carbone API to target via the carbone-version request header. Defaults to 5. Accepted values: 5, 4, 3, staging.
Import Carbone Data
Follow the steps below to import Carbone data using standard SQL:
-
From the ribbon in Excel, click Power Query -> From Other Data Sources -> From ODBC.
- Enter the ODBC connection string. Below is a connection string using the default DSN created when you install the driver:
Provider=MSDASQL.1;Persist Security Info=False;DSN=CData API Source -
Enter the SELECT statement to import data with. For example:
SELECT Id, Name FROM Templates WHERE Category = 'Invoices'
Enter credentials, if required, and click Connect. The results of the query are displayed in the Query Editor Preview. You can combine queries from other data sources or refine the data with Power Query formulas. To load the query to the worksheet, click the Close and Load button.
