Hub
Pricing About
WorkflowWorkflow

Import EXCEL - a meta workflow trying to answer questions about importing data from Excel files

ExcelImportSheetRMicrosoft
+15
mlauber71 profile image
Draft Latest edits on 
Sep 10, 2024 1:46 PM
Drag & drop
Like
Download workflow
Workflow preview
KNIME and Excel — An Overview of What You Can Do
https://medium.com/p/c6d400319745
-------------------------------
Import EXCEL - This workflow will act as a meta workflow trying to answer questions about importing data from Excel files

One of the most frequently asked questions is the one how to import data from Excel files under various circumstances.
At the bottom of this workflow there are some very basic examples you might want to explore.

If you come from Excel and want to learn more about what KNIME can do this (free) book is for you:

From Excel to KNIME - written by Kathrin Melcher: https://www.knime.com/knimepress/download-from-excel-to-knime
Also now in German - jetzt auch auf Deutsch: https://www.knime.com/knimepress/from-excel-to-knime-german
 and there are these videos by the author
  - in German: https://youtu.be/H3ljC0wlVMk&t
  - in English: https://youtu.be/pyulPrrOYNM


External resources

  • Medium: KNIME and Excel — An Overview of What You Can Do
  • 039 - Combine Excel Files from two Folders and export to a new Excel File - matching the file names
  • 041 - create path variables
  • 040 - Excel Import Blocks - one common question is how to import blocks of data from excel sheets
  • 056 - collection about how to use the Continental nodes for Excel
  • 038 - a "bulk load" of Excel files listing sheets (using the "Read Excel Sheet Names" node)
  • 037 - Force KNIME to use individual Excel Sheet names (workaround for AP-18133)
  • 205 - create an Excel file with a complex header with the help of Continental Excel nodes
  • 200 - you can use the Continental Nodes for Excel to do a lot of formatting
  • 036 - Read the sheet names from an existing excel file and add the sheet name as a column and write to a new excel file
  • 035 - Collect data from 4 differet Excel files and put it into sheets of a single file
  • KNIME and Excel - what you can and cannot do
  • 635 - Append data to an existing Excel sheet with the help of OpenPyxl / Python
  • Openpyxl in Python is the way to go if you want more complicated manipulations
  • 057 - Manipulate Excel Cells with the (new) Excel cell updater
  • 055 - Excel Formatting with the Continental nodes
  • 505 - as an alternative import Excel/XLSX file with R's openxlsx package
  • 021 - First Steps into KNIME Analytics Platforms for Excel Users - longer video by Kathrin Melcher
  • 020 - The Excel Reader Node - short video 2021
  • EXCELst Du noch oder KNIMEst du schon? (video in German)
  • From Excel to KNIME - written by Kathrin Melcher
  • 420 - Write excel data file with column with drop down list
  • 415 - KNIME - openpyxl and xlsxwriter to add formulas to excel files
  • 410 - KNIME - openpyxl and xlsxwriter to add formulas to excel files and also PNG
  • 405 - Manipulating Excel sheets with KNIME and Python
  • 400 - you can manipulate individual cells in Excel and add values or formulas using Python OpenpyXL
  • 030 - This workflow demonstrates how to use the new Excel Reader and path variables and read Excel sheets
  • 605 - use Python Pandas and Openpyxl to import and export Excel (sheets) and Export them back
  • 302- it is possible to append data to an existing Excel file with the help of Python Pandas and OpenPyxl
  • 025 - the very new Excel Reader (06/2020) should bring a huge improvement to a lot of Excel-Import related tasks
  • 120 - Loop thru same Excel sheet names from various sources and collect them back
  • 118 - use the current timestamp to construct a filename
  • 115 - construct new header from 2 lines in imported excel file (insert column header)
  • 172 - Read and annotate multiple Excel files
  • 110 - if you want to skip some lines or you want to start reading a header from a different line that can also be done
  • 305 - if the re-evaluation does not work there is a possibility to use Python to force a re-evaluation from within KNIME
  • 300 - people want to have the formulas in Excel re-evaluated when opening the file in KNIME - there are settings for that
  • 620 - if you encouter encrypted Excel files there is also a way to read them with Python
  • 515 - you can also use R to list all sheets from excel file without opening them in KNIME
  • 615 - you can also read binary Excel files (.xlsb) with the help of Python
  • 510 - you want to force a certain type on all your data or let R/KNIME decide for you
  • forum entry
  • 610 - use R package gdata to import older Excel files
  • 550 - you want to import a lot of data from various excel sheets and only keep the ones that are present in all sheets
  • 500 - use R library(readxl) to read XLSX/Excel file into KNIME (and store the data in various formats)
  • 600 - it is possible to use R’s openxlsx to add sheets from within KNIME to an existing Excel file which contains Macros (xlsm)
  • 100 - the standard import - Read an XLS file - by scottf
  • 175 - another example extracting some information about your Excel file and location
  • 170 - and you want to keep some information about the file you jus imported. You could annotate them with the name of the file/sheet
  • 150 - OK you have a changing structure of your Excel files but still want to read them, no problem, just give the collecting end loop some flexibility
  • 050 - the (new) excel import node can also handle multiple files at once from a single folder - you may want to start with that
Loading deploymentsLoading ad hoc jobs

Used extensions & nodes

Created with KNIME Analytics Platform version 4.7.8
  • Go to item
    KNIME Base nodesTrusted extension

    KNIME AG, Zurich, Switzerland

    Version 4.7.8

    knime
  • Go to item
    KNIME Excel SupportTrusted extension

    KNIME AG, Zurich, Switzerland

    Version 4.7.0

    knime
  • Go to item
    KNIME JavasnippetTrusted extension

    KNIME AG, Zurich, Switzerland

    Version 4.7.0

    knime

Legal

By using or downloading the workflow, you agree to our terms and conditions.

KNIME
Open for Innovation

KNIME AG
Talacker 50
8001 Zurich, Switzerland
  • Software
  • Getting started
  • Documentation
  • Courses + Certification
  • Solutions
  • KNIME Hub
  • KNIME Forum
  • Blog
  • Events
  • Partner
  • Developers
  • KNIME Home
  • Careers
  • Contact us
Download KNIME Analytics Platform Read more about KNIME Business Hub
© 2025 KNIME AG. All rights reserved.
  • Trademarks
  • Imprint
  • Privacy
  • Terms & Conditions
  • Data Processing Agreement
  • Credits