Let's be honest, some Excel files are like a beautifully decorated Christmas tree. They look great and serve a purpose, but when you try to extract data from them, it can be a real headache. In this article, we'll show you how to convert these complicated Excel files into a simpler format that's easier to work with.
What's the difference between Complex and Simple Excel files?
- Simple Excel files typically have one straightforward table per sheet. This table includes a field header and the actual data.
- On the other hand, complex files can have multiple tables per sheet. Some of these tables might even be pivot tables. Often, they come with footer and header information, and the data is scattered all over the place.
How can the Advanced ETL Processor help transform complex Excel files into a simpler format?
In the latest release of Advanced ETL Processor, we have introduced block transformations.
The way they work is very simple:
- The user opens the Excel file.
- Identifies and marks the data block containing relevant information.
- Each data block can be individually transformed, filtered, edited, or rotated.
- The transformed data blocks are then combined and written into one or several Excel files.
Complex Excel file example
Here we have an Excel file with various data blocks such as Date, Company name, Addresses, and Company details. Some data blocks consist just of one cell (Date and name), and "Company details" is a vertical data block.
The objective is to convert this data into a single row.
Simple Excel file
Marking Data blocks
More detailed video tutorial
Advanced ETL Processor Enterprise works correctly with Excel all the time. It can extract data from Excel or update existing Excel files without losing formatting.
Our WIKI has more detailed information if you are stuck post your question on our ETL Support forum and we will do our best to assist you
