This may be more difficult to represent in flattened CSV or Excel files. One benefit of structured data like JSON and XML is that it *is* structured, so that relationships and hierarchies can be represented in the JSON (or XML). The other aspect to consider is what you plan to do with the flattened JSON data. In these cases it may not make sense to flatten the JSON. These can become quite complex with embedded data-within-data. The reason for this is fairly simple: 'csv' stands for 'comma separated values', and VBA 'thinks the American way' and doesn't use the local settings (here: semicolon). At one bank I did some work for, financial products were represented in JSON. It is easy to open a csv file in Excel, you just double-click the icon, and doing it with an Excel VBA macro is also straigthforward, unless it is a semicolon-delimited file. However, JSON can also represent far more complex data structures. It’s a two-dimensional structure and very easy to ‘flatten’. The JSON example in this article is very simple. Special workarounds like encoding the line break are unnecessary. If this does not work for you, you are doing something wrong.
![excel import xml file keep code split values excel import xml file keep code split values](https://i.stack.imgur.com/dvmWe.jpg)
Now whether it’s a good approach to flatten JSON like that tool does, has a very succinct answer: it depends. In XML a line break is a normal character. IN a sense, that’s sort of what we’re doing with the VBA code example in this article.
![excel import xml file keep code split values excel import xml file keep code split values](https://i.stack.imgur.com/FvLfu.png)
The tool you provided the link for looks quite interesting.
Excel import xml file keep code split values update#
I’ll probably update this post with your and his feedback as it’s been really helpful to know what things can go wrong. Let me know how you go with those 2 checks.Īnother commenter, Phil experienced issues as he’s running a 64-bit edition of Excel (I’m running a 32-bit edition). If you step through the code, you should be able to view the contents of the items object. You can copy the code and insert it into a VBA module. I dont know the author, but he deserves the credit. There is no reason to reinvent the wheel, and the following code, which I found on the internet, works just fine. If it looks okay, then check what’s in the “items” object. If for some reason you dont want to use Excels built-in import functions, you can parse ('read') a csv or txt file using code. If there is no JSON or it looks corrupted, if you’re running this code from work (corporate) environment, make sure there are no firewall issues stopping you from retrieving data from the web. This will spit out the raw JSON to the Immediate window so you can check it. Try adding the following line right after the script.Eval line above: The first thing to check is the raw JSON that has been returned from the website. This takes the raw JSON which was returned from the website and turns it into an “object” which can be iterated through like a dictionary or collection. Set items = script.Eval(“(” & response & “)”)
![excel import xml file keep code split values excel import xml file keep code split values](https://i.stack.imgur.com/V0V2Z.png)
Just prior to calling the GetItemCount helper function, there’s this line: