Help with Importing XML or JSON online
Help with Importing XML or JSON online
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Help with Importing XML or JSON online

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with Importing XML or JSON online

     
    It seems I can't find a straight answer anywhere, so I hope someone here is able to answer my question, or at least guide me in the right direction. I'd like to know if there's a way to import specific data from an online source, such as a json or xml file, have it automatically populate the excel worksheet with that specific data and ignore anything else not requested.

    So for example, we use a ticket system that is 3rd party but does not have a way of exporting data to an excel worksheet. I discovered however that they do have an xml/json exterior that looks something like this.

    JSON: https://imgur.com/6mJPEnv (removed sensitive data)

    I was able to import the data and it imports everything as a connection. If I add it as a table, it only imports the top tree, which is a lot more work than just manual copy/paste. I would like to know if we can grab this data as a connection using the Get Data from Web function and then automatically populate cells in a work sheet that looks like this. Only adding the text data into the worksheet below. Mind you, the data vary from ticket to ticket but the functions seen on the left column of the JSON/Excel does not change.

    Worksheet: https://imgur.com/oZ7F0zj

    Thanks in advance.
    I don't think I have a great understanding of VBA in Excel but I have messed with scripting before so if I need to get dirty with code, I can.

  2. #2
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with Importing XML or JSON online

    If you can give the URL of the XML file, and tell us which values to be extracted, we can find a solution.

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Importing XML or JSON online

    I can't provide the URL, it requires an internal account but I can provide a test xml/json file that is saved from the source.

    Dropbox download: https://www.dropbox.com/sh/d3hxi506t...-Xc_dg5Sa?dl=0

  4. #4
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with Importing XML or JSON online

    So, which datas do you need to be extracted?
    Last edited by Haluk; Dec 8th, 2017 at 10:54 AM.

  5. #5
    New Member
    Join Date
    Dec 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Importing XML or JSON online




    Under










    Basically, the info should go in here like this: https://imgur.com/ZlOl4fl Don't mind the two inputs in the merged cells for System Type, I can fix that later.

  6. #6
    New Member
    Join Date
    Dec 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Importing XML or JSON online

    Oh oops, I forgot this uses BBCode.

    requester-name
    responder-name

    Under Custom Field

    customer_id_number_336006
    sales_order_number_336006
    invoice_number_336006
    model_336006
    version_336006
    depot_confirmed_shipping_address_336006
    warranty_336006
    billabletest_336006
    depot_shipping_type_336006

  7. #7
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with Importing XML or JSON online

    Try the code below code and if its OK for you, then change the Ranges mentioned in the code to suit your needs.

    Code:
    Sub Get_XML_Data()
        'Haluk
        '08/12/2017
        '
        Dim xDoc As Object
        Set xDoc = CreateObject("MSXML2.DOMDocument")
        xDoc.async = False
        xDoc.validateOnParse = False
        
        MyFile = Application.GetOpenFilename
        If MyFile = "False" Then Exit Sub
        
        xDoc.Load MyFile
        
        Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/requester-name")
        Key1 = MyKey.Text
        
        Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/responder-name")
        Key2 = MyKey.Text
        
        Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/customer_id_number_336006")
        Key3 = MyKey.Text
        
        Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/invoice_number_336006")
        Key4 = MyKey.Text
        
        Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/model_336006")
        Key5 = MyKey.Text
        
        Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/depot_confirmed_shipping_address_336006")
        Key6 = MyKey.Text
        
        Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/warranty_336006")
        Key7 = MyKey.Text
        
        Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/billabletest_336006")
        Key8 = MyKey.Text
        
        Set MyKey = xDoc.SelectSingleNode("//helpdesk-ticket/custom_field/depot_shipping_type_336006")
        Key9 = MyKey.Text
        
        Range("A1") = "Requester Name:"
        Range("B1") = Key1
        Range("A2") = "Responder Name:"
        Range("B2") = Key2
        Range("A3") = "customer_id_number_336006:"
        Range("B3") = Key3
        Range("A4") = "invoice_number_336006:"
        Range("B4") = Key4
        Range("A5") = "model_336006:"
        Range("B5") = Key5
        Range("A6") = "depot_confirmed_shipping_address_336006:"
        Range("B6") = Key6
        Range("A7") = "warranty_336006:"
        Range("B7") = Key7
        Range("A8") = "billabletest_336006:"
        Range("B8") = Key8
        Range("A9") = "depot_shipping_type_336006:"
        Range("B9") = Key9
        Range("A:B").Columns.AutoFit
        
        Set MyKey = Nothing
        Set xDoc = Nothing
    End Sub

  8. #8
    New Member
    Join Date
    Dec 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Importing XML or JSON online

    Thank you very much! This did it! It's loading info into their respective cells now.

    Now the only question I have left is: How do I get it use the data in connection/query loaded from the Get Data from Web option?

    https://imgur.com/Lg2KkF6

  9. #9
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Help with Importing XML or JSON online



    A different technique is used in my post and it has nothing to do with the method you are referring to.

    So, if the related XML table is somewhere on the net, change the below lines;

    Code:
        MyFile = Application.GetOpenFilename
        If MyFile = "False" Then Exit Sub
    with, something like this;

    Code:
    MyFile = "http://www.MySite.com/MyXMLfile.xml"
    Last edited by Haluk; Dec 8th, 2017 at 01:18 PM.

  10. #10
    New Member
    Join Date
    Dec 2017
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with Importing XML or JSON online

      
    Unfortunately, that did not work.

    https://imgur.com/UggdF1M

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
  •  

 

 
DMCA.com