[RESOLVED] Parsing String to data row
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.
Code:
<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.
Re: Parsing String to data row
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.
Code:
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
Re: Parsing String to data row
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.:
vb Code:
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
Re: Parsing String to data row
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
Re: Parsing String to data row
my method will work with both of those strings as long as both contain the same 6 fields
Re: Parsing String to data row
or do you mean:
<STX>T<US>12345<US>201106130335<US>01<US>C67FCBC67FBCB7BCCBC6CB7FD0CB7FBCBCBCC6CB<US>2D
???
Re: Parsing String to data row
vb Code:
Dim items() As String = s.Split(New String() {"<RS>", "<US>", "<STX>"}, StringSplitOptions.RemoveEmptyEntries)
Re: Parsing String to data row
I think mine would work too, if you changed
Dim row As DataRow = Nothing
To
Dim row As DataRow = table.NewRow
Re: Parsing String to data row
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>
Re: Parsing String to data row
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>
vb Code:
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
Re: Parsing String to data row
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.
Re: Parsing String to data row
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?
Re: Parsing String to data row
the easiest way is to split it as you load the dt:
vb Code:
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
Re: Parsing String to data row
Quote:
Originally Posted by
.paul.
the easiest way is to split it as you load the dt:
vb Code:
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."
Re: Parsing String to data row
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...
Re: Parsing String to data row
Ah, I found a "stupidity". It works.
Re: Parsing String to data row
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:
Code:
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.
Re: Parsing String to data row
Here is the code with the results I was expecting.
Thank you Paul! You have taught me a lot about splits.
Code:
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
