Results 1 to 5 of 5

Thread: Pick out some details in a JSON file and print these in a text file
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2011
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    622
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    Board Regular
    Join Date
    Mar 2016
    Posts
    622
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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

  5. #5
    Board Regular
    Join Date
    Dec 2011
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi


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

    /Bilbon

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •