During the COVID-19 pandemic, England witnessed a chaotic situation while using MS Excel as the repository solution. The government was not prepared to handle the COVID-19 results data, which led to data misinterpretation and data loss.
Because of this whole data mess, the government's glossy reputation got a big dent. The official figures and public no longer had confidence in the government. What's more, this situation left the government to be a laughingstock on social media.
This blog will discuss what went wrong with MS Excel in this situation and why excel is important for the business.
One of the BBC articles claimed that Public Health England was using an old file format for the COVID-19 results recordkeeping. The old Excel format only allows 65,536 rows, whereas the new Excel file format permits a whopping 1,048,576 rows.
The sources of this data came from multiple commercial firms, and the medium of transfer was CSV files. Many times, these files could be larger than the old Excel rows could incorporate.
Excel is an exceptional tool for presenting, consolidating, and recordkeeping. The MS Excel allows its users to take the help of summaries and charts for better interpretation. However, these tools are only as useful as the quality of the imported data.
Another problem with using the old Excel format is that even if you transfer the file into the new version, it still stays at 65,536 rows. Unless the data handler saves the data again in the new file format, all the things remain the same. It seems like the data handler did not follow this saving process.
The developers were using the old XLS dashboard without considering its limited row capacity. This mistake probably resulted in the VBA code to automate loading the CSV files into the old dashboard.
If the developers were using VBA files to load the CSV files, then MS Excel might not give any warnings to the exceeded number of rows. Due to this process, the fundamental error with data misplacement took place.
How To Prevent A Situation Like This
The whole problem had a very straightforward solution, which the developers did not care to implement. After importing the CSV file within the VBA import code, it should have a saved copy as a CSV file under a different name. It was then possible to use the VBA to compare the copy and the original file size.
If both sides did not agree, this would mean a red flag of danger. The developers could’ve linked the VBA directly to the CSV files and utilize each file as a database. Alternatively, in this situation, using an ADO recordset for each CSV file would have been a better approach.
It is also possible to connect to the current workbook and interpret each worksheet as a separate table through ADO. This process enables full SQL commands that offer enormous flexibility and maximizes MS Excel's efficiency as a recordkeeping tool.
However, due to time constraints, the government and the developers would've rushed through the whole process. It was possible to avoid the entire situation only if the developers were more cautious about the process. MS Excel use in the company is quite extensive.
The Bottom Line
Excel is an outstanding database and recordkeeping tool. MS Excel can even serve as a dashboard within the same application. The problem that took place in the above situation, poor design, and inadequate implementation.
Their old-school plan was not adept at handling the health records of thousands of patients. The failure of the whole procedure was also because of the lack of testing. It was better to put large CSV files into use for checks and testing.
There's also a high chance that the developers did not have a complete understanding of Excel. Sadly, this damaged the reputation of the government, and led to people question is ms excel capable or not.
Comments 0
