Function ConvertToJson work not correctly when the string contain continuous zero
badoyau opened this issue · 5 comments
Hi,
Thank for your library. it is a useful library for my project.
i have one item as string in excel cells as 0012020000000000
but when i use dictionary call final and export as json by json1 = JsonConverter.ConvertToJson(Final, Whitespace:=2)
it will return in differenct types of results. SO i try many cases such as following
0012020000000000 , then it return 0012020000000000 as integer, instead "0012020000000000"
001202000000000 , then it work perfectly as "001202000000000"
001202000000000A , then it work perfectly as "001202000000000A"
please kindly check this bug for many thanks!
VBA-JSON depends on how you format your Excel cells to determine the datatype to assign in JSON. If you don't specify a format for a cell, then it is General which essentially will be format Number when entering 0012020000000000
. Here is an example. I opened a new blank workbook. On Sheet1
I formatted cell A1
to General, cell B1
to Number with zero decimal places and cell C1
to Text. Then added a new module with the VBA-JSON code and another module with the following code:
Public Sub test()
Dim Worksheet As Excel.Worksheet
Dim jsonArray As New VBA.Collection
Dim json As String
Set Worksheet = ActiveSheet
jsonArray.Add Worksheet.Range("A1").Value ' Cell formated as General.
jsonArray.Add Worksheet.Range("B1").Value ' Cell formatted as Number, zero decimal places.
jsonArray.Add Worksheet.Range("C1").Value ' Cell formatted as Text.
json = ConvertToJson(jsonArray, Whitespace:=2)
Debug.Print json
Exit Sub
End Sub
This code will print the following the Developer Immediate window in the VBA project environment:
[
12020000000000,
12020000000000,
"0012020000000000"
]
So what I suspect your issue is, is that you have not appropriately set the format for cell in question and this is not a bug.
Hope that helps.
I noticed that your GitHub account had a repository with a workbook in it that contains some example data. I downloaded that and did a little digging into the VBA-JSON code which indicates in ConvertToJson
:
Case VBA.vbString
' String (or large number encoded as string)
If Not JsonOptions.UseDoubleForLargeNumbers And json_StringIsLargeNumber(JsonValue) Then
ConvertToJson = JsonValue
Else
ConvertToJson = """" & json_Encode(JsonValue) & """"
End If
Your cells in column C have 0012020000000000
which VBA-JSON considers a large number. The default for JsonOptions.UseDoubleForLargeNumbers
is FALSE
thus the condition in the code makes it TRUE
and the code will return it as a JSON number and not a string. I"m not sure why it didn't do this in the example I presented above, but it didn't... The solution is to change the VBA-JSON option JsonOptions.UseDoubleForLargeNumbers
to TRUE
in your code. Here is an updated example using the data from your workbook:
Public Sub test()
Dim Worksheet As Excel.Worksheet
Dim jsonArray As New VBA.Collection
Dim json As String
Set Worksheet = ActiveSheet
jsonArray.Add Worksheet.Range("C2").Value
jsonArray.Add Worksheet.Range("C3").Value
jsonArray.Add Worksheet.Range("C4").Value
JsonOptions.UseDoubleForLargeNumbers = True
json = ConvertToJson(jsonArray, Whitespace:=2)
Debug.Print json
Exit Sub
End Sub
which produces the following the Developer Immediate window in the VBA project environment:
[
"0012020000000000",
"0012020000000000",
"0012020000000000"
]
Your issue doesn't appear to be a bug, but you stumbled upon a feature!!
Hi houghtonap,
Many thanks for your kindly help!
After revise the LargeNumbers setting to True
JsonOptions.UseDoubleForLargeNumbers = True
Issue is fixed perfectly!
Yes, as you said this is not a bug. i also found the detail decription from the code remark
VBA only stores 15 significant digits, so any numbers larger than that are truncated
' This can lead to issues when BIGINT's are used (e.g. for Ids or Credit Cards), as they will be invalid above 15 digits
' See: http://support.microsoft.com/kb/269370
'
' By default, VBA-JSON will use String for numbers longer than 15 characters that contain only digits
' to override set JsonConverter.JsonOptions.UseDoubleForLargeNumbers = True
UseDoubleForLargeNumbers As Boolean
Really thanks for your kindly reply~!
There are a number of open issues aboutUseDoubleForLargeNumbers
andjson_StringIsLargeNumber
(including #66 by @timhall ) and reading/writing ECMA-404 numbers.
The string 0012020000000000 is not valid as an ECMA number (has leading zeros) and should always be written as ECMA string. But that should be the default in any case.
I agree that settingUseDoubleForLargeNumbers=True
will solve the immediate issue, but I want to get my head round the justification for this option 'fix' and maybe make some better code.
BTW note that there is a small performance issue with thatif ... and ...
condition. VBA does not do logical shortcuts. Sojson_StringIsLargeNumber
will always be called for every string converted.
I agree with @Nick-vanGemeren about UseDoubleForLargeNumbers
and it is unclear from the comments why this option was created and why it is not the default. My only guess is that the VBA runtime might take very large integer constants and convert them to variant strings, hence the reason behind the option, but I would need to investigate that and my focus was helping @badoyau move forward.
However, the bigger underlying question for me is why my test code was successful using the same string as @badoyau, but it failed in the workbook @badoyau was using. It wasn't until I downloaded @badoyau workbook that I could see it failing. My environment is Windows 10, Excel 2016 and I was using VBA-JSON v2.3.0. However, the code, in this area, looks the same between v2.3.0 and the current version.