+ Reply to Thread
Results 1 to 2 of 2

Need to make a loop to create names from a JSON like text

  1. #1
    Registered User
    Join Date
    05-29-2024
    Location
    Amsterdam
    MS-Off Ver
    365 Apps for business
    Posts
    1

    Need to make a loop to create names from a JSON like text

    Hi,

    I am a VBA novice and I have a problem not really knowing how to solve the following problem. The VBA code needs to loop through the original text, see below, and create a list of names in the following format: e.g. Level 1 name, Level 1 name.Level 2 name, Level 1 name.Level 2 name.Level 3 name, etc. depending if there is a lower level name and separated by a dot, see the required end result below. The text is a JSON format without the values but only the object names. After each ":" a new level starts and after each "[" an array starts.

    This is the original text:
    [{"identificatie":{"lokaalID":"5423870000","namespace":"NL.IMKAD.KadastraalObject"},"type":"Perceel","kadastraleAanduiding":{"kadastraleGemeente":{"code":"96","waarde":"Breda"},"perceelnummer":9448,"sectie":"E"},"ontstaanUitOZ":[{"betreftOZ":{"lokaalID":"54230070000","namespace":"NL.IMKAD.KadastraalObject"}},{"betreftOZ":{"lokaalID":"54230270000","namespace":"NL.IMKAD.KadastraalObject"}}],"kadastraleGrootte":{"soortGrootte":{"code":"1","waarde":"Vastgesteld"},"waarde":150},"indicatieMeettariefVerschuldigd":false,"plaatscoordinaten":{"type":"Point","coordinates":[231313101.712,5133931.054]}},{"identificatie":{"lokaalID":"54230330000","namespace":"NL.IMKAD.KadastraalObject"},"type":"Perceel","kadastraleAanduiding":{"kadastraleGemeente":{"code":"96","waarde":"Breda"},"perceelnummer":131,"sectie":"E"}}]

    The end results should look like:
    • identificatie.lokaalID
    • identificatie.namespace
    • type
    • kadastraleAanduiding.kadastraleGemeente.code
    • kadastraleAanduiding.kadastraleGemeente.waarde
    • kadastraleAanduiding.perceelnummer
    • kadastraleAanduiding.sectie
    • ontstaanUitOZ.betreftOZ.lokaalID
    • ontstaanUitOZ.betreftOZ.namespace
    • ontstaanUitOZ.betreftOZ.lokaalID
    • ontstaanUitOZ.betreftOZ.namespace
    • kadastraleGrootte.soortGrootte.code
    • kadastraleGrootte.soortGrootte.waarde
    • kadastraleGrootte.waarde
    • indicatieMeettariefVerschuldigd
    • plaatscoordinaten.type
    • plaatscoordinaten.coordinates.1
    • plaatscoordinaten.coordinates.2
    • identificatie.lokaalID
    • identificatie.namespace
    • type
    • kadastraleAanduiding.kadastraleGemeente.code
    • kadastraleAanduiding.kadastraleGemeente.waarde
    • kadastraleAanduiding.perceelnummer
    • kadastraleAanduiding.sectie

    Note some names don't have any lower level names and the names in mentioned in the original text between the [ ] are mentioned 2 times.

    Any thoughts how to solve this with a VBA code?
    Last edited by adrian1000; 05-29-2024 at 07:53 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    29,778

    Re: Need to make a loop to create names from a JSON like text

    We need some "rules" as it certain texts appear to be "ignored" e.g "NL.IMKAD.KadastraalObject", "E", "Vastgesteld", "Breda" plus all numeric values

    Or is your result list incorrect (as I believe you changed the input data)?
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Edit JSON parser code to add function to write JSON file
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-16-2020, 09:28 PM
  2. Nested Json loop
    By xccel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-11-2020, 09:22 PM
  3. [SOLVED] for each row, create a text ID to identify names of cols to right which contain numbers.
    By jrtaylor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-21-2019, 08:50 AM
  4. [SOLVED] How to create a loop of TextBox Names
    By j_Southern in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-27-2018, 11:17 AM
  5. Create a Loop That Will Make A String Containing Both Quotes and Commas
    By kstoneman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2017, 02:55 AM
  6. [SOLVED] Loop Through Folder, Create Emails with Sub Folder Names in Subject, Attach files in sub
    By Rschwar23 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2015, 10:06 AM
  7. Loop Through Column A On Sheet 1 To Create Tabs With Each Workers' Names
    By Richie77777 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-29-2013, 06:56 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1