Breadcrumbs

Export Data to SFTP

Overview

Fetches data from Pricefx and writes it as a CSV file to an SFTP server. Uses batched mode for large datasets.

Prerequisites

Define the SFTP connection in connections/sftp-server.json (see Connection Examples > SFTP Connection Setup).

Filter: filters/export-filter.xml

XML
<filters>
    <filter id="exportFilter">
        <and>
            <criterion fieldName="attribute1" operator="equals" value="ACTIVE"/>
        </and>
    </filter>
</filters>

Route: routes/export-to-sftp.xml

XML
<routes xmlns="http://camel.apache.org/schema/spring">
    <route id="exportPricesToSftp">
        <from uri="scheduler://exportTrigger?delay={{export.interval.ms}}"/>
        <to uri="pfx-api:fetch?objectType=PX&amp;filter=exportFilter&amp;batchedMode=true&amp;batchSize=5000"/>
        <split>
            <simple>${body}</simple>
            <to uri="pfx-api:fetchIterator"/>
            <to uri="pfx-csv:marshal"/>
            <to uri="pfx-sftp:upload?connection=sftpServer&amp;fileName={{sftp.path}}/prices_${date:now:yyyyMMdd_HHmmss}.csv&amp;fileExist=Append"/>
        </split>
    </route>
</routes>

Properties:

export.interval.ms=3600000
sftp.path=/incoming/prices

How It Works

  1. scheduler:// triggers the route periodically (every hour in this example)

  2. pfx-api:fetch with batchedMode=true returns batch descriptors instead of data

  3. split + pfx-api:fetchIterator fetches each batch — avoids loading all records into memory

  4. pfx-csv:marshal converts each batch to CSV text

  5. pfx-sftp:upload appends each batch to the same file on the SFTP server

Common Pitfalls

  • Use batchedMode=true for large exports to avoid OutOfMemoryError

  • fileExist=Append is needed when writing batches to the same file — without it, each batch overwrites the previous

  • The timestamp in fileName ensures a new file is created each run if you remove fileExist=Append