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 5.8.0