Import CSV File (up to 1.1.18)

Owned by Jana Volencová
May 07, 2024
16 min read

This section explains how to load a CSV file to a Price Setting or Analytics table. 

See also how to https://pricefx.atlassian.net/wiki/spaces/IM/pages/5103026382.

Process Diagrams

Loading Data to Analytics Tables

 

Loading Data to Price Setting Tables

Step by Step

  1. Prepare the recommended project file structure or download the template files:

  2. Download the source CSV file:

  3. Create a route context in camel-context.xml and the XML file in refs/routes.

    1. Create the file carRoutes.xml in src/main/resources/refs/routes.

      Car Route

      <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:pfx="http://www.pricefx.eu/schema/pfx" xmlns:util="http://www.springframework.org/schema/util" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd http://camel.apache.org/schema/spring http://camel.apache.org/schema/spring/camel-spring.xsd http://www.pricefx.eu/schema/pfx http://www.pricefx.eu/schema/pfx.xsd"> <routeContext id="carRoutes" xmlns="http://camel.apache.org/schema/spring"> <!-- Cars --> <route id="carData"> </route> </routeContext> </beans>

       

    2.  Add the context to the camel-context.xml file.

      Camel Context

      <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:pfx="http://www.pricefx.eu/schema/pfx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://camel.apache.org/schema/spring http://camel.apache.org/schema/spring/camel-spring.xsd http://www.pricefx.eu/schema/pfx http://www.pricefx.eu/schema/pfx.xsd"> <import resource="classpath:routeErrorHandling.xml"/> <!-- data type converters --> <import resource="refs/converters.xml"/> <!-- input entities mappings --> <import resource="refs/mappers-filters.xml"/> <!-- routes--> <import resource="refs/carRoutes.xml"/> <camelContext xmlns="http://camel.apache.org/schema/spring" useMDCLogging="true" errorHandlerRef="defaultErrorHandler"> <contextScan/> <!-- data routes --> <routeContextRef ref="carRoutes"/> </camelContext> </beans>

       

  4. Create the mapper for a Datamart table in the mappers-filters.xml file. 

    1. In and out fields associate columns from the header with fields in a Pricefx Data Feed.

      Mapper

      <?xml version="1.0" encoding="UTF-8"?> <beans:beans xmlns="http://www.pricefx.eu/schema/pfx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:beans="http://www.springframework.org/schema/beans" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.pricefx.eu/schema/pfx http://www.pricefx.eu/schema/pfx.xsd"> <loadMapper id="carDataMapper"> <body in="sku" out="productId"/> <body in="label" out="Brand"/> <body in="price" out="Price"/> <body in="date" out="AcquiredDate"/> <body in="year" out="YearOfManufacture"/> </loadMapper> </beans:beans>

       

    2. Convert the price to the decimal number.

      1. Add the decimal converter to the converters.xml file first.

        Converter Definition

         

      2. Convert the field with the defined converter.

        Decimal Converter

         

    3. Parse the date field with the correct date mask.

      1. You have to define the converter for the particular date mask first in the converters.xml file.

        Date Converter

         

      2. Use the converter for the date field.

        Date Conversion

         

    4. Simple language can be used for defining a value

      Simple

       

    5. The header value can be accessed directly.

      Header Value

       

    6. Groovy is used for concatenating fields or date operations.

      Groovy

       

  5. The mapper for master data in PriceBuilder requires a different definition. Attributes have to be referenced by an attribute name, e.g. attribute2. Aliases cannot be used.

    Products Mapper

     

  6. Define the endpoint URI and route including properties.

    1. Set the location of the file in the property file. Extended parameters define the retention policy and a flag file when the file is ready and not being transferred.

      Properties

      1. Done file option

      2. The file character encoding is set by csv-file-extra-parameters using the parameter charset.

      3. Moving the processed file is defined by file-extra-parameters-inbound-move and file-extra-parameters-inbound-moveFailed.

         

    2. Introduce the route in the carRoutes.xml file.

      Cars Route

       

  7. Use API pfx-csv:unmarshal to convert the CSV text lines into a list of object map. The API supports following parameters:

    • header – Keeps a list of all column names of the CSV file (in the corresponding order).

    • skipHeaderRecord – Indicates whether IM skips the first line of the CSV file when converting the CSV to a list or not (ideally set this to true when the CSV file contains the headers).

      CSV to list

       

  8. Use API pfx-api:loaddata to load the list to the Pricefx table. The API uses following parameters:

    • objectType – Keeps the short name of the Pricefx object type, such as: P (Product), PX (Product Extension), C (Customer), CX (Customer Extension), DM (Datasource). Their full list is here.

    • mapper – Keeps the reference name to the fields mapper.

    • dsUniqueName – Keeps the unique name of the destination Data Source.

       

  9. Use API pfx-api:flush to flush the data from the Data Feed to a Data Source once the load is successfully completed. The API uses following parameters:

    • dataSourceName – Keeps the unique name of the Data Source (destination).

    • dataFeedName – Keeps the unique name of the Data Feed (source).

      Data Source flush

       

  10. You can optionally truncate the Data Feed after a successful flush. IntegrationManager needs to be set up to consume events from the Pricefx server to get notified. It will follow in the event processing.

IntegrationManager version 5.0.0