Import CSV File (up to 1.1.18)
This section explains how to load a CSV file to a Price Setting or Analytics table.
See also how to Import CSV File (1.2.0 and higher).
Process Diagrams
Loading Data to Analytics Tables
Loading Data to Price Setting Tables
Step by Step
Prepare the recommended project file structure or download the template files:
Download the source CSV file:
Create a route context in camel-context.xml and the XML file in refs/routes.
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>
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>
Create the mapper for a Datamart table in the mappers-filters.xml file.
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>
Convert the price to the decimal number.
Add the decimal converter to the converters.xml file first.
Converter Definition
Convert the field with the defined converter.
Decimal Converter
Parse the date field with the correct date mask.
You have to define the converter for the particular date mask first in the converters.xml file.
Date Converter
Use the converter for the date field.
Date Conversion
Simple language can be used for defining a value
Simple
The header value can be accessed directly.
Header Value
Groovy is used for concatenating fields or date operations.
Groovy
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
Define the endpoint URI and route including properties.
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
Done file option
The file character encoding is set by
csv-file-extra-parameters
using the parameter charset.Moving the processed file is defined by
file-extra-parameters-inbound-move
andfile-extra-parameters-inbound-moveFailed
.
Introduce the route in the carRoutes.xml file.
Cars Route
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
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.
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
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 6.0.0