Export Data to CSV

This page explains how to fetch data from Pricefx and export it to a CSV file using the Integration Manager.

Basic Example

Fetch all Product Extensions matching a filter and write them to a CSV file:

XML

<filter id="pxCarsFilter">
   <and>
      <criterion fieldName="name" operator="equals" value="Cars"/>
   </and>
</filter>

<route>
  <from uri="timer://fetchData?repeatCount=1"/>
  <to uri="pfx-api:fetch?filter=pxCarsFilter&amp;objectType=PX"/>
  <to uri="pfx-csv:marshal"/>
  <to uri="file:test?fileName=data1.csv&amp;fileExist=Append"/>
</route>

pfx-api:fetch Options

The pfx-api:fetch endpoint retrieves data from Pricefx. Below are the most commonly used parameters.

Object Type

The objectType parameter specifies what kind of data to fetch:

Value	Description
P	Products
PX	Product Extensions
C	Customers
CX	Customer Extensions
DM	Datamarts
DMDS	Data Sources
LTV	Lookup Table Values (Pricing Parameters)
Q	Quotes
QLI	Quote Line Items

Filtering

You can filter data using either a named filter or inline sql:

Named filter:

XML

<to uri="pfx-api:fetch?filter=myFilter&amp;objectType=PX"/>

SQL-like query:

XML

<to uri="pfx-api:fetch?sql=SELECT sku,name,attribute1 WHERE name='Cars'&amp;objectType=PX"/>

Field Selection

resultFields — comma-separated field names to include in output (fetches all from DB, returns as Map)
valueFields — comma-separated field names (fetches only these from DB, returns as list — better performance)

XML

<to uri="pfx-api:fetch?objectType=P&amp;valueFields=sku,name,attribute1"/>

Sorting

Use sortBy to control the order of results (overrides any sortBy defined in the filter):

XML

<to uri="pfx-api:fetch?objectType=PX&amp;filter=myFilter&amp;sortBy=sku"/>

Other Options

Parameter	Default	Description
`distinctResults`	false	Add DISTINCT clause to results
`enableNullFields`	false	Include null fields in output
`startRow` / `endRow`	—	Manual pagination range
`dsUniqueName`	—	For Datamarts/Data Sources (e.g. `1669.DMDS`)
`pricingParameterName`	—	For Lookup Table Values
`connection`	pricefx	Named Pricefx connection to use

Batched Export (Large Datasets)

For large datasets, use batchedMode=true with split to avoid loading all records into memory at once:

XML

<route>
  <from uri="timer://fetchData?repeatCount=1"/>
  <to uri="pfx-api:fetch?filter=pxCarsFilter&amp;objectType=PX&amp;batchedMode=true&amp;batchSize=5000"/>
  <split>
    <simple>${body}</simple>
    <to uri="pfx-api:fetchIterator"/>
    <to uri="pfx-csv:marshal"/>
    <to uri="file:export?fileName=data.csv&amp;fileExist=Append"/>
  </split>
</route>

Parameter	Default	Description
`batchedMode`	false	Enable batched fetching
`batchSize`	5000	Number of records per batch

The first pfx-api:fetch call (with batchedMode=true) returns a list of batch descriptors. The split iterates over them, and pfx-api:fetchIterator fetches each batch using state from the exchange. Always use fetchIterator inside the split — not a second fetch.

pfx-csv:marshal Options

The pfx-csv:marshal endpoint converts fetched data into CSV format.

Parameter	Default	Description
`delimiter`	`,`	Field delimiter character
`header`	—	Comma-separated header names for output columns
`skipHeaderRecord`	false	Omit the header row from output
`quoteCharacter`	`"`	Character used to quote fields
`quoteMode`	—	Quote mode (e.g. `NON_NUMERIC`, `MINIMAL`)
`quoteDisabled`	false	Disable quoting entirely
`escapeCharacter`	—	Escape character
`recordSeparator`	—	Line ending: `CRLF`, `LF`, `CR`
`nullString`	—	String representation for null values
`trim`	false	Trim whitespace from values
`headerComments`	—	Comments to include before the header

Example with custom delimiter and headers:

XML

<to uri="pfx-csv:marshal?delimiter=;&amp;header=SKU,ProductName,Price&amp;skipHeaderRecord=false"/>

File Output Options

The standard Camel file: endpoint writes to the local filesystem:

XML

<to uri="file:{{data.directory}}/export?fileName=cars-${date:now:yyyyMMdd-HHmmss}.csv&amp;fileExist=Append"/>

Parameter	Description
`fileName`	Output filename (supports Camel expressions like `${date:now:yyyyMMdd}`)
`fileExist`	Action if file exists: `Append`, `Overwrite`, `Ignore`, `Rename`

Export to SFTP

To upload the CSV to a remote SFTP server instead of saving locally:

XML

<route>
  <from uri="timer://fetchData?repeatCount=1"/>
  <to uri="pfx-api:fetch?filter=pxCarsFilter&amp;objectType=PX"/>
  <to uri="pfx-csv:marshal"/>
  <to uri="sftp:{{ftp.address}}:{{ftp.port}}?username={{ftp.username}}&amp;password={{ftp.password}}&amp;fileName={{ftp.path}}/export_${date:now:yyyyMMdd}.csv&amp;useUserKnownHostsFile=false"/>
</route>

Use {{property}} placeholders instead of hardcoded credentials. Configure them in your application.properties file.

Delta / Incremental Export

To export only records modified since the last run, use a filter with lastUpdateDate:

XML

<filter id="deltaFilter">
  <and>
    <criterion fieldName="name" operator="equals" value="Cars"/>
    <criterion fieldName="lastUpdateDate" operator="greaterThan" value="${header.lastExportTimestamp}"/>
  </and>
</filter>

<route id="deltaExport">
  <from uri="timer://deltaExport?repeatCount=1"/>
  <to uri="pfx-api:fetch?filter=deltaFilter&amp;objectType=PX&amp;batchedMode=true&amp;batchSize=5000"/>
  <split>
    <simple>${body}</simple>
    <to uri="pfx-api:fetchIterator"/>
    <to uri="pfx-csv:marshal"/>
    <to uri="file:{{data.directory}}/export?fileName=cars-delta-${date:now:yyyyMMdd-HHmmss}.csv"/>
  </split>
</route>

Fetching Datamarts and Pricing Parameters

Data Source (DMDS):

XML

<to uri="pfx-api:fetch?objectType=DM&amp;dsUniqueName=1669.DMDS&amp;batchedMode=true&amp;batchSize=5000"/>

Pricing Parameters (Lookup Table Values):

XML

<to uri="pfx-api:fetch?objectType=LTV&amp;pricingParameterName=ExchangeRate&amp;batchedMode=true&amp;batchSize=5000"/>