[RESOLVED] Parsing String to data row

[Smartacus]

Hi All,

I am reading from an IO.port and returning the data to a string. I need to parse this to data rows that each represent a data record which I will add to a datagridview.

The data basically looks like this. I have inserted <SOH>,<US>, etc. for readability.

<SOH>EM709<US>17946<US>1114<US>62<STX>T<US>12345<US>201106130335<US>01<US>C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB<US>2D<RS>T<US>654321<US>201106130441<US>01<US>9E7F999E7F9EB2ADADB2947F94947F99ADA8A39E<US>07<RS>T<US>420689836<US>201106130508<US>01<US>947F99947F8F9EA8ADB2947F99947F94BCADB7C1<US>05<RS>T<US>123456<US>201106130522<US>01<US>9E7FA39E7F9E99ADBCB7947FA3947F9E94999E9E<US>B4<RS>T<US>765432<US>201106130539<US>01<US>947F99947F9EA3B7B2B78F7F998F7FA3BCB2BCAD<US>1B<RS>T<US>420863802<US>201106130550<US>01<US>9E7F999E7F9499B2ADA8997F99997F99B7B7A899<US>EF<RS>T<US>234567<US>201106130555<US>01<US>9E7F997F7F7F7F7F7F7F7F7F7F7F7F7F7F7F7F7F<US>5D<RS>T<US>876543<US>201106130607<US>01<US>947F99947F9EB7B7B7B7947F94947F9EC1C1BCC1<US>DC<EOT>

The first line is the identification from the equipment

<SOH>EM709<US>17946<US>1114<US>62<STX>

Then each data row will be

<RS>T<US>12345<US>201106130335<US>01<US>C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB<US>2D

T=Test
<US>12345<US> = Person ID
<US>201106130335<US> = Date and 24hour time
<US>01<US> = Technician ID
<US>C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB<US> = Hearing Results
<US>2D<RS>=Impedance

<RS> is the Record Separator

End result is I want to have a row like T|12345|201106130335|01|C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB|2D


I have searched on how to do this and apparently I am not putting in the correct keywords to show an example of what is needed. Help will be appreciated.

[wild_bill]

I would use regular expressions to parse the input string, then save the data to a datatable so you can easily bind it to a grid control.

Dim input = "<RS>T<US>12345<US>201106130335<US>01<US>C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB<US>2D"

'create table to store data
Dim table As New DataTable
table.Columns.Add("Environment")
table.Columns.Add("PersonID")
table.Columns.Add("DateTime")
table.Columns.Add("TechnicianID")
table.Columns.Add("HearingResults")
table.Columns.Add("Impedance")

Dim columnIndex = 0
Dim row As DataRow = Nothing
For Each m As Match In Regex.Matches(input, "(\<[A-Z]+\>)([A-Z0-9]*)")

    'get tag name
    Dim tagName = m.Groups(1).Value

    'check for new row
    If tagName = "<RS>" Then
        'create new row
        columnIndex = 0
        row = table.NewRow
    End If

    'get content
    Dim content = m.Groups(2).Value

    'add content to row
    row(columnIndex) = content

    'add row to table when last column is populated
    If columnIndex = table.Columns.Count - 1 Then
        table.Rows.Add(row)
    End If

    'increment column index
    columnIndex += 1
Next

[.paul.]

try this. i used a datatable + added the fields from the split string, then bound the datatable to the dgv. any additional rows added to the datatable after will automatically be added to the dgv.:

Public Class Form1
 
    Dim dt As New DataTable
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dt.Columns.Add("Test")
        dt.Columns.Add("Person ID")
        dt.Columns.Add("DateTime")
        dt.Columns.Add("Technician ID")
        dt.Columns.Add("Hearing Results")
        dt.Columns.Add("Impedance")
 
        Dim s As String = "<RS>T<US>12345<US>201106130335<US>01<US>C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB<US>2D"
        Dim items() As String = s.Split(New String() {"<RS>", "<US>"}, StringSplitOptions.RemoveEmptyEntries)
        dt.Rows.Add(Array.ConvertAll(items, Function(str) CObj(str)))
 
        DataGridView1.DataSource = dt
    End Sub
 
End Class

[Smartacus]

my mistake here

The first record is going to have an <STX> then
T<US>12345<US>201106130335<US>01<US>C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB<US>2D

then the second and following

<RS>T<US>54321<US>201106130335<US>01<US>C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB<US>2D

[.paul.]

my method will work with both of those strings as long as both contain the same 6 fields

[.paul.]

or do you mean:

<STX>T<US>12345<US>201106130335<US>01<US>C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB<US>2D

???

[.paul.]

Dim items() As String = s.Split(New String() {"<RS>", "<US>", "<STX>"}, StringSplitOptions.RemoveEmptyEntries)

[wild_bill]

I think mine would work too, if you changed
Dim row As DataRow = Nothing
To
Dim row As DataRow = table.NewRow

[Smartacus]

This would be the entire string. It would need to start the first record after <STX>, preceding records at <RS>

<SOH>EM709<US>17946<US>1114<US>62<STX>T<US>12345<US>201106130335<US>01<US>C67FCBC67FBCB7BCCBC6CB7FD0 CB7FBCBCBCC6CB<US>2D<RS>T<US>654321<US>201106130441<US>01<US>9E7F999E7F9EB2ADADB2947F94947F99ADA8A39 E<US>07<RS>T<US>420689836<US>201106130508<US>01<US>947F99947F8F9EA8ADB2947F99947F94BCADB7C1<US>05<RS >T<US>123456<US>201106130522<US>01<US>9E7FA39E7F9E99ADBCB7947FA3947F9E94999E9E<US>B4<RS>T<US>765432< US>201106130539<US>01<US>947F99947F9EA3B7B2B78F7F998F7FA3BCB2BCAD<US>1B<RS>T<US>420863802<US>2011061 30550<US>01<US>9E7F999E7F9499B2ADA8997F99997F99B7B7A899<US>EF<RS>T<US>234567<US>201106130555<US>01<U S>9E7F997F7F7F7F7F7F7F7F7F7F7F7F7F7F7F7F7F<US>5D<RS>T<US>876543<US>201106130607<US>01<US>947F99947F9 EB7B7B7B7947F94947F9EC1C1BCC1<US>DC<EOT>

[.paul.]

ok. i worked something out. there are a couple of extra points... there are spaces in the string i had to remove (typo?) + there's an extra tag <EOT>

Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim fileText As String = IO.File.ReadAllText("C:\Users\Paul\Desktop\string.txt")
        fileText = fileText.Substring(fileText.IndexOf("<STX>")).Replace(" ", "")
        Dim items() As String = fileText.Split(New String() {"<RS>", "<US>", "<STX>", "<EOT>"}, StringSplitOptions.RemoveEmptyEntries)
 
        Dim dt As New DataTable
        dt.Columns.Add("Test")
        dt.Columns.Add("Person ID")
        dt.Columns.Add("DateTime")
        dt.Columns.Add("Technician ID")
        dt.Columns.Add("Hearing Results")
        dt.Columns.Add("Impedance")
 
        For x As Integer = 0 To items.GetUpperBound(0) Step 6
            dt.Rows.Add(New Object() {items(x), items(x + 1), items(x + 2), items(x + 3), items(x + 4), items(x + 5)})
        Next
 
        DataGridView1.DataSource = dt
    End Sub
 
End Class

[Smartacus]

That works.

I am going to leave this open as I work on some data manipulation. I may need to pick your brain some more.

[Smartacus]

A part of the string is datetime that I need to format and insert into separate columns (Date, Time). Date is mm/dd/yyyy, time is military.

T<US>12345<US>201106130335<US>

Would it be better to keep it as is going to the data table then clone it and do the format and split or do it in the first step?

[.paul.]

the easiest way is to split it as you load the dt:

Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim fileText As String = IO.File.ReadAllText("C:\Users\Paul\Desktop\string.txt")
        fileText = fileText.Substring(fileText.IndexOf("<STX>")).Replace(" ", "")
        Dim items() As String = fileText.Split(New String() {"<RS>", "<US>", "<STX>", "<EOT>"}, StringSplitOptions.RemoveEmptyEntries)
 
        Dim dt As New DataTable
        dt.Columns.Add("Test")
        dt.Columns.Add("Person ID")
        dt.Columns.Add("Date")
        dt.Columns.Add("Time")
        dt.Columns.Add("Technician ID")
        dt.Columns.Add("Hearing Results")
        dt.Columns.Add("Impedance")
 
        For x As Integer = 0 To items.GetUpperBound(0) Step 6
            Dim d As DateTime = Date.ParseExact(items(x + 2), "yyyyMMddHHmm", Nothing)
            dt.Rows.Add(New Object() {items(x), items(x + 1), d.ToString("MM/dd/yyyy"), d.ToString("HH:mm"), items(x + 3), items(x + 4), items(x + 5)})
        Next
 
        DataGridView1.DataSource = dt
    End Sub
 
End Class

[Smartacus]

the easiest way is to split it as you load the dt:

Dim d As DateTime = Date.ParseExact(items(x + 2), "yyyyMMddHHmm", Nothing)
            dt.Rows.Add(New Object() {items(x), items(x + 1), d.ToString("MM/dd/yyyy"), d.ToString("HH:mm"), items(x + 3), items(x + 4), items(x + 5)})

The date format is not converting. Debug shows a date like 06/13/2011 03:12 PM. The time should be 24 hour and the cap H's surely indicate that.

System.ArgumentException was unhandled
Message="Input array is longer than the number of columns in this table."

[.paul.]

i just retested it using my code from post #13 (did you notice i split the datetime dt field into 2?), + your string you posted in post #9. i also changed 1 of the times in that string + the 24 hour formatting is working properly...

[Smartacus]

Ah, I found a "stupidity". It works.

[Smartacus]

I am moving along well with this, but I need help breaking items(x + 4) into separate strings like the time an date.

items(x + 4) has 40 characters. I need to split it 20 times so that each string contains 2 characters each.

e.g. C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB would be:

Dim LR(9) As String
Dim RR(9) As String
RR(y) = C6  '1-2 then 
RR(y) = 7F '3-4 then 
RR(y) = CB '4-5 
etc.

LR(Y)= CB '21-22
LR(Y)= 7F '23-24
LR(Y)= DO '25-26

What these are is the values of a hearing test result starting with the Right Ear 1-20 and Left Ear 21-40. Before writing it to the DataTable I will send it through a function to translate it to decimal like Translate(LR(y)) or something like that.

[Smartacus]

Here is the code with the results I was expecting.

Thank you Paul! You have taught me a lot about splits.

Private AUD As New DataTable
    Private LR(19) As String
    Private RR(19) As String
    Private p As Integer
    Private ds As DataSet

Private Sub ParseDetail()
        Dim fileText As String = IO.File.ReadAllText("readbuffer.dat")
        fileText = fileText.Substring(fileText.IndexOf("<STX>")).Replace(" ", "x")

        Dim items() As String = fileText.Split(New String() {"<RS>", "<US>", "<STX>", "<EOT>"}, StringSplitOptions.RemoveEmptyEntries)

        ds = New DataSet("ds")
        AUD = ds.Tables.Add("AUD")
        CreateAUDTable()

        For x As Integer = 0 To items.GetUpperBound(0) Step 6
            Dim newAUDRow As DataRow = ds.Tables("AUD").NewRow()
            Dim d As DateTime = Date.ParseExact(items(x + 2), "yyyyMMddHHmm", Nothing)


            For ss As Integer = 0 To items(x + 4).Length - 1 Step 40
                Dim section As String = items(x + 4).Substring(ss, 40)
                Dim p As Integer = 0

                For i As Integer = 0 To 19 Step 2
                    RR(p) = section.Substring(i, 2)
                    LR(p) = section.Substring(i + 20, 2)
                    p += 1

                Next
            Next

            newAUDRow("Type") = items(x)
            newAUDRow("PersonID") = items(x + 1)
            newAUDRow("tDate") = d.ToString("MM/dd/yyyy")
            newAUDRow("mTime") = d.ToString("HH:mm")
            newAUDRow("TechID") = items(x + 3)
            newAUDRow("R1kv") = Translate(RR(0))
            newAUDRow("R250") = Translate(RR(1))
            newAUDRow("Rd5K") = Translate(RR(2))
            newAUDRow("R1k") = Translate(RR(3))
            newAUDRow("R1p5") = Translate(RR(4))
            newAUDRow("R2k") = Translate(RR(5))
            newAUDRow("R3k") = Translate(RR(6))
            newAUDRow("R4k") = Translate(RR(7))
            newAUDRow("R6k") = Translate(RR(8))
            newAUDRow("R8k") = Translate(RR(9))
            newAUDRow("L1kv") = Translate(LR(0))
            newAUDRow("L250") = Translate(LR(1))
            newAUDRow("Ld5K") = Translate(LR(2))
            newAUDRow("L1k") = Translate(LR(3))
            newAUDRow("L1p5") = Translate(LR(4))
            newAUDRow("L2k") = Translate(LR(5))
            newAUDRow("L3k") = Translate(LR(6))
            newAUDRow("L4k") = Translate(LR(7))
            newAUDRow("L6k") = Translate(LR(8))
            newAUDRow("L8k") = Translate(LR(9))
            newAUDRow("Imped") = items(x + 5)

            ds.Tables("AUD").Rows.Add(newAUDRow)

        Next

        dgvResults.DataSource = ds.Tables("AUD")

    End Sub