informaticainformatica-powercenterinformatica-powerexchange

Informatica new line and carriage return

I have file where for one of field data is coming with new line and carriage return(CRLF). Issue is DATA IS not loading as expected due to this as we have row delimeter as \n and due .Example:

Input :

attr_value¬id¬wf_id¬date
select 'x';select a.id
,b.id from test
union  all
select 'y',select z.id
,c.id from test1¬123¬567¬2024-02-05 00:12:10
select 'j';select k.id
,l.id from test
union  all
select 'm',select r.id
,u.id from test1¬45¬67¬2024-02-05 00:13:10

So attr_id contains new line and carriage return CRLF as informatica is on windows. I have tried REPLACESTR (1, attr_value, CHR (10), CHR (13), '') but its not working, data type of attribute field is text and target is also text.If anyone can pls advise if there is any way to load the data as it is in target or if in case there is any other alternate option as currently its treating all these rows as seperately and loading 2 records as 10 records

Solution

  • There is no simple Informatica option for such case.

    Since this is a flat file as a source, replace the newline characters from the file contents before reading it with a Pre-Session command, like:

    Select-String -Path "your_file.txt" -Pattern "select\r\nfrom" -AllMatches -Replace "select from" | Out-File -FilePath "new_file.txt"

    The new_file.txt will contain your input with the new lines removed.

    Alternatively, you can change your source to command and use the below one:

    Select-String -Path "your_file.txt" -Pattern "select\r\nfrom" -AllMatches -Replace "select from"

    This will not create additional temporary file, yet will read the result of the command, rather than the file itself.