BPA Platform Training – Import Flat File: Extract CSV Data and Send to Database (ETL)

Extract Transform Load

The Import flat file tool is valuable to many Business Process Automation scenarios.

Where data is held as a CSV, spreadsheet or as a more complex flat file database, this tool can extract data to be used as part of wider business processes, such as eCommerce or Data warehousing.

Used as part of a process with other BPA Platform Tools, it can be used to transform and map data to eCommerce and ERP systems, and is particularly useful for the initial synchronisation of large volumes of data, such as product and manufacturing data sheets.

The Import Flat File Tool imports delimited or fixed-width files, extracting the file content and file properties and outputting it as either a recordset or XML. The columns in a recordset, or nodes and nested elements in an XML file, can be inferred from an imported example or created manually. Data validation can also be applied to only import required data.

In the following example, we will use the flat file import tool to extract example product data in CSV format, use a decision step to verify files exist, Transform Data Tool to loop and map the data, convert XML to recordset, send the data to the SQL staging database with the stored procedure tool and finally use the file management tool to move the processed file from the awaiting processing to products processed folder.

Chapter Title: Prerequisites

To complete this example, you will need a Staging Database and a configured connection.

You will also need BPA Platform with the following tools: import flat file, decision tool, run VB script, convert XML to recordset, call stored procedure and the file management tool.

Chapter Title: Setting up the file location

On the desktop let’s set up two folders. Name the first awaiting processing and the second products processed. We need to drag our products list CSV to the awaiting processing folder. Notice the products and structure in the file. (Open the CSV to show an example).

The products’ Processed folder is where the file will be automatically moved to when the Task is run.

Chapter Title: The Import flat file tool

Open BPA platform, and navigate to your task folder, and then create a new task. Name it “CSV ETL”. Click Design tab.

Select Import flat file and drag the tool onto the design area.

In the general tab, in Path, click browse and select the awaiting processing folder. In file, name type select the file or type “products” and add a wildcard (“*”) so it can pick up any incremented or dated versions. Add “.CSV” at the end.

In the main /file Tab, ensure XML is selected as this is required for the next step in the task. In root node name type “root”.

Select header row in file and then click browse, we are going to use an example CSV file which BPA Platform will use to configure the output structure. Locate our product CSV file and click open.

The file we are using is Comma delimited with a standard carriage return line feed end marker. Ensure these options are selected.

In the elements/columns tab, we can see the data structure. This can be used to change the values of each node and element. Let’s change new node to Products. Within here you can specify the type of data and other element properties.

Click preview output. Then click ok.

Next, in the options tab, we will set all options to continue, to allow for the processing of all available files.

Ok to close this step.

Whitepaper: Flat File Import Tool

Chapter Title: Transform Data

We will now transform the data so that the CSV input matches the expected output.

Drag in a transform data step from the browser… and in the general tab, in data source select use a recordset or XML from another step and select OutputXML from import flat file.

In the main tab, click replicate structure from input data source. we can see the output structure of the XML, lets change the output name of CSV_SKU_Code to ERP_SKU_Code.

OK, let’s go to the mapping tab. Expand the trees on both sides and here we can see the input XML on the left and Output XML on the right. Let’s drag the products node to products. BPA Platform will offer to automatically map child nodes at this point, click yes.

Notice the simple loop icon that appears between the two, this will ensure the step loops through all the products in the CSV file.

The CSV_SKU_Code will need to be manually mapped to ERP_SKU_CODE, as we wanted to remap the input and output on this.

Now let’s Click OK.

Whitepaper: Transform Data Tool

Chapter Title: The Decision Step

As part of good practice and to reduce unnecessary processing we will drag in a decision step. We will set this up to terminate the task if no files are found by proceeding to an empty run VB script step.

Let’s drag in a Run VB script step and name it END Task and click ok, then link these steps together.

Open the decision step and click on the transform data branch and click edit, name it “File exists”.

Click the script Tab and delete the word true. In the browser environment tab with scope set to Task turn down the import flat file tree and drag SuccessCount into the script window.

At the end, then type “> 0”. This sets the logic to take this route if a file exists. Now click Save and close.

Now click the End branch and click edit. Name it “No File” and in the script tab select else.

Now click Save and close for the branch and save and close for the step.

Whitepaper: Decision Tool Whitepaper: Run VBScript Tool

Chapter Title: Convert XML to recordset

Let’s drag in a convert XML to recordset step to align the output with the required format.

We can go straight to the main tab and ensure the data source is set to Task Step and in the drop-down click output XML from transform data.

In the output recordset configuration click all in select fields, then click ok, and then click yes to the warning dialogue as our requirements are within safe limits.

Whitepaper: Convert XML to Recordset Tool

Chapter Title: Sending Data to the Staging Database with Stored Procedure step

We now need to pass the data to the staging database.

Ok let’s drag in a stored procedure OLEDB step.

In the general tab let’s name it send to staging DB.

Then, ensure use a recordset is selected, in the dropdown ensure results from convert XML to recordset is selected.

In the main tab, set your database connection to your preconfigured connection, browse to your required procedure and we are going to select SP product upsert. Then Click OK.

On the left you will see the parameters within the staging database and we will assign the fields we want to transfer by dragging the record sources from the browser.

Let’s drag in some fields to populate the database.

In addition to your chosen fields, you will need to set TC_SyncStatus with a value of ready by typing this manually.

And Allow _Insert with a value of 1. Click ok to close the step.

Whitepaper: Call Stored Procedure (OLEDB) Tool

Chapter Title: The File Management Tool

The final step moves the processed CSV file from the Awaiting Processing Folder to the Products Processed folder.

Let’s drag in a file management step. In the general tab name it move processed files.
Browse to the file source path of the CSV file, which is the Awaiting Processing folder we made previously.

In file, name browse for the file or type in the name of the CSV and add a wildcard to the filename in case it will be incremented or dated. And add .csv at the end.

In the Main tab, we can use this tool to Copy, rename delete or upload,
for our purposes ensure the move option is selected.

Set the destination path by browsing to the Products Processed folder we made earlier, and we will leave the other settings at their default values.

Click ok and save and close the task.

Chapter Title: Testing the Task

Let’s link the remaining steps. Then click save and close.

In a real-world scenario, this file could be added by FTP or via a network for example.

Our task could be set up with a scheduled step that runs it at regular intervals, but for demonstration purposes, let’s run it by right-clicking and selecting queue now.

When the Task has been completed, let’s look at the folders on the desktop and we can see that the file has been moved to the Products Processed folder.

And In Microsoft SQL Server management studio, in the Products Table, we can see that the contents of the CSV file have been transferred.

Whitepaper: Transfer File (FTP) Tool

This example introduces the concepts and techniques of BPA Platform.
For full production examples, training and more please contact your account manager or visit www.codeless platforms.com.

Thanks for watching.