SharePoint Excel To Snowflake Data Loader by COZYROC

This gem automates the transfer of Excel file data from SharePoint to Snowflake target table. It eliminates manual effort by streamlining the data flow using SSIS. The process ensures accuracy, reliability, and timely updates to the target table. Ideal for scalable, hands-free data ingestion into Snowflake from cloud-based sources.

Setup Guide

This gem will establish a connection between Sharepoint (using MS Graph Connection) and Snowflake. It will then pull the data from sharepoint Excel File specified in the parameter Excel File and transfer it to the target snowflake destination table.

To run this gem and connect Sharepoint to Snowflake, configure the parameters that are mentioned below.

Microsoft Graph

• Server Host Required. The default host URL is set to https://graph.microsoft.com/v1.0/sites/root to connect to the SharePoint instance at the root level. To connect to a specific SharePoint instance, set the host URL to https://graph.microsoft.com/v1.0/sites/<site-id>.
• Token File Required. Select the Microsoft Graph Rest Token file from the drop-down list. Review the documentation article here on how to generate the token file.
• Source Folder Required. Specify the source folder path ending with the trailing slash, for example: Dev/Files/Excel.
• Excel File Required. Specify the source excel file name for example: Automobile.xlsx.
• Worksheet Required. Specify the worksheet name.
• Filter Column Optional. Specify a regular expression to exclude unwanted columns from the Excel file.

Snowflake

• Server Host Required. Specify the correct [account identifier] in the server host URL.
• Token File Required. Select the Rest Token file from the drop-down list. Review the documentation article here on how to generate the token file.
• Warehouse Required. Specify the Snowflake warehouse name.
• Table Required. Specify the target snowflake table name.
• Timeout Optional. Specify timeout in seconds to wait before process fails.

Mapping

• Mapping Optional. Use this parameter when source and destination columns differ. Specify mappings in a key-value format, with each pair on a new line, e.g., SourceColumn1=DestinationColumn1.