Non-standard format Spreadsheets

Non-standard format Spreadsheets

This workflow reads data from en Excel sheet which contains information other than what is defined by column headers and rowIDs. It extracts, appends, and filters the data into a formatted table and performs aggregations on it. Finally, it writes the results into a new sheet in the same Excel file.

You can find more workflows and resources for spreadsheet users on the related collection page on the KNIME Community Hub.

Task

Find the number of Olympic athletes born in 2000

Read and arrange parts of the Excel sheet

• Extract column headers from other than the top row

• Shift data entered into a wrong cell

• Remove rows without information

Combine and clean the extracted data

• Concatenate rows after collecting and arranging it in the previous step

• Define which columns and rows to keep in the table

Aggregate and export the data

• Calculate summary statistics

• Append the statistics to the formatted table

• Write the formatted table into a new sheet in the original Excel file