Excel file makes AI Cry

The Excel Challenge

Imagine that you have an Excel file with 50 sheets, every one of them has 5-10 tables and your job is to load it into the database.

There are multiple ways of solving this problem

1. If it is just once off, preparing the file manually is the easiest option
2. If you receive multiple files per day you may consider hiring a programmer to write a conversion script for you. Due to the complexity of the data the script is going to be very large, It will be very costly, time-consuming, and may lead to errors, especially after format changes
3. Another option is using AI tools, but it would be impossible to engineer the correct prompt.
4. Using ETL software. Although it is possible it will require creating transformations for every single table within the source Excel file (in the worst case about 500 transformations). This would be unmanageable.

The Excel challenge

In the latest version of our ETL software, we have introduced block transformations. It works with data blocks  The user marks the data block with an Excel file, transforms blocks using steps, and saves the results into an Excel file.

Marking data block

Note: It is possible to select cells, cols, rows, entire sheets, and groups of cells. Block transformation works with multiple Excel files and data blocks

Transforming data block using steps

Note:  Steps are executed sequentially from the left to the right

The following Transformation steps are supported

Running steps