Using TI process to parse data from a text file

[M1ndbender]

Hello,

I am totally new to this and was wondering if I could be pointed in the right direction to learn more about parsing a text file to another file in a proper format.

The data comes in like this and I am trying to do things like get the values between the **, capture the date, and all other data


** PRODUCT ** 11/25/2022 10:03:03 EST

SAINT JOHN, NB
Prd1 Prd2 Prd3
light heavy special light heavy special
supplier u 80.25 -- -- 90.50 -- -- -- -- -- --
supplier 2 u 80.25 60.25 118.70 -- -- -- -- -- --
RANGE - LOW 80.25 102.99 118.70 -- -- -- -- -- --
RANGE - HIGH 80.25 152.66 121.50 -- -- -- -- -- --
AVERAGE 80.25 138.20 120.10 -- -- -- -- -- --

[Wim Gielis]

In general this can be done but its tedious as TI does not have a lot of string manipulation functions nor regular expression support.

You could parse the file using a different language like Python or Vbscript or PowerShell or others. Have TI call the tool which in turn processes the file(s).

If you do this TI, set the connect to the file. Make sure that the delimiter is a weird character that does not appear in your text file. Like that the entire contents of a line will appear in one variable, say V1.

Then you can parse the lines one after the other.

Make use of a temporary cube or dimension or attribute or rule to simplify the work.

Note that empty lines will not appear in TI, they are skipped. So doing linenumber +2 for example will not work when empty lines are not coming by.

[ascheevel]

Your best bet imo is to work with your ETL team to get a better source file.

[M1ndbender]

Thank you for the reply. I have a ticket open with our ETL team to look at this but they are notorioulsy slow and I am trying to deply something soon.

Do you have an example of parsing the file in VB?

[Wim Gielis]

Hello,

Sure. Here is vbScript code to open up a file, read its contents, manipulate it, and write back to a different file.

Code: Select all

  Dim SourceRUX, Target_RUX, sq

  With CreateObject("Scripting.FileSystemObject")

    Source_RUX = .GetAbsolutePathName(".") & "\" & WScript.Arguments(0)
    Target_RUX = WScript.Arguments(1)

    'get the RUX file contents and split on carriage returns
    sq = Split(.OpenTextFile(Source_RUX).ReadAll, vbCrLf)

    'fill up completely empty lines in the file
    For i = 0 to UBound(sq)
      sq(i) = Trim(sq(i))
      If Len( sq(i) ) = 0 Then
        sq(i) = "[EMPTY LINE]"
      End If
    Next

    'write the array to a new .RUX file
    .CreateTextFile(Target_RUX, 2).Write Join(sq, vbCrLf)

  End With

The example here is about populating completely empty lines with some text.
The script takes 2 parameters.