Skip to main content

Import positions in a custom CSV file

This sample shows how you can use Camel route and data mapping to import a custom CSV file with portfolio positions to FA. The highlights of the sample are:

  1. Reading a tab-delimited CSV file and converting it to a semicolon-separated file for data mapping.

  2. Creating a data mapping from the received file's format to FA position import format.

  3. Feeding the semicolon-separated file to data mapper and further to the position import process.

Starting point

You have a tab-separated CSV file custom_positions.csv that looks like this:

PORTFOLIO    PRODUCT    CURR    IDENTIFICATION    OFFER    VOLUME    INFOXYZ    ZDATE
1000952    FI0010805360    SEK    DOOBA 24    100.250000    1100000.000000    100.300000    2022-02-10
1000952    NO0012675485    SEK    VARENNE RB 3    0    275400.000000    100.045098    2022-02-10

You need to import the positions to FA. The standard FA file format for importing positions (semicolon-separated) looks like this:

portfolio;security;currency;amount;date
1000952;FI0010805360;SEK;1100000.000000;2022-02-10
1000952;NO0012675485;SEK;275400.000000;2022-02-10

Camel route to change CSV delimiter and feed the file to data mapping

The Camel route below:

  • Reacts to all files with the name that ends with positions.csv (antInclude=*positions.csv) placed in the /integrations/customFiles/ directory.

  • Reads the original file using Camel's CSV file format and tab ("	") as a delimiter.

  • Archives the original file in the monthly folders after processing (move=./history/${date:now:MM_yyyy}/${file:onlyname}).

  • Feeds the processed file in the position import mapping directory and adds the mapping name to processed file name extension (custom_positions.custom-mapping.csv).

<routes xmlns="http://camel.apache.org/schema/spring">
    <route id="customPositionsImportMapping">
        <from uri="file:{{back.writable.dir}}/integrations/customFiles/?antInclude=*positions.csv&amp;autoCreate=true&amp;charset=iso-8859-1&amp;readLock=changed&amp;readLockCheckInterval=5000&amp;moveFailed=.failed&amp;move=./history/${date:now:MM_yyyy}/${file:onlyname}"/>        
        <unmarshal>
            <csv delimiter="&#x9;" ignoreSurroundingSpaces="true" ignoreEmptyLines="true"/>
        </unmarshal>
        <marshal>
            <csv skipHeaderRecord="false" delimiter=";"/>
        </marshal>
        <to uri="file:{{back.writable.dir}}/positionImport/mapped/?autoCreate=true&amp;fileName=${file:onlyname.noext}.custom-mapping.${file:ext}&amp;charset=iso-8859-1"/>
    </route>
</routes>

Data mapping

Data mapping receives the semicolon-separated CSV file custom_positions.custom-mapping.csv that looks as follows:

PORTFOLIO;PRODUCT;CURR;IDENTIFICATION;OFFER;VOLUME;INFOXYZ;ZDATE
1000952;FI0010805360;SEK;DOOBA 24;100.250000;1100000.000000;100.300000;2022-02-10
1000952;NO0012675485;SEK;VARENNE RB 3;0;275400.000000;100.045098;2022-02-10

Before using the route, you need to configure the data mapping in FA Back. Data mapping creation is described in Import files in custom CSV formats to the system. Enter the custom-mapping preference key and import the following custom-mapping definition in the Data mapping window:

[{"rowNr":"1","extCode":"PORTFOLIO","valueMapping":"","FAField":"portfolio","customScript":""},
{"rowNr":"2","extCode":"VOLUME","valueMapping":"","FAField":"amount","customScript":""},
{"rowNr":"3","extCode":"CURR","valueMapping":"","FAField":"currency","customScript":""},
{"rowNr":"4","extCode":"ZDATE","valueMapping":"","FAField":"date","customScript":""},
{"rowNr":"5","extCode":"PRODUCT","valueMapping":"","FAField":"security","customScript":""}]

Position import

After the custom_positions.custom-mapping.csv file is run through the custom-mapping, you receive an outcome file that follows FA file format for importing positions.

In this section: