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