Code:
Imports System.Text.RegularExpressions
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim arrFeed As String = "[{'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1100-02', '5': 'E & G : CASH IN BANK : xxx', '6': '208851.91', '7': '185275.84', '8': '93681.67', '9': '117257.74'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1100-03', '5': 'E & G : CASH IN BANK : xxx', '6': '44983.94', '7': '23138.30', '8': '9706.77', '9': '31552.41'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1100-04', '5': 'E & G : CASH IN BANK : xxx', '6': '100473.61', '7': '78896.16', '8': '37011.58', '9': '58589.03'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1106-01', '5': 'E & G : CASH IN BANK - FIRST STATE : xxx', '6': '30925181.58', '7': '12997835.43', '8': '12774926.52', '9': '30702272.67'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '00-0-00000-1113-01', '5': 'E & G : AP CLEARING : xxx', '6': '269722.29', '7': '287535.82', '8': '165586.31', '9': '147772.78'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '10-0-00000-1106-01', '5': 'I & S : CASH IN BANK - FIRST STATE : xxx', '6': '298453.11', '7': '10838.92', '8': '183.24', '9': '287797.43'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '20-0-00000-1106-01', '5': 'PLANT : CASH IN BANK - FIRST STATE : xxx', '6': '1123694.45', '7': '208568.57', '8': '294643.13', '9': '1209769.01'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '30-0-00000-1106-01', '5': 'AUX : CASH IN BANK - FIRST STATE : xxx', '6': '-64466.23', '7': '1601387.89', '8': '2177416.50', '9': '511562.38'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '30-0-00000-1111-01', '5': 'AUX : SUSPENSE : xxx', '6': '-0.01', '7': '36.53', '8': '11.90', '9': '-24.64'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '30-0-00000-1111-04', '5': 'AUX : SUSPENSE : xxx', '6': '0.00', '7': '0.09', '8': '0.00', '9': '-0.09'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '40-0-00000-1106-01', '5': 'AGENCY : CASH IN BANK - FIRST STATE : xxx', '6': '1312292.46', '7': '45065.76', '8': '132332.53', '9': '1399559.23'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '40-0-00000-1107-01', '5': 'AGENCY : CASH IN BANK-BANK ONE (OTHER) : xxx', '6': '8041.37', '7': '539.09', '8': '0.00', '9': '7502.28'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '50-0-00000-1106-01', '5': 'SCHOL : CASH IN BANK - FIRST STATE : xxx', '6': '1653258.79', '7': '810356.57', '8': '533130.20', '9': '1376032.42'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '60-0-00000-1112-01', '5': 'LOAN : DIRECT LOANS : xxx', '6': '3079590.41', '7': '3079241.37', '8': '1903928.46', '9': '1904277.50'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '70-0-00000-1106-01', '5': 'OPP GRANT : CASH IN BANK - FIRST STATE : xxx', '6': '5392619.21', '7': '5393119.21', '8': '740332.47', '9': '739832.47'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '70-0-00000-1109-01', '5': 'OPP GRANT : STALE DATED CHECKS : xxx', '6': '-840.76', '7': '0.00', '8': '0.00', '9': '-840.76'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '80-0-00000-1106-01', '5': 'FEDERAL WORKSTUDY : CASH IN BANK - FIRST STATE : xxx', '6': '0.00', '7': '4522.00', '8': '19895.00', '9': '15373.00'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11100', '3': 'Cash', '4': '90-0-00000-1106-01', '5': 'PAYROLL : CASH IN BANK - FIRST STATE : xxx', '6': '777.77', '7': '5068751.04', '8': '5068383.59', '9': '410.32'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '00-0-00000-1110-01', '5': 'E & G : PETTY CASH - CHANGE FUND : xxx', '6': '2800.00', '7': '0.00', '8': '0.00', '9': '2800.00'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '00-0-00000-1110-02', '5': 'E & G : PETTY CASH - CHANGE FUND : xxx', '6': '800.00', '7': '0.00', '8': '0.00', '9': '800.00'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '00-0-00000-1110-04', '5': 'E & G : PETTY CASH - CHANGE FUND : xxx', '6': '100.00', '7': '0.00', '8': '0.00', '9': '100.00'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-01', '5': 'AUX : PETTY CASH - CHANGE FUND : xxx', '6': '6290.00', '7': '0.00', '8': '0.00', '9': '6290.00'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-02', '5': 'AUX : PETTY CASH - CHANGE FUND : xxx', '6': '600.00', '7': '0.00', '8': '0.00', '9': '600.00'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-04', '5': 'AUX : PETTY CASH - CHANGE FUND : xxx', '6': '400.00', '7': '0.00', '8': '0.00', '9': '400.00'}"
arrFeed &= ", {'0': '11000', '1': 'Cash and Cash Equivalents', '2': '11300', '3': 'Petty Cash', '4': '30-0-00000-1110-05', '5': 'AUX : PETTY CASH - CHANGE FUND : DO NOT USE', '6': '800.00', '7': '0.00', '8': '0.00', '9': '800.00'}"
arrFeed &= "]"
DataGridView1.DataSource = JsonToDataTable(arrFeed)
End Sub
Public Function JsonToDataTable(ByVal strJson As String) As DataTable
Try
strJson = strJson.Substring(strJson.IndexOf("[") + 1)
strJson = strJson.Substring(0, strJson.IndexOf("]"))
Dim rg As Regex = New Regex("(?<={)[^}]+(?=})")
Dim mc As MatchCollection = rg.Matches(strJson)
Dim tb As DataTable = Nothing
For i As Integer = 0 To mc.Count - 1
Dim strRow As String = mc(i).Value
Dim strRows As String() = strRow.Split(","c)
If tb Is Nothing Then
tb = New DataTable With {.TableName = ""}
For Each str As String In strRows
Dim dc As DataColumn = New DataColumn()
Dim strCell As String() = str.Split(":"c)
dc.ColumnName = strCell(0).ToString().Replace("""", "").Replace("'", "").Trim()
tb.Columns.Add(dc)
Next
tb.AcceptChanges()
End If
Dim dr As DataRow = tb.NewRow()
For r As Integer = 0 To strRows.Length - 1
dr(r) = strRows(r).Split(":"c)(1).Trim().Replace(",", ",").Replace(":", ":").Replace("/", "").Replace("""", "").Replace("'", "").Trim()
Next
tb.Rows.Add(dr)
tb.AcceptChanges()
Next
Return tb
Catch ex As Exception
MsgBox(ex.Message & vbNewLine, 48, "JsonToDataTable")
Finally
End Try
End Function
End Class
but why do you have to use a Json when you extract the Data like you say...