Input Data File that has Multiple Sections

[WWillis]

Please pardon the basic question as I am new to Alteryx but have a complex input file to import and I am unable to find solutions in the community nor the learning videos.  I have built a handful of workflows but this file is more advanced then I am used to using.

I am trying to create a table of records based on Oracle HFM journal extract.  The extract uses a semicolon “;” as a delimiter. 

I will attach a screenshot of the .txt file.

I prepped the file completing the following steps.

• I converted Oracle's HFM journal extract from .jlf to .txt.
• I then removed all miscellaneous document header rows leaving only the three primary information that is applicable to all records (journals) in the file.

The file is a .txt file and there are three issues that I am trying to solve.  

1. The first issue is how to move the first three document header rows to each record.
   • The document header information applicable to all journals begins with an explanation point “!”.
   • Each journal record needs to have ACTUAL, 2023, JUL.
   • How can the document header information be added to each record?

2. The second issue is how to move the journal header information to each record.
   • Each journal is a separate section in the file.
   • Each new journal begins with an explanation point “!” followed by JOURNAL. (!JOURNAL).
     •       The first part of !JOURNAL is a unique identifier for each journal and needed for each record for that specific journal.
     •       The only part needed for each record for that specific journal is the value.   In this example it is <Entity Curr Adjs> but can be anything in the 5th location.
   • !DESC row can be ignored.
   • How can the journal ID and value be moved to the rows of the journal?

3. The third issue is how to pull in all journals listed as separate sections in the file.
   • The first row of the journal has no delimiter at the beginning of the row.  It can begin with any letter or number.
   • The number of rows to each journal can be 2 or more rows.  The picture shows 4 rows but some exceed 700 rows.
   • I have tried using Input Data but it only identifies the first journal and stops before the next journal.
   • How to pull in all journals in the file listed in separate sections and not just the first journal?

Some help and guidance would be so appreciated.

[Luke_C]

Hi @WWillis 

For this I'd suggest using a multi-row formula tool to flag each section. 

• First, filter out any empty rows, !DESC rows, etc
• Then in a multi-row formula tool you could make an ID with something along the lines of 

  if startswith([Field1],'!JOURNAL') then [Row-1:ID]+1 else [Row-1:ID] endif

  Then you'll be in a position to start splitting out the data as needed.

[DanielG · Accepted solution]

@WWillis -- check this out and see if it works for you.  I'd bring it in as a non-delimited file and work some magic within the workflow.  My attempt is rough, so I am sure you can fine tune the REGEX in there a bit.  Hope it helps.  :)

[WWillis]

DanielG,

Yes!   Thank you.  You did address my issues and found the solution.

I could not even get the file to import as I did not know you could import as a non-delimited file.  As such it kept failing to import with all the required information.   Your solution is exactly what I was researching.   And you are correct, I will be using RegEx to continue to parse the information as required.

Additionally, you solved my other two issues on how to parse and then pivot the document header and journal header information joining to the journal records.

I cannot express how grateful I am for your help.

Deepest of Thanks,

WWillis

[DanielG]

@WWillis  -- glad to help.  Alteryx is such a fun tool, so I was actually excited by the problem you presented.  (I know that might sound a little sad...  😀 haha)