Search code examples
vbastringloopssplithp-uft

Split a cell in UFT

I have a text document (.txt), there I have n lines that have to be split up, but the point is I don't have delimiter. I know the length of each variable that doesn't change.

For example, the first variable is the from the 25 character to 35; the second one, from 36 to 47; then from 48 to 78, then from 79 to 119, and this until the 360th character of the line.

I guess that the solution is by double loop, one for each line and the other one for each variable, but I cannot get it.

If you need more information just ask, I am completely lost.

Thankfully,

Solution

  • Steps you need to take:

    1. Open the file
    2. Read a line
    3. Confirm the line is 360 characters
    4. Assign chunks of the line to different variables
    5. Do things with the variables
    6. Read another line and repeat until EOF

    1 & 2: Your workbook needs a reference to the Microsoft Scripting Runtime in order to give you access to the FileSystemObject. I'll let you research that.

    Create a FileSystemObject and use that to create a TextStream with the path to your file.

    currentLine = textStream.ReadLine()
Do Until textStream.EOF
    If Len(currentLine) = 360 Then
        firstChunk = Mid$(currentLine, 25, 10)
        secondChunk = Mid$(currentLine, 36, 11)
        thirdChunk = Mid$(currentLine, 48, 30)
        fourthChunk = Mid$(currentLine, 78, 30)

        ' Do stuff with chunks
    End If
    currentLine = textStream.ReadLine()
Loop

    In due course you could get fancy and have an array populated with paired items detailing the starting point of a chunk and how many chars it is, something like:

    Dim arrChunkPoints As Variant
Dim arrChunks As Variant
arrChunkPoints = Array(25,10, _
                       36,11, _
                       48,30, _
                       78,30)
ReDim arrChunks(UBound(arrChunkPoints)\2)   ' Integer returned

    This would allow you to step over the items in arrChunkPoints and populate each element of arrChunks with a section of currentLine using Mid$(), but populated with the values from arrChunkPoints. But this is probably for another day.