PDA

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

SamT
02-07-2017, 01:42 PM
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"
}
}
]
}

SamT
02-07-2017, 04:24 PM
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

SamT
02-07-2017, 06:22 PM
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

SamT
02-08-2017, 11:07 AM
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

SamT
02-08-2017, 04:35 PM
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

SamT
02-08-2017, 04:55 PM
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

SamT
02-09-2017, 12:35 PM
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