How can I dynamically parse flat file with nested values?

[former_member558803]

I have a string I need to parse through and load into a table, but the string has multiple levels within it, and no delimiters. For example:

010203040519012717400607081901271740

01 is a device number
02 is the number of reports coming in the string that relate to that device number
03 is report data
04 is report data
05 is report data
1901271740 is the datetime the report was run

I had the series repeat to show the number of reports. Logically it would look like:

01 - Device Number 02 - Number of Reports
First report (03, 04, 05, datetime)
Second report (06, 07, 08, datetime)

The end result in the table I'd like would be

Device Number Report Data1 Report Data2 Report Data3
01 03 04 05
01 06 07 08

The series that contains the number of reports is 44 characters long, then the reports are 40 characters. This repeats, going over 1000 characters. I thought one way to do this would be to create a loop catching the device number and number of reports, setting the number of reports as a counter, then getting the next 40 characters for the number of times in the counter, then repeating this until reaching the end of the string. Would that solution work? Is there a better way?

[former_member187605 · Accepted solution]

i would build a custom function that inserts xml-tags into the string. The result could look similar to this:
<dn>01</dn><nrep>02</nrep><rd><rep>03 04 05</rep><dt>1901271740</dt></rd><rd><rep>06 07 08</rep><dt>1901271740</dt></rd>
This can be achieved this by counting characters in the input string, as you indicate. Check https://wiki.scn.sap.com/wiki/display/EIM/How+to+split+a+comma+separated+String+into+multiple+rows+v... for a somewhat similar use case.
Map the input to this format in a first Query transform.
Create an .xsd file for the XML format. Import it into DS.
Call the extract_from_xml built-in function in a second Query transform, with the xsd name as second parameter.
Unnest the result in a third Query transform. Get rid of unnecessary data and write to a target table.

[former_member558803 · Accepted solution]

Thanks, dirk.venken. I used the loop idea and trimmed the string after each column was parsed. It worked a treat!