Here are some example uses of the Read Excel File step. All examples in this post work with the following Excel file: club.xlsx (37.3 KB)
Reading Table Data
Reading table data from Excel files is possibly the most common use case. Common pitfalls include inconsistent data types in Excel columns and unexpected extra empty rows and columns.
Our example flow read-members.dfl (23.7 KB) reads from the ‘Members’ sheet, which looks like this:
The strategy is to try and coerce values from the age and join date columns to their respective types, and failing that to revert to a nil value.
Reading Data Card Data
Another common occurrence is that data-cards are placed somewhere within the sheet and a data objects should be extracted from them. Have a look at the ‘Locations’ sheet:
Each of the locations is presented as a little data card.
Our example flow read-locations.dfl (28.5 KB) tries to find the data cards and extract location records.
Reading Cross-Tab Data
Perhaps the most daunting data processing job is to extract records from cross-tab tables. Have a look at the ‘Tournament’ sheet summarizing the results of a multi-day tournament, where each day had a series of rounds of games.
The task is to extract a record for each game, including its date, round, location, winner id, score, and comment on the score if there is any.
Our extraction flow read-tournament.dfl (30.7 KB) first determines the size and boundaries of the data table, and then iterates over each game by calculating the position of its location cell. The surrounding cells and row/column indexes provide all the information we need.
