-
Dec 2nd, 2019, 07:05 PM
#1
Thread Starter
Addicted Member
VB.NET 2019 JsonToDataTable
WindowsApp1.zip
Hello everyone, I have encountered difficulties in converting JSON to a table. Can anyone help me?
The reason for the error is that JSON contains a ampersand character, because JSON is separated by ampersands, it cannot be replaced directly, and the position and content are not fixed.
thank you all!
edit:The image is so big, why can't I see it clearly?
The JSON at the error is: "yy1,6yy"
Code:
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("""", "").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("""", "").Trim()
Next
tb.Rows.Add(dr)
tb.AcceptChanges()
Next
Return tb
Catch ex As Exception
MsgBox(ex.Message & vbNewLine & sInfo, 48, "JsonToDataTable")
Finally
End Try
End Function
JSON:
{"result": {"record": 0,"pcount": 0,"rows": [{"purchOrderId": "78eb","middlePackage": 10,
"batchNum": "110006","batchGroup": 0,"prodDate": "2019-11","prodDate2": "2019-11-30",
"dueDate": "2020-06","dueDate2": "2020-06-30","inStoreDate": null,"storeNum": 2000.00,
"hightestPrice": 36.000,"price": 50.000,"tradePrice": null,"unit": "he","cfy": 0},
{"purchOrderId": "1a3","middlePackage": 1,"batchNum": "16","batchGroup": 1,"prodDate": "2015-07",
"prodDate2": "2015-07-31","dueDate": "2020-06","dueDate2": "2020-06-30","inStoreDate": null,
"storeNum": 0.000,"hightestPrice": 368.00,"price": 35.98,"tradePrice": null,"unit": "pp","cfy": 0}]},"statusCode": 200,"message": null}
Last edited by ChenLin; Dec 2nd, 2019 at 07:38 PM.
QQ: 289778005
-
Dec 2nd, 2019, 07:22 PM
#2
Re: VB.NET 2019 JsonToDataTable
Don't post pictures and links to entire projects. Post the relevant code directly, formatted appropriate. Post error messages directly. Post JSON content directly, or as a link to a file if it's very large. Of course, even if your JSON is very large, we don;t need all your JSON. We just need an example that demonstrates the issue.
-
Dec 2nd, 2019, 07:37 PM
#3
Thread Starter
Addicted Member
Re: VB.NET 2019 JsonToDataTable
Originally Posted by jmcilhinney
Don't post pictures and links to entire projects. Post the relevant code directly, formatted appropriate. Post error messages directly. Post JSON content directly, or as a link to a file if it's very large. Of course, even if your JSON is very large, we don;t need all your JSON. We just need an example that demonstrates the issue.
Thank you jmcilhinney, the parsing code and JSON are posted.
-
Dec 2nd, 2019, 07:45 PM
#4
Re: VB.NET 2019 JsonToDataTable
You shouldn't really be parsing JSON manually like that. There are libraries that are tried and tested that will do the parsing for you, the most well-known being Json.NET.
-
Dec 3rd, 2019, 08:18 AM
#5
Re: VB.NET 2019 JsonToDataTable
...because JSON is separated by ampersands, it cannot be replaced directly...
Now that simply is not true. Even in the JSON snip that you posted, there isn't a single ampersand that separates anything. JSON stands for JavaScript Object Notation and in its basic format it takes on the form of "key":"value" pairs. In some cases that value is a complex object, so it gets wrapped in {} to show it it's a larger object....if it's part of an array, then it uses [] ... bu there isn't anything that uses an ampersand for a separation of anything in JSON. At best an & has been expanded out to & for encoding purposes, but that's it.
However, if one were to use a proper JSON parser, this wouldn't be an issue since it would be able to take care of this for you and you wouldn't need to worry about it in the firs place.
-tg
-
Dec 5th, 2019, 09:08 PM
#6
Thread Starter
Addicted Member
Re: VB.NET 2019 JsonToDataTable
I try to use newtonsoft. JSON to solve this problem, but there are still some mistakes: because my JSON often has some special characters.
Finally, my solution is to transfer these JSON characters in the form of parameters to SQL server, and use the database to solve it. So far, there are no more errors.
Thank you!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|