Skip to Content
Technical Articles
Author's profile photo Iuliana Anghel

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.

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo BhanuGopiKrishna Tiruveedula
      BhanuGopiKrishna Tiruveedula

      Hi Luliana,

      This blog is very helpful. And I have a question.

      Why the second datastore for archiving is required ? We can archive the file by just giving target path in 'file_move' function, right ?

      Thanks.

      Author's profile photo Aravind Chitram
      Aravind Chitram

      In my understanding, file move function works only with an absolute path which means it does not work with the network path. I followed the blog post hoping the other datastore would help, but it did not. The file move did not work for the network path while it worked flawlessly for a local path.

      The second datastore is not being used anywhere in the job. Hence there is no reason for it to be created.

      I have no doubt the above works without the second datastore, but wont work with the file path mentioned. 

      Author's profile photo Aravind Chitram
      Aravind Chitram

      Iuliana Anghel

       

      Hi, thanks for the article. Could you please clarify the use of the second datastore? If its not being used in the task, what is the reasoning behind creating it?