Applicaiton - DataMapping - transformation

[padmanabhamv]

user case - Read a CVS file (has 10-200 rows) and transform and send it to Target System format (XML) format.

1. I used SFTP download option to get the file, and have content.

Viewing: connectorOutputPayload

[{ "Success": "True", "Content": "Trade Date,RIC,Security Description,Ask Price,Bid Price,Mid Price,Currency Code,Currency Code Description,Base Currency Code\r\n04/24/2024,USDAUDONFIX=WM,Australian Dollar/US Dollar Overnight FX Forward Fixing,.00004,.00003,.00004,USD,US Dollar,AUD\r\n04/24/2024,USDAUDTNFIX=WM,Australian Dollar/US Dollar Tomorrow Next FX Forward Fixing,.00006,.00006,.00006,USD,US Dollar,AUD\r\n04/24/2024,USDAUDSWFIX=WM,Australian Dollar/US Dollar Spot Week FX Forward Fixing,.00014,.00013,.00014,USD,US Dollar,AUD\r\n04/24/2024,USDAUD1MFIX=WM,Australian Dollar/US Dollar 1 Month FX Forward Fixing,.00059,.00058,.00058,USD,US Dollar,AUD\r\n04/24/2024,USDAUD2MFIX=WM,Australian Dollar/US Dollar 2 Month FX Forward Fixing,.00114,.00112,.00113,USD,US Dollar,AUD\r\n04/24/2024,USDAUD3MFIX=WM,Australian Dollar/US Dollar 3 Month FX Forward Fixing,.00169,.00167,.00168,USD,US Dollar,AUD\r\n04/24/2024,USDAUD6MFIX=WM,Australian Dollar/US Dollar 6 Month FX Forward Fixing,.00304,.00296,.003,USD,US Dollar,AUD\r\n04/24/2024,USDAUD9MFIX=WM,Australian Dollar/US Dollar 9 Month FX Forward Fixing,.00422,.00385,.00404,USD,US Dollar,AUD\r\n04/24/2024,USDAUD1YFIX=WM,Australian Dollar/US Dollar 1 Year FX Forward Fixing,.00493,.00476,.00484,USD"]}

Question: How to use a for-each in data mapping to transform in to target system format.

This was a much easier in XSLT, but now what is the best way to implement in Application Integration ?

XSLT:

<xsl:for-each select="/ns0:ForwardPoints/ns0:Data">
<item>
<TradeDate>
<xsl:value-of select="ns0:TradeDate"/>
</TradeDate>
<Ric>
<xsl:value-of select="ns0:RIC"/>
</Ric>
<RicDescp>
<xsl:value-of select="ns0:SecurityDescription"/>
</RicDescp>
<AskPrice>
<xsl:value-of select="ns0:AskPrice"/>
</AskPrice>
<BidPrice>
<xsl:value-of select="ns0:BidPrice"/>
</BidPrice>
<MidPrice>
<xsl:value-of select="ns0:MidPrice"/>
</MidPrice>
<CurrCde>
<xsl:value-of select="ns0:CurrencyCode"/>
</CurrCde>
<CurrCdeDesc>
<xsl:value-of select="ns0:CurrencyCodeDescription"/>
</CurrCdeDesc>
<BcurrCde>
<xsl:value-of select="ns0:BaseCurrencyCode"/>
</BcurrCde>
</item>
</xsl:for-each>

[phertzog]

Hello,

I had a similar need and I choose to make the conversion using a JS Task to make the transformation.

You can perhaps also try to use a Data Transformer task (https://cloud.google.com/application-integration/docs/configure-data-transformer-script-task).

It contains a functions to manipulate Xml and Json like manifestXml (https://cloud.google.com/application-integration/docs/data-transformer-functions-reference).

[padmanabhamv]

The data mapping is very draft stage.. need a lot of improvement for data transformation.

[rohitjangid · Accepted solution]

Hi

You can use parseCsvWithHeader function provided by Data Transformer Task to parse csv to json. The json could be transformer to per your usecase and converted to xml using manifestXml function.

You data transformer script should look similar to:

// Import the additional functions library
local f = import "functions";

local connectorOutputPayload = std.extVar("connectorOutputPayload");
local csvArr = f.parseCsvWithHeader(connectorOutputPayload[0].Content);

local xmlArr = {
    item: [
        {
            TradeData: { "$": row["Trade Date"] },
            Ric: { "$": row["RIC"] },
            RicDescp: { "$": row["Security Description"] },
            AskPrice: { "$": row["Ask Price"] },
            BidPrice: { "$": row["Bid Price"] },
            MidPrice: { "$": row["Mid Price"] },
            CurrCde: { "$": row["Currency Code"] },
            CurrCdeDesc: { "$": row["Currency Code Description"] },
            BcurrCde: { "$": row["Base Currency Code"] },
        } for row in csvArr]
};

{
    xml: f.manifestXml(xmlArr) // This would set value to "xml" named variable
}