How to convert a 1 column Datatable into multicolumn Datatable using FixedWidth

Question

I am working with a robotics program called BluePrism to automate reading, interpreting and ultimately working in a mainframe application. The mainframe application is out of date and its session files are not compatible with BluePrism. If it were I wouldn't be attempting what I am about to show you/ask you.

Since we can't use the built in screen reading in BluePrism, why not have the robot copy the whole screen as text? Text manipulation will work so well here I thought.

Anyways I have the robot copying the screen and I can get the screen from the clipboard. No problem. I even have the screen in a DataTable (BluePrism calls them Collections but same objects) where each row is a row from the screen. Awesome.

Well now here is the ask with the code I have trying to do it but failing. I want to split each row in that DataTable using TextFieldParser and FixedWidth since I can't use character delimiters or spaces. And then add that split up row to a new output DataTable. So that I can use it later in BluePrism. The code below compiles. I catch no error. I get no unhandled ones either. What I do get is an empty output DataTable but it has the correct number of rows. So a logic error, but I'll be damned if I see what I did wrong.

Dim colFields(6) As String
Dim line As String
line = ""

Try
    For Each row As System.Data.DataRow In ScrnSectColl.Rows
        line = row("Value")
        Using stringline As New System.IO.StringReader(line)
            Using reader As New Microsoft.VisualBasic.FileIO.TextFieldParser(stringline)
                reader.TrimWhiteSpace = False
                reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.FixedWidth
                reader.SetFieldWidths(17, 9, 9, 17, 10, 10, 8)
                colFields = reader.ReadFields()
                Dim R As DataRow = SplitScrnSectColl.NewRow()
                For i As Integer = 0 To SplitScrnSectColl.Columns.Count - 1
                    R.Item(i) = colFields(i)
                Next
                SplitScrnSectColl.Rows.Add(R)
            End Using
        End Using
    Next row
Catch ex As Exception
    TextParserFailed = True
    'SplitScrnSectColl.Clear()
    ErrorMessage = "Error: Text Field Parser failed " & System.Environment.NewLine & line & System.Environment.NewLine & "Original Error: " & ex.Message
End Try

I know the TextFieldParser is working because I can output the string array colFields to a text file and I get the screen section that I just copied back to me. So the step inside the for loop R.Item(i) = colFields(i) isn't working as intended or the add SplitScrnSectColl.Rows.Add(R) isn't working. No errors though. Just a blank DataTable. Any help would be appreciated.

ScrnSectColl is the input DataTable that is comprised of text rows from the screen. SplitScrnSectColl is the output DataTable of 7 columns all text.

Answer 1

Dim workTable As New DataTable
workTable.Columns.Add("InvNum", GetType(String))
workTable.Columns.Add("BatchNum", GetType(String))
workTable.Columns.Add("InvDate", GetType(String))
workTable.Columns.Add("PO_Num", GetType(String))
workTable.Columns.Add("InvTot", GetType(String))
workTable.Columns.Add("OpenAmt", GetType(String))
workTable.Columns.Add("PayDate", GetType(String))

Dim colFields(6) As String
Dim line As String
line = ""

Try
    For Each row As System.Data.DataRow In ScrnSectColl.Rows
        line = row("Value")
        Using stringline As New System.IO.StringReader(line)
            Using reader As New Microsoft.VisualBasic.FileIO.TextFieldParser(stringline)
                reader.TrimWhiteSpace = False
                reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.FixedWidth
                reader.SetFieldWidths(17, 9, 9, 17, 10, 10, 8)
                colFields = reader.ReadFields()
                Dim R As DataRow = workTable.NewRow()
                For i As Integer = 0 To workTable.Columns.Count - 1
                    R.Item(i) = colFields(i)
                Next
                workTable.Rows.Add(R)
            End Using
        End Using
    Next row
    SplitScrnSectColl = workTable
Catch ex As Exception
    TextParserFailed = True
    'SplitScrnSectColl.Clear()
    ErrorMessage = "Error: Text Field Parser failed " & System.Environment.NewLine & line & System.Environment.NewLine & "Original Error: " & ex.Message
End Try

I defined a temp workTable and then later assigned it to the output DataTable SplitScrnSectColl. It works! Thank you.