How to load Files(.csv) in IBP and archive them after processing using CPI-DS

Introduction

CPI-DS (Formerly called as HCI-DS) is a cloud-based data integration tool for batch & scheduled data integration between on-premise applications and the SAP cloud platform.

Cloud Platform Integration for Data Services (CPI-DS) is the recommended tool to integrate with Integrated Business Planning (IBP).

This article will explain how to import multiple .csv files in IBP using CPI-DS and archive them (or move to another folder) after processing.

Pre-requisite

File path for the new directories must be created at agent side and the files must be uploaded in a location accessible to the DS Agent.

Example

Let’s assume that business users will upload .csv files containing transactional data to a specific share drive folder(SharePoint), which is connected with CPI-DS agent server. All the files should be loaded in IBP using CPI-DS and archived (or moved to another folder) after processing, in order to avoid loading them again at the next task execution.

Flat file example:

How to achieve it

Step 1: Create 2 new Datastores in CPI-DS

First datastore will contain the files that should be loaded in IBP. The second one will be used to archive the files after processing.

Enter the name and description for the new directory. The name should be the same as the one created at agent side. For Type select the option “File Format Group”  and the field Root directory should contain the file path maintained at agent side(e.g.: /DSAgent/Test_csv_file). Once all the mandatory fields are completed, click on Save.

Next step is to create the file format. Select from Create file Format the option “Create from Scratch”, if you want to maintain the structure of the file manually or choose the option “Create from Sample” and upload a template file for autofill structure.

Enter the file name, remember to choose the correct Column delimiter and to check the box First row contains column names if this is the case.

The order of the columns and data type are also important. Columns need to be maintained in the same order as they are in the file, otherwise CPI-DS will not map correctly the data. Enter the maximum length for every field that can be in the file.

Create a new datastore which will be used to archive the files after processing, as previous.

Step 2: Create a new task

Once file format is created, it can be used as a Source for required Dataflow:

As Target datastore select IBP:

Step 3: Create a new dataflow inside the task

Select the source file created above. If the requirement is to load all the files from the directory, executing the task once, then simply just put *.csv in the File name box. In this way CPI-DS will merge automatically all the files and load them in IBP.

If the directory will contain multiple files and the requirement is to load just the ones with specific naming convention or pattern (e.g.: IBP_DATA_XXXX.csv), then replace *.csv with IBP_DATA*.csv.

Do the necessary transformations and map all the fields with the corresponding IBP output.

Step 4: Archive the processed files using Postload script

In order to archive (or just move into another folder) them after processing, Postload script must be used.

Function FILE_MOVE will move the files from the initial directory to the archive one. Like this, at the next task execution, this files will not be processed again.

Maintain Global variables:

$G_file_name  – Stores file name that must be archived. If all the files from the folder need to be archived after task execution, use *.csv. Otherwise, use a pattern(e.g.: IBP_DATA*.csv). In both ways, the files will be moved to the second folder.

$G_File_Path – Store the initial path of the files concatenated with global variable $G_file_name. Value can be maintained in the section Global variables or directly in the script.

$G_File_Path_Target – Store the path for the archive folder. Value can be maintained in the section Global variables or directly in the script.

# Use scripts to assign values to variables, call functions, or define delta load properties. 
# For more information, see “Script, Functions, and Global Variables” in the Help. 
# Statements must end with a semicolon (;).


$G_File_Path = '/DSAgent/Test_csv_file/' || $G_file_name;
$G_File_Path_Target = '/DSAgent/Test_csv_Archive';
print ($G_File_Path);
print ($G_File_Path_Target);

file_move(
$G_File_Path,
$G_File_Path_Target,
1);


#End of script;
print('Script finished');

Conclusion

You have seen how to load multiple files (.csv) from directory to IBP using CPI-DS and how you can move them between folders after processing.

Hope this blog helps you in your quest on how to setup the connection and use the FILE_MOVE function.