Import Data from Excel | Pricefx Knowledge Base

Overview

Reads an Excel file (.xlsx) using pfx-excel:read and loads the data into Pricefx. The Excel component reads a specified sheet and returns rows as a List<Map<String, Object>>.

Mapper: mappers/product-excel-mapper.xml

XML

<mappers>
    <loadMapper id="productExcelMapper">
        <body in="SKU" out="sku"/>
        <body in="Product Name" out="label"/>
        <body in="Currency" out="currency"/>
        <groovy expression="body['Unit Price']?.toBigDecimal()" out="attribute1"/>
        <body in="Category" out="attribute2"/>
    </loadMapper>
</mappers>

Note: in values must match the Excel column header names exactly (case-sensitive).

Route: routes/import-from-excel.xml

XML

<routes xmlns="http://camel.apache.org/schema/spring">
    <route id="importProductsFromExcel">
        <from uri="file:{{import.fromUri}}?include=.*\\.xlsx"/>
        <to uri="pfx-excel:read?sheetName={{excel.sheetName}}&amp;headerRow=0"/>
        <to uri="pfx-api:loaddata?objectType=P&amp;mapper=productExcelMapper"/>
        <onCompletion onCompleteOnly="true">
            <to uri="pfx-api:internalCopy?label=Product"/>
        </onCompletion>
    </route>
</routes>

Properties:

import.fromUri=import/excel
excel.sheetName=Products

How It Works

file: component watches the import/excel directory for .xlsx files
pfx-excel:read reads the specified sheet — headerRow=0 means the first row contains column names
The body becomes a List<Map<String, Object>> where each Map is one row, keyed by column header
pfx-api:loaddata loads the rows using the mapper

Common Pitfalls

Column header names in the mapper in attribute must match the Excel headers exactly including spaces and casing
pfx-excel:read loads the entire file into memory — for very large files (100k+ rows), prefer CSV
Date cells are returned as java.util.Date objects — use a Groovy expression to format them: body.Date?.format('yyyy-MM-dd')
sheetName is case-sensitive and must exactly match the sheet tab name in Excel