Import Excel from FTP to Pricefx

This template fetches a XLSX file from an FTP location (configured as a connection) and loads its data to a selected Pricefx table using a defined mapper. The template can also handle the Excel file as a ZIP or GZ archive.

Note the following limitations:

• For each file, it is only ever one-to-one relationship with the table being loaded. You cannot split one Excel file into various Pricefx tables.

• Data Sources loads do not automatically trigger flushes (needs to be scheduled in Pricefx).

• Files are selected by the folder they are placed in, rather than file name patterns.

Installation

Go to PlatformManager > Marketplace, find Import Excel from FTP to Pricefx, select Deploy and then select a target integration instance and template version.

Configuration Steps

1. Enter a name for the template deployment and select Pricefx connection (or create a new one).

2. Choose the SFTP connection (or create a new one).

3. In Integration Properties:

   • Use .done file – If Yes is chosen, there has to be an empty marker file with the “.done” suffix added along with the actual data file. For example, if there is a data file named products.csv, there has to be another file products.csv.done added. This “.done” file is a signal saying that the file is done being uploaded and ready to be processed.
     If No is chosen, you only need to upload the data file, without any additional marker files. This would be mostly used when the uploads are so small that network issues are unlikely to interrupt the upload.

   • Archive processed files – Files are archived (moved to the archive folder) after they are processed.

   • Use configured business keys – Choose Yes if you want to define your own custom business keys to be used when uploading data to Pricefx.

   • Comma separated business keys – Only used if configuring business keys is set to Yes. A list of comma separated business keys that will be used when uploading data to Pricefx.

   • Length of each business key – Define here length of each business key. If there are more business keys, specify more values in the same order and separate them with a comma.

   • Use direct load for Data Source (for DMDF/DMDS entities only) – Uploads data directly to the Data Source.

   • Flush data automatically after loading to Data Feed (for DMDF/DMDS entities only) – If set to Yes, the data will be moved to the Data Source. Successfully flushed data will be truncated from the Data Feed. This option works only if the previous option "Direct load" is set to No.

   • Perform internal copy after loading data (for DMDF/DMDS entities only) – Works only for Product, Customer, PX and CX master data tables. It allows you to execute internal-copy data load action to the Analytics module. This way you can keep the Product/Customer data in Price Setting and Analytics modules in sync.

   • Directory path – Path to the directory where uploaded XLSX files go. This path is appended to a path defined in the FTP connection. For example, if the path defined in the connection is “/filearea/inbound” and this parameter is set to “/transactions”, the template will use files from a directory “/filearea/inbound/transactions“.

4. In the XLSX Mapper step, upload a sample Excel to review field types and edit parsing options. Parsing options are auto-detected, so you may not need to change them.

   • Separator – What character separates each column in the XLSX?

   • Quote Character – What character surrounds quotes when necessary?

   • Escape Character – If you need to use a special character inside of a string, how do you show that it is not to be interpreted as a special character? For example, if the string is “Mock Company Name, Inc.” but if the comma is also the separator between columns, it would cause issues. The data would usually be uploaded as “Mock Company Name\, Inc.“. In that case, '\’ would be the escape character.

   • Decimal Separator – What symbol do you use to split the whole number and its decimal parts?

   • Date Format – Define the date format used in the inbound XLSX so that it is properly parsed into date fields in Pricefx.

   • Uploaded file contains header – Indicate whether the XLSX file contains a header.

5. In Entity Type select the table in Pricefx to upload to and map the import file columns to Pricefx columns.
   Also, decide how empty values should be passed on. You have two options:

   • Send empty value as empty string ""

   • Send empty value as NULL

6. Click Continue to complete the deployment.