Pick out some details in a JSON file and print these in a text file

bilbon

Board Regular
Joined
Dec 19, 2011
Messages
83
Hi
I have since 1999 downloaded statistics in XML format from a home page and now they have changed to JSON. I can't write code myself and wondering if anyone can help me with VBA code that can pick out details from this file and print the details in a text file.
I keep my fingers crossed that any helpful person can help me.


The JSON file looks like this.


{"error":null,"requestInfo":{"elapsedTime":3,"apiVersion":1},"requestId":"d3798818-2b94-4629-8897-efc9490449da","sessionId":null,"deviceId":"fe1c3020-ee04-4225-8a5a-f76c64a806a1","session":null,"sessionUser":null,"clientInfo":null,"result":{"cancelled":false,"events":[{"eventNumber":1,"description":"Nederländerna-Tyskland","cancelled":false,"outcome":"1","outcomeScore":"3-0"},{"eventNumber":2,"description":"Irland-Danmark","cancelled":false,"outcome":"X","outcomeScore":"0-0"},{"eventNumber":3,"description":"Norge-Slovenien","cancelled":false,"outcome":"1","outcomeScore":"1-0"},{"eventNumber":4,"description":"Bulgarien-Cypern","cancelled":false,"outcome":"1","outcomeScore":"2-1"},{"eventNumber":5,"description":"Georgien-Andorra","cancelled":false,"outcome":"1","outcomeScore":"3-0"},{"eventNumber":6,"description":"Lettland-Kazakstan","cancelled":false,"outcome":"X","outcomeScore":"1-1"},{"eventNumber":7,"description":"Armenien-Gibraltar","cancelled":false,"outcome":"2","outcomeScore":"0-1"},{"eventNumber":8,"description":"FYR Makedonien-Liechtenstein","cancelled":false,"outcome":"1","outcomeScore":"4-1"},{"eventNumber":9,"description":"Burton Albion-Bristol R","cancelled":false,"outcome":"1","outcomeScore":"1-0"},{"eventNumber":10,"description":"Coventry-Wycombe","cancelled":false,"outcome":"1","outcomeScore":"1-0"},{"eventNumber":11,"description":"Rochdale-Doncaster","cancelled":false,"outcome":"2","outcomeScore":"2-3"},{"eventNumber":12,"description":"S****horpe-Peterborough","cancelled":false,"outcome":"2","outcomeScore":"0-2"},{"eventNumber":13,"description":"Sunderland-Blackpool","cancelled":true,"outcome":"1","outcomeScore":"0-0"}],"distribution":[{"winners":42,"amount":"107698,00","name":"13 rätt"},{"winners":5112,"amount":"331,00","name":"12 rätt"},{"winners":71519,"amount":"18,00","name":"11 rätt"},{"winners":423006,"amount":"0,00","name":"10 rätt"}],"productName":"Stryktipset","productId":1,"drawNumber":4567,"openTime":"2018-10-07T07:00:00+02:00","closeTime":"2018-10-13T15:59:00+02:00","turnover":"17397460,00","checksum":"9beedfd1ec7e3910c1d852008f911fc75c45ce04"}}






The details I would like to have printed in the text file looks like this.


Stryktipset
1
4567
2018-10-13


1,Nederländerna,Tyskland,false,1,3-0
2,Irland,Danmark,false,X,0-0
3,Norge,Slovenien,false,1,1-0
4,Bulgarien,Cypern,false,1,2-1
5,Georgien,Andorra,false,1,3-0
6,Lettland,Kazakstan,false,X,1-1
7,Armenien,Gibraltar,false,2,0-1
8,FYR Makedonien,Liechtenstein,false,1,4-1
9,Burton Albion,Bristol R,false,1,1-0
10,Coventry,Wycombe,false,1,1-0
11,Rochdale,Doncaster,false,2,2-3
12,S****horpe,Peterborough,false,2,0-2
13,Sunderland,Blackpool,true,1,0-0


13 rätt,42,107698
12 rätt,5112,331
11 rätt,71519,18
10 rätt,423006,0

/Bilbon
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
hello.... ok try this out... this is a huge workaround but it might work for this file alone and maybe the future ones....

Notepad
1. Copy and paste the JSON data from above and paste into a notepad file and save it as demo.json
2. place the somewhere simple like on your desktop

Excel
1. Open a brand new workbook
2. Insert the code into a VBA module and CHANGE THE pathr VARIABLE TO WHEREVER THE PATH OF THAT DEMO.JSON FILE IS
3. run the code

Let me know if it works

Code:
Sub Macro1()
'
' Macro1 Macro
'


'
Dim pathr As String
'change this path to your path
pathr = "C:\Users\username\Desktop\demo.json"


    ActiveWorkbook.Queries.Add Name:="demo", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Json.Document(File.Contents(""" & pathr & """))," & Chr(13) & "" & Chr(10) & "    result = Source[result]," & Chr(13) & "" & Chr(10) & "    #""Converted to Table"" = Record.ToTable(result)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Converted to Table"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=demo;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [demo]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "demo"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Queries.Add Name:="demo2", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Json.Document(File.Contents(""" & pathr & """))," & Chr(13) & "" & Chr(10) & "    result = Source[result]," & Chr(13) & "" & Chr(10) & "    events = result[events]," & Chr(13) & "" & Chr(10) & "    #""Converted to Table"" = Table.FromList(events, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"" = Table.ExpandRecordColumn(#""Converted to Table"", ""Column1"", {""eventNum" & _
        "ber"", ""description"", ""cancelled"", ""outcome"", ""outcomeScore""}, {""Column1.eventNumber"", ""Column1.description"", ""Column1.cancelled"", ""Column1.outcome"", ""Column1.outcomeScore""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"""
    'ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=demo;Extended Properties=""""" _
        , Destination:=Range("$A$15")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [demo2]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "demo2"
        .Refresh BackgroundQuery:=False
    End With
        ActiveWorkbook.Queries.Add Name:="demo3", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Json.Document(File.Contents(""" & pathr & """))," & Chr(13) & "" & Chr(10) & "    result = Source[result]," & Chr(13) & "" & Chr(10) & "    distribution = result[distribution]," & Chr(13) & "" & Chr(10) & "    #""Converted to Table"" = Table.FromList(distribution, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"" = Table.ExpandRecordColumn(#""Converted to Table"", ""Colu" & _
        "mn1"", {""winners"", ""amount"", ""name""}, {""Column1.winners"", ""Column1.amount"", ""Column1.name""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Expanded Column1"""
    'ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""demo"";Extended Properties=""""" _
        , Destination:=Range("$A$32")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [demo3]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "demo3"
        .Refresh BackgroundQuery:=False
    End With
End Sub
 
Upvote 0
Hi Nine Zero


Thank you for your reply.
Get some weird letters because I have åäö in the language.
Otherwise, I get the information I need and can copy over it to a text file.


/Bilbon
 
Upvote 0
Yeah i noticed that too maybe if the language is supported, then it might work.... Keep playing around with it

it a good thing I came across this question as I have been thinking about creating an Excel Add-In that users can download and it will parse the JSON automatically and then you can possibly export as text.

Pretty much exactly what you need.

If I ever get anywhere with it, you will be the first to know about it lol

good luck
 
Upvote 0
Hi


It have been a very good Add In that I hope will become a reality.
A thousand thanks for your help

/Bilbon
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top