Snowflake Open Flow in Action – Part 4 – Setting up Openflow Connector for Excel

Introduction:

Here’s the next blog in the Snowflake Openflow series, where we delve into the implementation of the Openflow Connector for Excel. This connector enables seamless integration between Snowflake and either an AWS S3 instance or a Microsoft SharePoint site, allowing periodic ingestion of Microsoft Excel files directly into Snowflake tables.

This post walks you through the connector’s workflow, highlight its limitations, and explain how Snowflake simplifies data replication by abstracting the underlying processors and controllers. This connector is ideal if you’re looking to load data from Microsoft Excel Spreadsheets stored in Amazon S3 bucket or Microsoft Sharepoint into Snowflake tables for reporting and analytics. In this blog, we’ll use an Excel file that is uploaded in an Amazon S3 Bucket.

Prerequisites:

1. Ensure that Openflow is setup in your snowflake account and a deployment is active. Refer my previous blog to see how to setup openflow in your snowflake account.
2. AWS Setup:
   1. Create a new S3 bucket and upload the necessary Excel files to be ingested.
   1. Login to your AWS Account and navigate to IAM User to create a new user. Specify the group and additional permissions if needed. This user should have at least s3.GetObject access to objects read by connector from the S3 bucket.
   1. Once the user is created, navigate to Security Credentials and create new access keys. You can allow access only to the specific S3 bucket created. Note down the Access Key and Secret Access Key.
3. Snowflake Setup:
   1. Create a new service user OPENFLOW_EXCEL_USER with type = ‘SERVICE’ and grant OPENFLOW_ADMIN_ROLE created during Openflow setup to this user.
   1. Generate Key-pair – public and private keys for this service user
   1. Create a warehouse for the connector to use, with the smallest size and scale up if required. Grant Usage on this warehouse to OPENFLOW_ADMIN_ROLE.

Here I have used the same Database, Schema, Role and Warehouse created as part of Openflow Setup in my previous blog. You can also opt to create Database objects for this connector, but give the necessary grants to the role which is granted to the Service User.

USE ROLE ACCOUNTADMIN;CREATE USER OPENFLOW_EXCEL_USER TYPE='SERVICE';
GRANT ROLE OPENFLOW_ADMIN_ROLE TO USER OPENFLOW_EXCEL_USER;
ALTER USER OPENFLOW_EXCEL_USER SET RSA_PUBLIC_KEY='MIIBI...';

• Create a runtime using the active deployment created.

Configuring the connector:

• Once the Runtime is Active, navigate to the Openflow Overview page. Go to Featured Connections section and click on View more Connectors. Select Excel connector and click on Add to Runtime.

• Select your runtime from the Available runtimes drop-down list. Click Add.

• Authenticate using your snowflake credentials.

• Once the connector installation is complete, Openflow Canvas will appear with the connector process group added to it.

• Right-click on the open canvas and click on Enable All Controller Services. Check whether all services are enabled by right-clicking on the connector and selecting Controller Services.

• Now we will set the flow parameters by right-clicking on the connector and selecting Parameters.

• There are three different groups of flow parameters to be set for this connector.
  • AWS S3 Source Parameters:

• Snowflake Destination Parameters:

• Excel Data Ingestion Parameters:

• Run the workflow by right-clicking on the connector and selecting Start

• Once the workflow is successfully completed, you will be able to find a new table with the excel name as the suffix.

The connector only performs truncate and load ingestion. This means that every time a file is ingested, the existing data in Snowflake is completely replaced with the new data from the file.

Limitations:

• Currently, only .xlsx files are supported (Office 2007 and later versions). The following file types are not supported:
  • .xls files (Office 97 to Office 2003 versions)
  • .xlsm files (macro-enabled workbooks)
  • .xlsb files (binary workbooks)
• Due to memory limitations, the maximum size of the .xlsx file should not be greater than 2 MB for a Small runtime node, 10MB for a Medium runtime node and 20MB for a Large runtime node.
• For columns with mixed data types (for example, numbers and strings) within the same column:
  • The values are saved as Variants in the Snowflake table.
  • The schema of ingested data (that is, the data types for the columns), is currently inferred using the first 10 rows. If the first 10 rows are of the same type, then the column type might not be properly recognized as mixed.
• If the whole spreadsheet is to be ingested, without any specific range, then the first row, starting in cell A1, must be the header row and it must contain the column names.

Conclusion:

The Openflow Connector for Excel offers a powerful and streamlined solution for ingesting Excel data from Amazon S3 or Microsoft SharePoint into Snowflake. By abstracting the complexity of the underlying processors and controllers, Snowflake simplifies data replication, making it easier for users to focus on analysis and decision-making rather than data integration logistics. Whether you’re building dashboards, running analytics, or powering data-driven applications, this connector can help you automate and scale your Excel data ingestion workflows with ease.

Look out for upcoming posts where we’ll delve deeper into other OpenFlow connectors, share best practices, and explore real-world implementation scenarios.

Referral Link: https://docs.snowflake.com/en/user-guide/data-integration/openflow/connectors/excel/setup

Please feel free to reach out to us for your Snowflake solution needs. Cittabase is a Premier  partner with Snowflake.