How to load Files(.csv) in IBP and archive them after processing using CPI-DS
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.
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.
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_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');
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.
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 ?
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.
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?