Icon

6. Non-Standard Format Spreadsheets

<p>This workflow shows how to work with spreadsheets that come in a non-standard format. The aim is to clean up and rearrange a spreadsheet in a non-standard format, and then apply a row aggregation. We demonstrate this by finding the number of athletes born in 2000 using the included Olympic Athlete Bio file.</p>

URL: Based on this example published by KNIME https://hub.knime.com/s/ukMA7B-vr6Wq96Bs

Non-standard format Spreadsheets

This workflow reads data from an 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.

This workflow was adapted from this example created by KNIME.

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

  • Change birth date datatype

  • Define which columns and rows to keep in the table

Aggregate and export the data

  • Calculate summary statistics

  • Write the statistics into a new sheet in the original Excel file

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

Read the Athlete Bio file (non-standard format)
Excel Reader
Split the table by the separator row
Table Splitter
Concatenate the transformed tables
Concatenate
Extract the date from the misplaced cell
Cell Extractor
Convert birthdayto Local Dateformat
String to Date&Time
Set the second row ascolumn header
Row to Column Names
Remove unnecessary columns
Column Filter
Count the number ofathletes with each birth year
Row Aggregator
Place the extracted cell content in the correct place
Cell Updater
Extract birth year
Date&Time Part Extractor
Row Filter
Write the aggregationtable to a separate sheet
Excel Writer
Write the reformattedtable to a separate sheet
Excel Writer
Column Renamer

Nodes

Extensions

Links