Amazon S3 Excel To Dataverse Data Loader by COZYROC

This gem automates the ingestion and upsert of Excel worksheet data from Amazon S3 into a designated Microsoft Dataverse table (entity). By eliminating manual steps, it streamlines the data pipeline and ensures accurate, consistent, and timely updates. Designed for scalability and reliability, this solution enables seamless, hands-free data integration between cloud-based storage and Dataverse.

Setup Guide

This gem establishes a connection between Amazon S3 and Microsoft Dataverse. It retrieves data from the specified worksheet within the Excel file stored in S3 (as defined in the Excel File parameter), and then performs an upsert into the target Dataverse table, updating existing records or inserting new ones as needed.

To run this gem and connect desired Amazon S3 bucket to Dataverse, configure the parameters that are mentioned below.

Amazon S3

• Server Host Required. Provide the relevant [bucket] and [region] in the Server URL.
• Access Key Required. Specify the access key for Amazon S3 service.
• Secret Key Required. Specify the secret key for Amazon S3 service.
• Source Folder Required. Specify the path to the source folder ending with a 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.

Microsoft Dataverse

• Server Host Required. Specify the correct instance 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.
• Object Required. Specify the target dataverse object (table) name.
• Ignore Null Value Optional. Specify to ignore null values and not send for processing.
• Alternate Key Optional. Specify alternate key identifier column.

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.