Connecting Snowflake to EXCEL with ODBC driver.
Integrating your Microsoft Excel to access data from a Snowflake data cloud is a possibility using the Snowflake ODBC connector. With the ODBC Driver, you can pull Snowflake data directly into an Excel Spreadsheet as a table for further processing. This knowledge can significantly enhance your data analysis capabilities.
I will be showing you how to do this in this article using the Windows platform.
Step 1: Installing the Snowflake ODBC Driver:
1.1. Begin by downloading the Snowflake ODBC Driver from the official Snowflake website. You can even start by clicking the ‘Client download’ link from the Snowsight UI.
1.2. Follow the installation instructions provided with the driver installer.
1.3. Once installed, ensure that the Snowflake ODBC Driver is properly configured on your system.
Step 2: Configuring ODBC Data Source:
2.1. In the User DSN or System DSN tab, click on the “Add” button to create a new Data Source.
2.2. Select the Snowflake ODBC Driver from the list of available drivers and click “Finish.”
2.3. Provide the necessary connection details such as the Snowflake account URL, username, password, and other authentication parameters.
·Data Source — (Give a name a desired.)
· User — Enter your user name.
· Server — <account.region>.snowflakecomputing.com (e.g msb42730.snowflakecomputing.com)
· Enter the Database, Schema, Warehouse and Role.
2.4. Test the connection to ensure successful configuration.
Step 3: Connecting Excel to Snowflake:
3.1. Launch Microsoft Excel.
3.2. Navigate to the Data tab and click on “Get Data” or “Data” depending on your Excel version.
3.3. Select “From Other Sources” and then choose “ODBC.”
3.4. In the “ODBC Data Source Administrator” window, locate the Data Source Name (DSN) configured for Snowflake and click “OK.”
3.5. Enter your Snowflake credentials if prompted.
3.6. Select the desired tables or views from Snowflake that you want to import into Excel.
3.7. Click “Load” or “Load To” to import the data into Excel.
This is exactly what you will get if you had query the table in the Snowflake UI.
Additional Note:
Using the advanced option, you can enter an SQL statement that will be executed right after establishing a connection to the data source. Here I am just selecting a subset of a table. The query gets push down into Snowflake.
OTHER USAGE NOTES.
· You will need to use the USERNAME/PASSWORD connection method to connect to EXCEL.
· You can only get the data from Snowflake to Excel through this method and not the reverse. To load Excel data into Snowflake, you’ll have to convert to CSV and load into Snowflake.
· While creating the connection, you may encounter this error if you use https://msb42730.snowflakecomputing.com, for the server connection. Simply use it as msb42730.snowflakecomputing.com and it should work.
CURLerror (curl_easy_perform() failed) — code=60 msg=’SSL peer certificate or SSH remote key was not OK’ osCode=9 osMsg=’Bad file descriptor’.
Conclusion.
Integrating Snowflake data into Microsoft Excel through the ODBC connector provides users with a powerful solution for data analysis and reporting. By following the steps outlined in this article, you can seamlessly import Snowflake data into Excel spreadsheets, enabling you to leverage Excel’s rich features for insightful data analysis and visualization. Unlock the full potential of your Snowflake data by integrating it with Excel today!
Give this a clap and share if you find it helpful.
Follow me on Medium not to miss anything. Let’s also connect on my LinkedIn. Thanks.