View Full Version : VBA error with parsing JSON
Pasi12
02-06-2017, 03:36 PM
Hi,
I am a new bee using below code to parse Json txt data into xls but I get error below, can any one help? I also like to be able to read a json .txt file and parse that info into xls sheet? how do I get below code working?Thanks
Thanks
Pasi
Sub Test()
Dim jsonText As String
Dim jsonObj As Dictionary
Dim jsonRows As Collection
Dim jsonRow As Collection
Dim ws As Worksheet
Dim currentRow As Long
Dim startColumn As Long
Dim i As Long
' Load JSON string from text file
With CreateObject("Scripting.FileSystemObject")
JSONstring = .OpenTextFile("C:\Users\Pasi\Documents\json.txt", 1).ReadAll
End With
Set ws = Worksheets("Sheet1")
'Create a real JSON object
jsonText = "{" & ws.Range("A1").Value & "}"
'Parse it
Set jsonObj = JSON.parse(jsonText)
'Get the rows collectionc
Set jsonRows = jsonObj("rows") 'geeting error here saying "Object required"??
'Set the starting row where to put the values
currentRow = 1
'First column where to put the values
startColumn = 2 'B
'Loop through all the values received
For Each jsonRow In jsonRows
'Now loop through all the items in this row
For i = 1 To jsonRow.Count
ws.Cells(currentRow, startColumn + i - 1).Value = jsonRow(i)
Next i
'Increment the row to the next one
currentRow = currentRow + 1
Next jsonRow
End Sub
Refers To (https://en.wikipedia.org/wiki/JSON#JSON_Schema)
Does your JSON File resemble this
{
--"id": 1,
--"name": "Foo",
--"price": 123,
--"tags": [
----"Bar",
----"Eek"
--],
--"stock": {
----"warehouse": 300,
----"retail": 20
--}
}
We need to see one of your JSON texts, at least a couple of dozen lines.
Pasi12
02-07-2017, 03:50 PM
almost some thing like:
{ "orders": [
{
"created_on": "2017-01-10 10:32:41",
"department": "rcigm",
"name": "sergey",
"physician_first_name": "Dav",
"physician_last_name": "Didi"
}
],
"rcigm_id": "RCIDM_17E55D",
"sample_name": "sample_test_name",
"sequence": [
{
"pdf_created_on": "2017-01-10 10:32:40",
"pdf_path": "/Users/genetics/PycharmProjects/sample_accessioning/pdf_path/CASE_17D59E/RCIGM_17E56C/WGS",
"rcigm_id": "RCIDM_17E55D",
"sequence_type": "WGS"
}
],
"users": {
"email": "admin",
"first_name": "admin",
"id": 1,
"institute": "rciDM",
"last_name": "oh"
}
}
]
}
This represents the start of an attempt to parse that from a VBA viewpoint
{ "orders": [
{
"created_on": "2017-01-10 10:32:41",
"department": "rcigm",
"name": "sergey",
"physician_first_name": "Dav",
"physician_last_name": "Didi"
}
],
"rcigm_id": "RCIDM_17E55D",
"sample_name": "sample_test_name",
"sequence": [
{
"pdf_created_on": "2017-01-10 10:32:40",
"pdf_path": "/Users/genetics/PycharmProjects/sample_accessioning/pdf_path/CASE_17D59E/RCIGM_17E56C/WGS",
"rcigm_id": "RCIDM_17E55D",
"sequence_type": "WGS"
}
],
"users": {
"email": "admin",
"first_name": "admin",
"id": 1,
"institute": "rciDM",
"last_name": "oh"
}
}
]
}
Key:
Data Names = Double quoted and followed by a colon
Red = Names of Data Blocks
Purple = Data Blocks
Teal = Data that is not blocked
Note that two of the above data blocks, Orders and Sequence, are double bracketed {[...]}.
The Users data block is only single bracketed {...}
It is this kind of mixed patterns that make it so hard to code for. I think we need to see the Schema for these reports/files. We also will need you to provide a sample "Parsed" excel file using the actual Schema as the source for parsing.
The Schema file will tell us how to parse and the Parsed file will tell us how to put the result into Excel.
Pasi12
02-07-2017, 06:03 PM
Thanks SamT.. I do not have any other files to show you this was part of the file I pasted here just to show the JSON file. There is no Schema, this is outputted from a python program.
Pasi
Can you at least show us an actual JSON file and it's parsed Excel file?
Pasi12
02-08-2017, 09:28 AM
HI Sam, there is no excel file, just below Json txt file, here is the file: This is what I am trying to Parse to excel using vb:
Thanks!
{
"family": [
{
"case_id": "CASE_17BC95",
"individual": [
{
"date_of_birth": "2017-01-10 00:00:00",
"family_relationship": "Pro",
"gender": "F",
"individual_first_name": "Da",
"individual_last_name": "Oh",
"samples": [
{
"orders": [
{
"created_on": "2017-01-10 10:21:07",
"department": "RCI",
"name": "sergio",
"physician_first_name": "Test",
"physician_last_name": "DANNY"
}
],
"RCI_id": "RCI_170245",
"sample_name": "sample_test_name",
"sequence": [
{
"pdf_created_on": "2017-01-10 10:21:06",
"pdf_path": "/Users/gen/PycharmProjects/sample_accessioning/pdf_path/CASE_17BC95/RCI_170245/WGS",
"RCI_id": "RCI_170245",
"sequence_type": "WGS"
}
],
"users": {
"email": "admin",
"first_name": "admin",
"id": 1,
"institute": "rci",
"last_name": "oh"
}
},
{
"orders": [
{
"created_on": "2017-01-10 10:32:41",
"department": "rci",
"name": "sergio",
"physician_first_name": "Test",
"physician_last_name": "Danny"
}
],
"RCI_id": "RCI_17E56C",
"sample_name": "sample_test_name",
"sequence": [
{
"pdf_created_on": "2017-01-10 10:32:40",
"pdf_path": "/Users/gen/PycharmProjects/sample_accessioning/pdf_path/CASE_17D59E/RCI_17E56C/WGS",
"RCI_id": "RCI_17E56C",
"sequence_type": "WGS"
}
],
"users": {
"email": "admin",
"first_name": "admin",
"id": 1,
"institute": "RCI",
"last_name": "oh"
}
}
]
},
{
"date_of_birth": "2017-01-10 00:00:00",
"family_relationship": "Pro",
"gender": "F",
"individual_first_name": "Youn",
"individual_last_name": "Shu",
"samples": [
{
"orders": [
{
"created_on": "2017-01-10 10:32:01",
"department": "rci",
"name": "sergio",
"physician_first_name": "Test",
"physician_last_name": "Danny"
}
],
"RCI_id": "RCI_17404C",
"sample_name": "sample_test_name",
"sequence": [
{
"pdf_created_on": "2017-01-10 10:32:00",
"pdf_path": "/Users/gen/PycharmProjects/sample_accessioning/pdf_path/CASE_17BC95/RCI_17404C/WES",
"RCI_id": "RCI_17404C",
"sequence_type": "WES"
}
],
"users": {
"email": "admin",
"first_name": "admin",
"id": 1,
"institute": "RCI",
"last_name": "oh"
}
}
]
}
]
},
{
"case_id": "CASE_17D59E",
"individual": [
{
"date_of_birth": "2017-01-10 00:00:00",
"family_relationship": "Pro",
"gender": "F",
"individual_first_name": "Da",
"individual_last_name": "Oh",
"samples": [
{
"orders": [
{
"created_on": "2017-01-10 10:21:07",
"department": "RCI",
"name": "sergio",
"physician_first_name": "Test",
"physician_last_name": "Danny"
}
],
"RCI_id": "RCI_170245",
"sample_name": "sample_test_name",
"sequence": [
{
"pdf_created_on": "2017-01-10 10:21:06",
"pdf_path": "/Users/gen/PycharmProjects/sample_accessioning/pdf_path/CASE_17BC95/RCI_170245/WGS",
"RCI_id": "RCI_170245",
"sequence_type": "WGS"
}
],
"users": {
"email": "admin",
"first_name": "admin",
"id": 1,
"institute": "RCI",
"last_name": "oh"
}
},
{
"orders": [
{
"created_on": "2017-01-10 10:32:41",
"department": "RCI",
"name": "sergio",
"physician_first_name": "Test",
"physician_last_name": "Danny"
}
],
"RCI_id": "RCI_17E56C",
"sample_name": "sample_test_name",
"sequence": [
{
"pdf_created_on": "2017-01-10 10:32:40",
"pdf_path": "/Users/gen/PycharmProjects/sample_accessioning/pdf_path/CASE_17D59E/RCI_17E56C/WGS",
"RCI_id": "RCI_17E56C",
"sequence_type": "WGS"
}
],
"users": {
"email": "admin",
"first_name": "admin",
"id": 1,
"institute": "RCI",
"last_name": "oh"
}
}
]
}
]
}
]
}
Paul_Hossler
02-08-2017, 09:58 AM
It'd be better and more accurate if you could attach the actual JSON file by clicking [Go Advanced] at lower right and then using the paperclip icon to attach the file.
You'll probably need to rename it to .txt to be able to upload it
It's also be helpful if you included a small xlsx with the expected/desired results
Pasi12
02-08-2017, 10:23 AM
Hi, it wont let me, I tried uploading a .txt file but says invalid file? You can copy paste this file I have pasted above into a note pad.
Pasi.
Paul_Hossler
02-08-2017, 10:37 AM
I have JSON files, but they only have a line feed, not a LF+CR so the format is 'touchy'
Pasting the text into Notepad might give unrealistic results
Maybe if you zip it the you can upload it
https://github.com/ronle/Json-To-Excel
JsonToExcel
This Excel powered by VBA is capable of taking Json file, present the user with a treeview structure of the file and allow user selection of the key elements upon which a new line would be created.
In the repository is a simplified Json file to allow for simple conversion and representation in tabular view.
To operate:
1. Open the table and click on "Button 1"
2. Acknowledge the welcome message
3. Click on "Build TreeView"
4. The script will create a treeview representing the Json file
5. Expand the tree and select "colorName" as the key value (place checkmark next to it)
6. Select the output tab & click the "Parse To Excel" button
Blue values represents end KeyNames that are valid entries for a new line break.
None blue lines will are master keys that a new line cannot be broken by
Note: Not all files are suitable for representation as tabular view.
As some Objects might include different items, their representation in tabular view might not be consistent which makes it
difficult to spot patterns.
Json file into excel.xlsm (https://github.com/ronle/Json-To-Excel/blob/master/Json%20file%20into%20excel.xlsm)
After spending time researching JSON and the lack of much recent development, I would avoid it unless absolutely required.
Pasi12
02-08-2017, 12:02 PM
I have zipped the file let me know if it works?
Thanks.
Pasi
Pasi12
02-08-2017, 12:03 PM
File is attached.
Pasi
Paul_Hossler
02-08-2017, 12:30 PM
Not sure what kind of output you want, but here's a POSSIBLE start
Option Explicit
Sub Test()
Dim iFile As Long
Dim sJSONpath As String, sLine As String, sTrim As String
Dim iOut As Long, iIndent As Long
sJSONpath = Environ("USERPROFILE") & "\Desktop\json2.txt"
iFile = FreeFile
iOut = 1
Open sJSONpath For Input As #iFile
Do While Not EOF(iFile)
Line Input #iFile, sLine
sTrim = LTrim(sLine)
iIndent = (Len(sLine) - Len(sTrim)) / 2
Worksheets("Sheet1").Cells(iOut, iIndent + 1).Value = sTrim
iOut = iOut + 1
Loop
Close #iFile
End Sub
Pasi12
02-08-2017, 01:08 PM
Thank you Paul, I am looking for the out put like this: I don't want the brackets and all headers that starts/ends with " " should be a header
Family Case id Date of birth Gender Oders RCI_Id Users Sample Name and so on... Case_17BC95 2017-01-10 F RCI_170245 Sample_test_name
This did not come through correctly. should have headers and under headers the data.
Pasi12
02-08-2017, 02:12 PM
Paul,
Attached is the sample sheet. the red headers I want the Json file to populate.
Thanks,
Pasi
I have been concentrating on the Metadata to discern the Schema. Here is a quasi VBA representation of it. Note that the Collections keyword is used to indicate that more than one instance may occur in a section/block.
Public Type JSON
Family As String
Cases As Collection
End Type
Public Type Case
Case_Id As String
Individuals As Collection
End Type
Public Type Individual
Date_Of_Birth As String
Family_Relationship As String
Gender As String
Individual_First_Name As String
Individual_Last_Name As String
Samples As Collection
End Type
Public Type Samples
Orders As Collection
End Type
Public Type Order
Orderer As UDT
RCI_id As String
Sample_Name As String
Sequence As UDT
User As UDT
End Type
Public Type Orderer ''Orders" in JSON File
created_on As String
department As String
name As String
physician_first_name As String
physician_last_name As String
End Type
Public Type Sequence
pdf_created_on As String
pdf_path As String
RCI_id As String
sequence_type As String
End Type
Public Type User
email As String
first_name As String
id As Integer
institute As String
last_name As String
End Type
the red headers I want the Json file to populate.
That is simple. If you had provided that example when I asked for it in my post #4, you would have saved us hours and hours of work. I spent 5 hours on the problem just today, because I didn't know how very little you needed. :banghead:
It took me less than 30 seconds to figure out how to that little bit.
I am outta here.
Paul_Hossler
02-08-2017, 04:56 PM
Paul,
Attached is the sample sheet. the red headers I want the Json file to populate.
Thanks,
Pasi
Easy enough to do
Q1 - There is no 'family' value that I can see
Q2 - There is no 'User' but the is a 'Users' which has multiple entries. Is that what you want and all entries?
Q3 - In your format, just start in H2 and fill in data across and then go down?
It'd be helpful if you took the data in v2 and copy/pasted it onto your format sheet the way you want it
18295
Pasi12
02-08-2017, 05:54 PM
Easy enough to do
Q1 - There is no 'family' value that I can see-- yes but still need to have the header I think.
Q2 - There is no 'User' but the is a 'Users' which has multiple entries. Is that what you want and all entries? -----yes Pls
Q3 - In your format, just start in H2 and fill in data across and then go down? Yes pls
It'd be helpful if you took the data in v2 and copy/pasted it onto your format sheet the way you want it ( Sorry not sure what you mean by v2?)
18295
Pasi12
02-08-2017, 05:55 PM
Thanks Sam, I ma not sure how am I going to sue these blocks in the code?
Pasi
Paul_Hossler
02-08-2017, 08:45 PM
v2 = version 2 -- it was the attachment in post 19
Pasi12
02-09-2017, 09:21 AM
Sam, It was not my intent to have you guys spend hours , I did not have the file and only got it yesterday, the sample I showed you was typed in just to show what the data looks like. Thanks for your effort!
Pasi
Open the file
Remove all {. [, }, and ]
Remove commas
Replace ": " with ":" (Colon+space with Colon)
Trim the file
The result should look like
"case_id":"CASE_17BC95"
"individual":
"date_of_birth":"2017-01-10 00:00:00"
"family_relationship":"Pro"
"gender":"F"
"individual_first_name":"Da"
"individual_last_name":"Oh"
"samples":
"orders":
"created_on":"2017-01-10 10:21:07"
"department":"RCI"
"name":"sergio"
"physician_first_name":"Test"
"physician_last_name":"DANNY"
6."Split" the file into a 2D inputarray around the Colons
This will read the appropriate parts of the Cleaned up JSON inputarray into the output sheet. It needs some editing to be perfected.
Dim SampleName As String
Dim Output(1, 6)
For i = 1 to UBound(inputarray)
Select Case LCase(inputarray(i,1))
Case "family": Output(1, 1) = inputarray(i,2)
Case "case_id": Output(1, 2) = inputarray(i,2)
Case "date_of_birth": Output(1, 3) = inputarray(i,2)
Case "gender": Output(1, 4) = inputarray(i,2)
Case "rci_id": Output(1, 5) = inputarray(i,2)
Case "sample_name": SampleName = inputarray(i,2)
'"first_name" is actual name of User
Case "first_name": Output(1, 6) = inputarray(i,2)
End Select
If LCase(inputarray(i,1)) = "first_name" Then
OutputSheet.NextRow.Cells("A") = SampleName 'Not code
'If the sample name is already in the sheet, you will have to Find it
'and use that Row for the next step
OutputSheet.NextRow.Cells("H").resize(1, 6) = OutPut 'Not code
End If
Next i
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.