Seems hard to believe, but as recently as 2014 it was difficult to convert csv files to JSON. XML was easy enough, but none of the typical progs that handled csv would output flat files as JSON.
As I found out one morning!
I was project managing a thing that required us to deliver some data to a 3rd party. It was complex, and on the morning of our deadline day that data landed in my inbox and I sent it along to our client. Chuffed I was!
… at least until they got back to us and pointed to an obscure email where they had asked for it in JSON format.
No problem, I thought. How hard can it be to convert a flat data file into JSON? I headed over to my friendly DBA and asked him to do it for me, but he had no tools and neither did any of the developers I asked. Google also was bereft of robust solutions, although I was able to find a couple websites that would do the conversion with some manual steps. That would have been OK, but the data was way too sensitive to put onto the web like that.
So, out comes the VBA. From knowing nothing about JSON in the morning I had a working conversion tool in the afternoon. It was very manual – 18 separate steps were required – but it worked and my deadline was safe!
But this data had to be delivered every month ad-infinitum – its probably still being delivered monthly now. As a contractor I had to leave a robust and viable solution behind me. And so I decided to hone my solution into something anyone could use easily.
For speed and ease I stuck with Excel VBA, and was able to deliver a working solution before my contract expired. For some time my VBA app was a key component of an important workflow – doubt they still use it now though!
It had a few bells and whistles too – the ability to merge JSON files meant that data held in relational tables could be output to multiple csv files, converted to JSON, then merged into a single JSON file (assuming a shared key field of course).
Throughout the build process the idea of developing it Java would not go away. At the very heart of the design of the VBA app was the structure of Excel – I had built it around worksheets that I could use for easy storage and used the tools available to manipulate the data. This often provided very quick wins and easy solutions, but also imposed constraints. Having just developed the app in VBA I had a very good idea for an object model and build unfettered by these constraints, and all the thinking was fresh in my mind. It just made sense to build it in Java, and to do so straight after finishing with the VBA version.
It took considerably longer in Java, despite beginning the project with a very clear idea of what to build and how to do it. I estimate 3 times as much coding time relative to the VBA app. If my life depended on it I would use the Java version, but that might be because it is effectively a second iteration design. The output of both is well tested and reliable though, and the Excel UI is perhaps easier to use, so the jury is out!
Anyway, both versions are decent tools (although these days its not hard to find tools to do the jobs these apps do). You can find both in this GitHub repo.