The article explains how to connect to the HubSpot database and retrieve its data using the Tableau, Excel, and Power BI tools and the ODBC driver for HubSpot.
Devart ODBC Driver for HubSpot is a high-performance connection solution with enterprise-level functionality. With it, you can connect to HubSpot from reporting, analytics, BI, and ETL applications that are ODBC-compliant from 32-bit and 64-bit Windows. ODBC driver offers simple and safe access to real-time HubSpot data from any location and fully covers standard ODBC API methods and data types.
Key Features
Extended Syntax for SQL
Thanks to the ODBC driver, working with HubSpot objects is now as simple as working with SQL tables. With the enhanced SQL syntax, you may use everything SQL offers in SELECT statements that are still backward compatible with SQL-92.
DML Procedures
In HubSpot, data may be updated in much the same manner as in SQL databases, thanks to the DML (INSERT, UPDATE, DELETE) capability provided by the Devart ODBC Driver for HubSpot.
ODBC Conformance
With the driver, the process of changing large amounts of data in HubSpot becomes more streamlined and accelerated, and you can also perform bulk changes using SQL statements.
Compliance with Open Database Connectivity
The ODBC driver offers complete support for ODBC Data Types and ODBC API Functions.
Additionally, the use of Advanced Connection String options is accommodated. As a result, HubSpot may be accessed from any ODBC-compatible environment, including desktop and online apps.
Compatibility with HubSpot
ODBC driver entirely supports the HubSpot API data types.
The driver may also communicate with the HubSpot application programming interface. Further, we will provide detailed instructions on how to connect to HubSpot via the ODBC driver.
Advanced Data Conversion
We have developed forward Data Conversion mechanisms that allow two-direction mapping among ODBC and HubSpot data types.
Integration
The driver works with some popular integrated development environments (IDEs) and various third-party tools like Microsoft Excel, Visual Studio, Power BI, etc.
See Compatibility for a full rundown of supported platforms and tools.
Wide Range of Platforms
There’s no need to tweak the driver, software, or setting to utilize the Devart ODBC Driver for HubSpot – it works on both 32-bit and 64-bit systems.
Fully Unicode Driver
Our ODBC driver is completely Unicode that allows you to obtain data and operate with them from HubSpot databases that include various languages and scripts, regardless of the environment’s default charset.
Excellent Performance
ODBC driver features, such as local data caching, connection pooling, query optimization, and more, speed up any interaction with HubSpot significantly.
Connecting to HubSpot Using ODBC Driver
To get data from HubSpot, you first need to create a Data Source Name (DSN) using the Data Source Administrator.
1. Start up the ODBC Data Source Administrator.
- You may discover ODBC Data Sources under ControlPanel > Administrative Tools. The icon was known as Data Sources (ODBC) in previous versions of Windows. Locate the software with a bitness compatible with the external program by searching for ODBC Data Sources in Windows (32-bit or 64-bit).
- To create a DSN in 32 bits, run odbcad32.exe from the C:WindowsSystem32 folder, whereas 64-bit DSNs may be created using odbcad64.exe from the C:WindowsSysWOW64 folder.
2. Decide between the User DSN and the System DSN options. Most software supports all DSN types. However, there are a few exceptions.
3. Press the Add button. The Create New Data Source window will appear.
4. Select the Devart ODBC Driver for HubSpot and click Finish. The driver configuration dialog will open.
5. Enter the data of network settings into the corresponding fields.
6. To check if your connection is successful, press the Test Connection button.
7. Click OK to save the DSN.
How to Generate an ODBC Trace Log
In both 64-bit and 32-bit ODBC Administrators, tracing is synchronized when it is enabled or disabled in one.
Make sure Machine-Wide tracing for all user identities is enabled if the ODBC client program you want to monitor uses the Local System account or a user login other than yours.
The ODBC Trace Log Generation on Windows
Follow these procedures to create a trace file in Windows using ODBC Source Administrator.
1. In Windows 10, search for ODBC Data Sources; in older versions of Windows, go to Control Panel > Administrative Tools, and choose the application with an appropriate bitness.
2. Choose the Tracing tab.
3. If required, modify the location of the log files in the Log File Path menu and hit the Apply button.
4. Choose the Start Tracing Now button to begin.
5. Activate a system-wide reset by relaunching all programs.
6. To verify the driver connectivity, go to the DSN settings and click the Test Connection button.
7. Reproduce the issue.
8. In the Tracing section, click the Stop Tracing Now button.
9. Provide the collected log file to us (for example, devart.log).
The ODBC Trace Log Generation on macOS
On macOS, open ODBC Administrator and go to the Tracing tab to activate the trace feature.
1. Open ODBC Administrator.
2. Click on the Tracing tab.
3. If required, modify the location of the log file.
4. In the When to trace option, choose All the time
The ODBC Trace Log Generation on Linux
On Linux, to trace the ODBC calls set the Trace and TraceFile keyword/value pairs in the [ODBC] section of the /etc/odbcinst.ini file:
[ODBC]
Trace=Yes
TraceFile=/home/test/devart.logAfter acquiring a log file, you should turn off logging since it slows down read and write operations.
Using ODBC Driver for HubSpot with Third-Party Tools
ODBC Driver for HubSpot can work with a wide range of ODBC-compliant tools:
- DBeaver
- Oracle Database Link
- Microsoft Access
- Microsoft Excel
- OpenOffice and LibreOffice
- PHP
- Power BI
- Python
- QlikView
- SQL Server Management Studio
- SSIS
- Tableau
You can find detailed instructions on how to connect ODBC-compliant tools to HubSpot via ODBC Driver here.
Connecting to HubSpot with ODBC Driver into Power BI
Power BI is a popular business intelligence solution consisting of services, applications, and connections that enable you to gather raw data from diverse sources and produce relevant insights. With our ODBC driver, you can easily link Power BI with the HubSpot data source.
Let’s look at how it works. It is expected to have a DSN for the ODBC driver for HubSpot already installed and set up.
1. Start Power BI Desktop, then choose Get Data.
2. In the Get Data dialog box, navigate to the Other section and choose ODBC. Then, click Connect to confirm the choice.
3. Expand the Data Source Name DSN drop-down menu in the From ODBC dialogue box, and then choose the DSN you set up for HubSpot earlier.
4. If you wish to input a SQL statement to narrow down the returned results, click the Advanced options arrow, which widens the dialog box, and type or paste your SQL statement.
5. Hit the OK button. If your data source requires authentication, Power BI will request the appropriate credentials from you. To log in, fill out the required sections with your Username and Password, then press the button.
6. Now you should see the data structures in your data source. Clicking on a database item will provide a summary of its contents.
7. Select the table you need to import into Power BI from HubSpot, and click Load.
Connecting to HubSpot with ODBC Driver into Tableau
You can import raw data, run analyses, and generate insightful reports using Tableau – a data visualization tool. You may link to various cloud and on-premise relational and non-relational databases using Tableau Desktop and our ODBC drivers.
1. Launch Tableau Desktop.
2. On the homepage, click More in Connect pane.
3. Select Other Databases (ODBC).
4. Expand the DSN drop-down list and select the DSN you set up for HubSpot. Alternatively, you may pick the Driver option and, if you haven’t already done so, select the Devart ODBC Driver for HubSpot option from the drop-down menu.
5. Hit the Connect button.
6. You may click Sign in when your connection has been established successfully.
7. Choose the appropriate HubSpot database and database schema. Tables in the linked data source should be shown here.
8. Insert the table’s name by dragging it to the field labeled. The data may be retrieved by dragging the necessary tables into this area, or a new custom SQL query can be created by clicking the corresponding button.
9. To see the most recent information, choose Update Now.
Through an ODBC connection, you may import HubSpot data into Microsoft Excel. Data may be imported into a Microsoft Excel workbook and formatted into a table with the help of the ODBC Driver. Use the same version of Excel as the ODBC driver you’re working with; for example, if you installed a 64-bit ODBC driver, you should use a 64-bit version of Excel.
Using ODBC drivers, you may access data from various sources inside Microsoft Excel:
- Incorporating Get & Transform to Link Excel and HubSpot (Power Query)
- Data Connection Wizard: Linking Excel and HubSpot (Legacy Wizard)
- Using Excel’s Query Wizard to Connect to HubSpot
- Connecting Excel to HubSpot with Microsoft Query
- Using PowerPivot to Link Excel and HubSpot
Connecting Excel to HubSpot Using Get & Transform (Power Query)
Connecting Excel to HubSpot through ODBC is possible using Get & Transform (Power Query). The use of an ODBC driver for HubSpot is assumed here.
1. Select Get Data from the drop-down menu after clicking Data in Excel. Then Select From ODBC under From Other Sources.
2. Pick your data source in the From ODBC menu (DSN). You may input the connection string for your data source if you haven’t done so (without credentials, defined in the credentials dialog box in the next step). If you choose, you may also provide a SQL query that will run when a connection has been made. Hit the OK button.
3. The database is where you’ll go to input your login and password for accessing a database, followed by Connect.
4. Select Default or Custom and then hit Connect if your database is not password-protected or if you have already entered your credentials in the ODBC data source settings.
5. Select the table from which you wish to load data and click Load in the resulting box.
6. An Excel spreadsheet with the table’s information will open.
Connecting Excel to HubSpot with Data Connection Wizard (Legacy Wizard)
This solution allows you to access a predefined OLE DB or ODBC external data source.
1. Click on the file’s Data tab in Excel. Select Other Sources from the menu, then use the Data Connection Wizard to import your data.
2. In the new window, choose ODBC DSN and click Next to proceed.
3. To proceed, click the Next button and choose the data source you want to link to.
4. When you’ve located the table holding the necessary data, click on its name, click Next to fill out the details of your new file, or Finish to save your changes.
5. Choose how you want your data displayed in Excel and where you want it placed in the worksheet from the Import Data dialog, then click OK.
6. The necessary information has been added to the current Excel sheet.
Using Excel Query Wizard to Connect to HubSpot
You may use this option to write a basic query for getting data from HubSpot to Excel through the ODBC driver.
Click the Data button in Excel’s main menu to get started.
Open the aforementioned drop-down option and choose From Microsoft Query from the list of available sources.
Once the dialogue box has been displayed, choose the desired data source.
After establishing a connection, choose the information you’d want to see in Excel and go to the next step.
Data filtering and classification are the subsequent two phases. To go on without completing these steps, click Next.
The Save option to the right allows you to save the query for later use.
7. Click Return Data To Microsoft Excel and hit Finish.
8. Select how you want your data displayed in Excel and where you want it placed in the worksheet from the Import data window, then click OK.
9. Excel now has the necessary information.
Connecting to HubSpot into Excel with Microsoft Query
This feature allows you to construct a more complex query for exporting data from HubSpot to Excel using the ODBC driver.
1. Launch Excel, and go to the Data menu.
2. Choose From Other Sources and then From Microsoft Query on the new ribbon.
3. Select the data source you want to link to in the following window (e.g., using the data source name – Devart ODBC HubSpot). Uncheck To create or modify queries, use the Query Wizard and choose OK.
4. You may now choose which tables to include in your query. When you’re ready, select the plus sign.
5. Using the visual editor, you may do various data manipulation tasks, such as filtering rows or columns, sorting data, joining tables, making a parameter query, etc.
Connecting to HubSpot into Excel Using PowerPivot
PowerPivot is an Excel add-in that can be used to conduct in-depth analyses of data and build sophisticated data models. Follow these steps to begin importing the necessary information:
1. Navigate to the PowerPivot window by selecting it from the Excel menu, clicking the PowerPivot tab, and selecting Manage.
2. Select From Other Sources in the new window that opens.
3. Select Others (OLEDB/ODBC) and click Next in the Table Import Wizard.
4. Select the Build button within the Specify a Connection String box.
5. Select the desired data source in the Data Link Properties pane (for instance, Devart ODBC HubSpot), and then click Next.
6. Select a method for data importation now (either select a table from the list or write a query to specify the data to be imported).
7. You might exit the window by selecting Close if the import was successful. The information that was obtained is added to the current worksheet.
