SQR Portable File format (SPF) / Parsing/Input

[JamesMalyk]

Hi everyone.. I'm working on a method of processing/parsing an input file with a custom format; the formatting type is SQR Portable File format (SPF); it's a reporting method for transferring data from an external vendor which I cannot modify from its initial state (as that's the format I get it in). It's opened in an application called the 'BRIO Viewer' locally, but there are a hundred different reports we get and they all have different formats.. the unifier between these formats is that they're all structured similarly (I can open them in Notepad); the report data is all ascii characters. But because the report is structured (similarly to a PDF), the file also contains identifiers for how the file is read and structured into the BRIO Viewer.

I located this online developers guide in relation to the construction/formatting of this file type: http://www.is.mcgill.ca/brio/developerguide.pdf

I can't share a file unfortunately of this format as it's very sensitive client data, and I can't mask the data without corrupting the file.

There are no line breaks in the file, it's a continuous string, so importing it with line breaks doesn't work.

However; there are identifiers within the file that break out each line/element for positioning. Some of them look like this.

The idea I have is to:

1. Import the files as one long string (using a flat ascii input) - no line breaks.
2. Use this symbol at the start of each line to act as a parser, to parse row by row the data
3. Use filters to knock out the rows I don't need
4. Identify the rows I have left by this fancy code and applying a secondary field to each line.
5. Regex out these fancy codes.
6. Restructure the data into a table.

The problem I have is that this symbol is not reading in Alteryx, when I bring in the file it shows as a blank.. I've tried filtering it to identify the first character then to apply this against the file and throws an Alteryx error about invalid character usage.. The file contains numeric values which may be similar to these code structures (such as 0100 for 1.00) etc. I've tried using \n and \0, I was thinking of trying to apply an ascii value from the symbol and somehow reworking it but this symbol seems to be only showing like this in Notepad (not Textpad or NotePad++).

If anyone has any ideas it would be appreciated.

Thank you,

James

[JamesMalyk]

Anybody by chance have any suggestions for me to try in relation to this? Sorry to have bumped this post.

Thank you in advance,

James

[JamesMalyk · Accepted solution]

Solved this on my own.. read in flat file, applied formulas using CharToint to locate hex field codes; reversed the hex code back in as a parser and was able to break out the file by fields. Not sure if solutioning my own question is a good thing, or if I should request this to delete? Hope this helps someone at some time :)