In this article we will look into how to load the escape and new line characters into a field using HDL file.
Normally we load data from business objects using HDL file and we face issues if the underlying data itself has a | (pipe) value or if you want to load the new line character.
Here is where the escape characters come to our rescue. When we add the escape characters to the HDL file they will allow the delimiter value for the field.
COMMENT ##############################################################################
COMMENT Business Entity : Location
COMMENT Updates the location record 03-Sun Corporation Corporate HQ to nullify AddressLine2 attribute, to have | and new line characters in description attribute.
COMMENT ##############################################################################
METADATA|Location|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|Description|AddressLine2
MERGE|Location|USER03|33735_LOCATION|0001/01/01|4712/12/31|03-Sun Corporation | Corporate \n Head Quarters|#NULL
The above HDL will load the Description as
03-Sun Corporation | Corporate
Head Quarters
By default, these characters are reserved for HDL:
Delimiter (vertical bar |)
Newline character (n)
Escape (backslash \)
However these characters can be overridden in the HDL with SET Commands.
SET FILE_ESCAPE *
SET FILE_DELIMITER ,
SET FILE_NEW_LINE newline
Now, if we have to prepare the above HDL file with these overrides, it would look like below:
COMMENT ##############################################################################
COMMENT Business Entity : Location
COMMENT Updates the location record XX-Moon Corporation Corporate HQ to nullify AddressLine2 attribute, to have | and new line characters in description attribute.
COMMENT ##############################################################################
SET FILE_ESCAPE *
SET FILE_DELIMITER ,
SET FILE_NEW_LINE newline
METADATA,Location,SourceSystemOwner,SourceSystemId,EffectiveStartDate,EffectiveEndDate,Description,AddressLine2
MERGE,Location,USER03,33735_LOCATION,0001/01/01,4712/12/31,03-Moon Corporation | Corporate *newline Head Quarters,#NULLThis is how the escape, new line and delimiter overrides can be used.
Note: Whenever you are overriding these defaults, you need to use the SET commands in the same HDL file before starting the METADATA line else these overrides don’t work.
