Results 1 to 5 of 5
  1. #1
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62

    MS Access to Nested XML

    Hi all,



    I have been attempting to convert a csv to nested json without luck, so my backup plan is to created a nested XML file from a MS Access table (containing the csv file).

    Please see the attached json file as a reference to what I want the XML to look like: test_json.txt

    Also, attached is the database table: test.zip

    Any help would be very much appreciated. I have tried stackoverflow and several other forums and have not had much success.

    Thanks in advance.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If Access export to XML won't produce desired structure, use code that writes to a text file.

    Also, review https://social.msdn.microsoft.com/Fo...ned-xml-schema
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Why is it so important to export nested data (XML or JSON)?

    The table structure would easily export to Excel or a CSV file.
    Text file output hits a limit causing an error - see screenshot

    Click image for larger version. 

Name:	Capture.PNG 
Views:	15 
Size:	16.3 KB 
ID:	39082

    My suggestion is (as before) to export to CSV then use a free online converter
    http://www.convertcsv.com/csv-to-json.htm or http://www.convertcsv.com/csv-to-xml.htm

    Attached are a JSON & XML file(zipped) created in a few seconds using the above.
    The output can be tweaked further so you should be able to obtain what you want

    Hope that helps
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    isladogs & Ajax, thanks for the replies.

    The file arrives to me in a csv text format (15-20 MB), without headers. My attempt to use Access is because the data has to enter into the db for other processing and I wanted to make it part of the workflow, along with the fact that it has headers at that point.

    The client has requested that the file replicate the json example I posted and I think the reason it is nested is because it isn't a simple flat transform from csv -> json; there are some summations that happen, along with groupings.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As I said before, the online converters should be able to created 'nested output' whether XML or JSON.
    However, you haven't explained why they need the exported file, whether XML or JSON.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. MS Access - Help with Nested Query (Max version)
    By ironfelix717 in forum Queries
    Replies: 5
    Last Post: 03-21-2019, 11:57 PM
  2. MS Access API Call with a Nested Loop
    By djs79 in forum Access
    Replies: 7
    Last Post: 10-27-2015, 02:34 PM
  3. How to Create Nested Tables using Access?
    By Rafegh in forum Database Design
    Replies: 7
    Last Post: 07-24-2014, 03:45 PM
  4. How to Create Nested Tables using Access?
    By Rafegh in forum Programming
    Replies: 2
    Last Post: 07-23-2014, 04:34 PM
  5. Replies: 5
    Last Post: 04-20-2012, 06:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums