How to split entire file on two delimiters?

[#1]

Any help is appreciated.

I have a .dat file, with sections separated by ETX and SOH Ascii characters. I can split it by one or the other in Qlikview, but I need it by both. Is there a way to do this, or a way to replace all ETX with SOH in the entire file, and then I can delimit on just SOH?

The data in the document looks like this, and extends down for several rows.

orig_source[ETX]SO[SOH]header_created_by[ETX]Joe[SOH]header_created_date[ETX]06/02/17[SOH]

[sunny_talwar]

Are you loading this from a text file?

[#3]

A .dat file

In my example code, the [ETX] is where an actual ETX character is

[daveamz · Accepted solution]

Hi Aaron,

Have a look at the attached file. I've used SubField and preceding load.

I'm not sure if this is the best solution for your data set, but I hope it will give you at least an idea of how this can be tackled.

Regards,

David

[#5]

With this solution, would I have to do this for each row or column?

The actual data I'm working with extends on for much longer and deeper.

[daveamz]

I'm sure that the real data set is much more complex and requires more tweaks.

In the attach there is an example of how the previous solution can be further extended. I've duplicate the rows, but I suppose in your .dat file, the rows are not separated by [CR][LF] special chars.

If the .dat structure is too complex I suggest you to look for an external tool (like a power shell script, python script etc.) to convert it to csv or some other readable format for qlik.

DAT files are pretty generic and there is no out of the box tool to suit every .dat file. You can read more here: https://www.lifewire.com/what-is-a-dat-file-2620550

Regards,

David

[#7]

Thank you for the extensive response. I got it figured it out now, thanks.