First to develop tool for STRABAG. This Excel Add-In was supposed to be originally just a single tool for importing Asphalt plants products to Arriba iTWO master project, however, with further requests from my manager and colleague I have decided to unify all importing tools into one modular Add-In. Originally it was written in VBA, later on as I got new laptop with more IDE programs, the latest additions were written in C# for faster performance and simpler code writing.
The Add-In contains simple help documentation and bug reporting form. There are also basic settings for the program behavior. The base modules of the program are: transfer data from custom XML structured file that was designed to contain only the required information for data transfer.
When designing the program, I took the opportunity to set the data format received from STRABAG group companies, STRABAG Asfalt and KAMENOLOMY ČR. With representatives of mentioned companies, I had negotiated and successfully agreed upon exchanged data formats to simplify the process of data transfer.
Data from asphalt, concrete or gravel are converted to Arriba iTWO’s Commodity XML file, whilst checking if key information is not duplicate, and updating the products in the process. If there is need to add a new item, the user must specify only a part ID string that is unique for each item and partially predefined by my predecessors in data management.
For updating the machinery in STRABAG’s master project, the program uses data exported from AS400 custom table, since I wasn’t allowed to access the database with SQL scripts. It cleanses duplicate machines and machines with missing necessary data. Then it orders the items by costs and displays WinForm for the user to have the possibility to add more machines or to check the updated machinery.
Lastly, the program is capable of converting predefined format by my predecessor of URS price catalogue to required XML files by Arriba iTWO to be able to import all required data to the program. The bottleneck of this operation is iTWO’s capacity of reading the uploaded XML files. The conversion itself takes up to 5 minutes. In total it is necessary to convert more than 1 million rows of data in separate Excel files, which some of them must be connected and are dependent on each other.
Converting OTSKP price catalogue takes part solely in C# algorithm, converting 18 000 items within 5 seconds with data validity check and slightly modifying the structure of the data since Arriba iTWO does not support a group to contain simultaneously groups and items.
For this program I have also created a simple documentation for all the functions and data structure requirements.