SAP Data Services – Easy way to split long string into equal parts

Sometimes you need to split long (or very long) string into equal parts (e. g. to load it to STXL, STXH SAP Tables). There is pretty easy way to do it, using SAP Data Services standard tools and transforms:

Source data

As a source, we have string with length 617 symbols, and we want to split it into equal pieces of 132 symbols each.

Data Flow

To build this Data Flow we need only 1 Row Generation transform, source (e.g. Excel workbook format), 2 Query transforms and target (e. g. template table)

1. Row Generation transform: Row number starts at 0, Row count equals Length of the longest string divided by Chunk size (Round up the result), Join rank is 0.
2. Query transform “Split”: Map DI_ROW_ID from Row_Generation and create new Substring column of desirable size. Change Substring mapping as it shown below:

   substr(<source_string>, (Row_Generation.DI_ROW_ID*<chunk_size>)+1, <chunk_size>)​

   

3. Query transform “Get_Not_Null”:  Row Count (see paragraph 1)  corresponds to amount of substrings, that will be generated for each long string. If you have multiple strings in your source dataset, there will be NULL values generated for shorter ones. Exclude such records on WHERE tab.

Result

After executing the job, you will get splitted string(s), as shown below:

Main disadvantage of given approach is Row Count, that you have to calculate and set in Row_Generation transform. But anyway, it is much faster than create bunch of columns with substr() functions + Pivot transform or than create custom functions/user defined transforms.