Out Of Necessity
Zev, a longtime reader of The Daily WTF, has a confession to make.
It all started with the best of intentions. Zev works for a large company doing custom development; they use various databases and tools, but the most common tool they're asked to develop against is VBA for Microsoft Excel with an Access backend. One recent project involved data moving from an on-premise SQL Server solution to the cloud. This meant rebuilding all their reports to connect to an API instead of using ODBC to get the data. Enter Zev.
The cloud tool was pretty well developed. By passing in an API key, you could get data back in a variety of formats, including JSON, HTML, XML, and CSV. Obviously choice number one was JSON, which is quickly becoming the de facto language of APIs everywhere. Upon doing a quick survey, however, Zev found many of his users were stuck on Office 2013, which can't parse JSON natively.
No worries. There's always XML. Zev churned out a quick Excel file with an XML-map in it and used code to pull the data down from the API on demand. Now the hard part: plugging into Access. Turns out, in Office 2013, you can't use a network XML file as a data source, only a local one.
Well, Excel can feed the data into a table, which Access can read, but that takes longer. In Zev's case, far too long: minutes, for a relatively small amount of data. Okay, no problem; the code can download the XML to a local file, then connect to it as an XML table. Except that turns out to be no faster.
Zev's next try was to build Excel files for each of the queries, then connect Access to the Excel files as tables. Then he could add code to open and refresh the Excel files before using them. On some days, that took longer than the old way, while on other days it worked fine. And sometimes it managed to lose the Excel files, or they'd run into lock file issues. What gives?
Zev's testing concluded that the same query returning took twice as long via XML as it did via CSV, which makes sense: XML is about twice as fat as CSV. So the final product used VBA to download the data as a CSV file, then connect to the CSV file as a local Excel table through Access.
In Zev's own words:
My greatest fear is that someone will see this code and submit it to the Daily WTF and ask "why?"
We tried to use JSON, because it is the new hotness. But lo, our tools did not support it. We tried to use XML because it was the last hotness. But lo, it took too long to process. Shuddering and sobbing, we defaulted to CSV. And so we wrote code 20 years out of date, not out of hubris or lack of desire to learn, but out of cold, heartless, necessity.
[Advertisement] Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!