OIC - Extracting File Name Before the Last Dot and Changing extension from .txt to .csv in OIC

Use Case

In Oracle Integration Cloud (OIC), files are often received with a specific naming convention, such as "abc.01287568371133.yyyymmdd.txt" However, some business processes require converting these .txt files into .csv format dynamically before further processing. Instead of hardcoding file names, we can extract and replace the file extension using XPath functions.

Solution Steps

1. Extract the File Name:

   • Use XPath to fetch the file name from the payload.
   • Example: /ns0:execute/ns3:request-wrapper/ns3:ProcessRequest/ns3:FileDetails/ns3:FileName

2. Find the Last Occurrence of a Dot (.):

   • Identify the position of the last dot (.) in the file name to locate the extension.
   • Function used: oraext:last-index-within-string()

3. Extract the Base Name:

   • Use fn:substring() to retrieve the file name without the extension.

4. Concatenate with .csv:

   • Append .csv to the extracted base name using fn:concat().

Final Expression:

fn:concat(fn:substring(ns3:FileName,1, 

oraext:last-index-within-string(ns3:FileName, '.')),
'.csv')

This converts abc.01287568371133.yyyymmdd.txt → abc.01287568371133.yyyymmdd.csv