Description
The objective of this project was to read very large csv files, and extract the data by classifying them in much lighter Excel files, according to defined criteria. This operation makes it possible to store the data neatly and make the information more accessible than with the original csv files.
Program developped
I developed a VBA (Visual Basic for Application) script on Access. The program has a simple user interface as well as significant flexibility to allow the user to process data despite changes in the structure of the source files. The program starts by browsing all the source csv files and then integrates the data into the Access database. Then, the program performs different queries on the AS/400 in order to update some data. To do this, I used an ODBC driver that I integrated into my VBA code. I followed a training on the AS/400 in the company, in order to learn how to make queries in the different bases, to aggregate them, and to extract the results. The program also generates Excel summary tables, in order to represent different statistics on the contents of the files. Finally, differents SQL queries are run to extract the data, and saves the results in Excel files.
Result
The time saving generated by the developed tool, compared to the formerly manual treatment, is estimated at 20 days/ men. In addition, automation has the great advantage of making processing more reliable, which is very important for a regulatory operation of this type. Reliability applies from the cutting of files by population (formerly subject to a risk of errors in the manipulation of Excel filters) to the completion of summary tables (formerly filled by hand with a calculator).
Technology used
- Access
- Excel
- VBA
- AS/400
- SQL
- ODBC Driver