Dataflow automation for sending UDC Excel to AWS S3 bucket in Envizi

This tutorial provides a step-by-step guide for setting up Envizi dataflow automation to send Universal Data Connector (UDC) Excel templates to Envizi via an AWS S3 bucket for data ingestion and integration.

Prerequisites

To follow this tutorial, you need access to the IBM Envizi ESG Suite with administrator privileges.

Steps

These are the steps you need to follow:

1. Create a data service for AWS S3 bucket.
2. Create a data pipeline.
3. Send an UDC Excel file to an AWS S3 bucket.

Step 1. Create a data service for AWS S3 bucket

1. Open Data Services by navigating to Admin > Data Flow Automation.

   

2. In the Data Services page, click Add New Service.

   

3. Configure the data service:

   • From the Type list, select Amazon S3 (Cloud Storage).
   • From the Owner list, select Envizi.

   • In the Name field, enter a name for the data service.

     

4. Click Save. The data service is created.

5. Access the Data Services page by selecting Actions > Manage Connections.

   

   The Manage Connections page of the Data Service is displayed.

   

6. Note down the following values for future reference:

   • Bucket
   • Folder
   • Username
   • Access Key
   • Secret Access Key

Step 2. Create a data pipeline

To create a data pipeline for downloading UDC Excel files from an AWS S3 bucket and to faciliate data ingestion into Envizi, complete the following steps:

1. Open the data pipeline by selecting Data Pipelines from the top menu.

   

2. In the Data Pipelines page, click Add New Pipeline.

   

3. Configure the data pipeline:

   • In the Name field, enter a name for the data pipeline.
   • In the Description field, enter a description for the data pipeline.
   • From the Target System list, select Account (or your desired target system).
   • In the Filename Pattern field, specify a regex pattern (for example, ^POC.*.xlsx).
   • From the Data sources list, choose the AWS S3 data source that you created in Step 1.

   • From the Data Transformer list, select None(pass through).

     

4. Click Save to create the data pipeline.

   

Step 3. Send an UDC Excel file to an AWS S3 bucket

Let's use the sample python script main.py to upload the data.xlsx file to an AWS S3 bucket.

1. Download the sample main.py and data.xlsx files into a folder.

2. Update the columns Organization, Location, Account Style Caption, and Account Number in the data.xlsx with your Envizi environment specific values.

   Here is the sample content of an Excel file:

   

3. Open the Linux/Mac terminal window and navigate to the folder containing the main.py file.

4. If you don't have boto3 Python installed, run the following command to install it:

    python -m pip install boto3
   

5. Set the AWS S3 Data Service values with the following commands:

    export s3_BUCKET_NAME=envizi-client-dataservice-us-prod
    export s3_FOLDER_NAME=client_9608cd600af647
    export s3_ACCESS_KEY=AKIxxxxxxxxxxxxxxx
    export s3_SECRET_KEY=axhHxxxxxxxxxxxxxx
   

6. Upload the file to AWS S3 bucket by running the following command:

    python main.py
   

   An output similar to the following example is displayed:

    S3Handler  ...
     ENVIZI_S3_AWS_BUCKET_NAME : envizi-client-dataservice-us-prod
     ENVIZI_S3_AWS_FOLDER_NAME : client_9608cd600af647
    output/results-10122023-110535-725742/POC Account Setup and Data Load_G1_20231012-110543.xlsx is uploaded to envizi-client-dataservice-us-prod  : client_9608cd600af647/POC Account Setup and Data Load_G1_20231012-110543.xlsx
    2023-10-12 11:05:45,747 - INFO:127.0.0.1 - - [12/Oct/2023 11:05:45] "POST /api/turbo/query HTTP/1.1" 200 -
   

   Envizi should have successfully processed your file.

7. Navigate to the File Delivery Status screen by clicking File Delivery Status. You can check the status of your file and observe that it is processed.

   

8. You can observe that the account is successfully created and is now accessible in the Organizational Hierarchy.

   

Summary and next steps

This tutorial guided you through creating a data service for AWS S3 bucket, setting up a data pipeline, and sending a UDC Excel template to Envizi via an AWS S3 bucket for data ingestion.

To get more information about Envizi or to try it out yourself, start your 14-day IBM Envizi ESG Suite trial. You can also request a personalized IBM Envizi demo.