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

[bilbon]

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

 

[Nine Zero]

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

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

 

[bilbon]

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

 

[Nine Zero]

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

 

[bilbon]

Hi


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

/Bilbon