Icon

kn_​example_​excel_​import_​meta_​workflow

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

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

Collection of additional Excel / KNIME Links-------------------------------------KNIME and Excel - what you can and cannot do (as of now)https://forum.knime.com/t/excel-reader-transformation-removing-columns-multiple-sheets/38326/4?u=mlauber71-------------------------------------020 - The Excel Reader Node - short video 2021https://youtu.be/vyJXSP1e_LI021 - First Steps into KNIME Analytics Platforms for Excel Users - longer video by Kathrin Melcherhttps://youtu.be/pyulPrrOYNM025 - the very new Excel Reader (06/2020) should bring a huge improvement to a lot of Excel-Import related taskshttps://kni.me/n/I129Ntc6bhMN6EpL030 - This workflow demonstrates how to use the new Excel Reader and path variables and read Excel sheetshttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_new_node_list_sheets?u=mlauber71035 - Collect data from 4 different Excel files and put it into sheets of a single filehttps://kni.me/w/LjB2JAnV2mEOi104036 - Read the sheet names from an existing excel file and add the sheet name as a column and write to a new excel filehttps://kni.me/w/X581zhevHB312F5-037 - Force KNIME to use individual Excel Sheet names (workaround for AP-18133)https://forum.knime.com/t/bug-or-no-bug-excel-reader-preconfigured-work-sheet-selection-options/38226/7?u=mlauber71038 - a "bulk load" of Excel files listing sheets (using the "Read Excel Sheet Names" node)https://forum.knime.com/t/bulk-load-of-excel-sheet-with-empty-worksheet/43934/2?u=mlauber71039 - Combine Excel Files from two Folders and export to a new Excel File - matching the file nameshttps://forum.knime.com/t/consolidate-multiple-excel-files-into-one/66846/12?u=mlauber71040 - Excel Import Blocks - one common question is how to import blocks of data from excel sheetshttps://forum.knime.com/t/move-head-rows-to-a-column/45094/2?u=mlauber71 041 - create path variables: https://forum.knime.com/t/write-multiple-csv-file-to-a-loop/44989/2?u=mlauber71 050 - the (now deprecated) excel import node can also handle multiple files at once from a single folderhttps://hub.knime.com/knime/extensions/org.knime.features.ext.poi/latest/org.knime.ext.poi2.node.read4.XLSReaderNodeFactory100 - the standard import - Read an XLS file - by scottfhttps://hub.knime.com/knime/spaces/Examples/latest/01_Data_Access/01_Common_Type_Files/07_Reading_Excel_Files=> most of the time you will just be fine using these examples110 - if you want to skip some lines or you want to start reading a header from a different line that can also be donehttps://forum.knime.com/t/excel-import-numbers-recognized-as-string-which-leads-to-an-automatic-roundup-of-all-numbers/12425/14?u=mlauber71https://hub.knime.com/mlauber71/spaces/Public/latest/forum/kn_forum_12425_import_excel115 - construct new header from 2 lines in imported excel file (insert column header)https://forum.knime.com/t/excel-reader-merged-cells/24916/3?u=mlauber71https://hub.knime.com/mlauber71/spaces/Public/latest/forum/kn_forum_excel_2lines_header118 - use the current timestamp to construct a filenamehttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_timestamp_as_filename119 - try to untangle a complex Excel file, use RowIDs and fill blockshttps://forum.knime.com/t/filtering-for-a-subset-of-data-from-a-single-table/40107/9?u=mlauber71120 - Loop thru same Excel sheet names from various sources and collect them backhttps://forum.knime.com/t/multiple-excels-files-with-multiple-sheets/27953/2?u=mlauber71150 - 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 flexibilityhttps://forum.knime.com/t/how-to-read-multiple-excel-files-in-a-folder-and-multiple-sheets-in-an-excel-file/10723/9?u=mlauber71170 - and you want to keep some information about the file you just imported. You could annotate them with the name of the file/sheethttps://forum.knime.com/t/can-knime-help-me-input-words-in-excel-automatically/25071/5?u=mlauber71 172 - Read and annotate multiple Excel files https://kni.me/w/32TRHcgHTUeWoUup175 - another example extracting some information about your Excel file and locationhttps://hub.knime.com/mlauber71/spaces/Public/latest/forum/kn_forum_excel_read_multiple_files_and_sheets------------------------ KNIME, Excel and Continental Formats and AF Community Nodes ------------------------200 - you can use the Continental Nodes for Excel to do a lot of formatting (https://www.knime.com/community/continental-nodes-for-knime)https://kni.me/e/JPEQiK3qwxAJb5tU201 - Excel Formatting with the Continental nodeshttps://forum.knime.com/t/problem-with-decimal-point/38724/5?u=mlauber71202 - collection about how to use the Continental nodes for Excelhttps://forum.knime.com/t/excel-formating/44593/2?u=mlauber71205 - create an Excel file with a complex header with the help of Continental Excel nodeshttps://kni.me/w/FLsPoDBusoKLMTWb220 - Manipulate Excel Cells with the (new) Excel cell updaterhttps://kni.me/n/pfKae1pgEo_UWX2K240 - Re-Evaluate Formulas in Excelhttps://forum.knime.com/t/excel-reader-and-excel-writer-reevaluate/38380/2?u=mlauber71https://forum.knime.com/t/new-excel-writer-having-problems-with-evaluate-formulas/36393/6?u=mlauber71-----------------------------------------------------300 - people want to have the formulas in Excel re-evaluated when opening the file in KNIME - there are settings for thathttps://forum.knime.com/t/xl-eval-error/13258/2?u=mlauber71302- it is possible to append data to an existing Excel file with the help of Python Pandas and OpenPyxlhttps://forum.knime.com/t/data-extraction-from-xls-file-s/29528/12?u=mlauber71305 - if the re-evaluation does not work there is a possibility to use Python to force a re-evaluation from within KNIMEhttps://forum.knime.com/t/how-to-apply-excel-expression-on-excel-through-knime/19358/19?u=mlauber71https://forum.knime.com/t/excel-based-queries-refresh-through-knime/21336/2?u=mlauber71------------------------ KNIME, Excel and Python/OpenpyXL (cf. also 600s) ------------------------400 - you can manipulate individual cells in Excel and add values or formulas using Python OpenpyXLhttps://forum.knime.com/t/excel-reader-dumps-formulas/14672/2?u=mlauber71405 - Manipulating Excel sheets with KNIME and Pythonhttps://forum.knime.com/t/manipulating-excel-sheets/15541/2?u=mlauber71410 - KNIME - openpyxl and xlsxwriter to add formulas to excel files and also PNGhttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_enter_formula?u=mlauber71415 - KNIME - openpyxl and xlsxwriter to add formulas to excel fileshttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_enter_formula_openpyxl?u=mlauber71420 - Write excel data file with column with drop down listhttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_drop_down?u=mlauber71------------------- some more complicated scenarios that can be solved easily with the help of R (and Python) -----------500 - use R library(readxl) to read XLSX/Excel file into KNIME (and store the data in various formats)https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_r_read_single_xlsx_file505 - as an alternative import Excel/XLSX file with R's openxlsx packagehttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_r_openxlsx_read_excel_file~3_qd7tq5wPIC3u56510 - you want to force a certain type on all your data or let R/KNIME decide for youhttps://hub.knime.com/mlauber71/spaces/Public/latest/forum/kn_example_r_readxl/m_001_r_readxlhttps://forum.knime.com/t/how-to-force-column-data-type-in-excel-reader-xls-node/9088/8?u=mlauber71=> sometimes there is a problem with data types when importing data from excel. You could empliy R to help you solve the problem by guessing or forcing a type and deal with it later515 - you can also use R to list all sheets from excel file without opening them in KNIMEhttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_r_excel_multiple_sheets/m_001_import550 - you want to import a lot of data from various excel sheets and only keep the ones that are present in all sheetshttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_r_excel_find_united_fields/m_001_import_r_excel_unite_fieldshttps://forum.knime.com/t/reading-excel-sheets-with-different-column-counts-and-different-data-types/19245/2?u=mlauber71Openpyxl in Python is the way to go if you want more complicated manipulationshttps://hub.knime.com/search?tag=openpyxl=> how to set up KNIME and Python, please check here: https://kni.me/w/AvjrddXKOIoZYLV3KNIME 4.6 will have an integrated Python which makes it easier to use600 - it is possible to use R’s openxlsx to add sheets from within KNIME to an existing Excel file which contains Macros (xlsm)https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_macros_xlsm605 - use Python Pandas and Openpyxl to import and export Excel (sheets) and Export them backhttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_import_export_openpyxl?u=mlauber71610 - use R package gdata to import older Excel files (think like Excel 5.0/7.0)https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_old_files_import=> if you encounter very old excel file formats not all is lost. You can employ R to still import them into KNIME615 - you can also read binary Excel files (.xlsb) with the help of Pythonhttps://forum.knime.com/t/xlsb-files/23402/2?u=mlauber71https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_xlsb620 - if you encouter encrypted Excel files there is also a way to read them with Pythonhttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_read_password_protected_file625 - OK you want to filter data from Excel by the color of a cell - that is also possiblehttps://forum.knime.com/t/filter-excel-table-by-colour/20633/3?u=mlauber71https://hub.knime.com/mlauber71/spaces/Public/latest/forum/kn_forum_python_openpyxl_colour630 - How about extracting pictures stored in Excel - no easy task but there was a debate with some suggestions that might workhttps://forum.knime.com/t/export-pictures-from-excel-xls-or-xlsx/14714/10635 - Append data to an existing Excel sheet with the help of OpenPyxl / Pythonhttps://forum.knime.com/t/data-extraction-from-xls-file-s/29528/12?u=mlauber71640 - KNIME - use Pandas and OpenPyxl to import a protected and formatted excel sheet - where import is otherwise slowhttps://forum.knime.com/t/knime-4-5-2-excel-reader-extremely-slow-when-reading-from-other-sheet-different-than-the-first/41008/15?u=mlauber71641 - use KNIME and Python OpenPyxl to read the index numbers of every sheet in an Excel file - then move a sheet from one index position to a new onehttps://forum.knime.com/t/ordering-of-excel-worksheets/43929/4?u=mlauber71642 - KNIME and Python's openpyxl to extract named tables (ranges) from Excel filehttps://forum.knime.com/t/read-from-excel-table/38190/8?u=mlauber71643 - Python - import a large Excel file with 2,000 columns and 25,000 lines - 300 MB sizehttps://forum.knime.com/t/increased-excel-file-size-and-excel-writer-sheet-append-issue-with-knime-4-6-0/62130/5?u=mlauber71 relative path for sub-folder of workflownot working? KNIME File Handling Guidehttps://docs.knime.com/latest/analytics_platform_file_handling_guide/index.html#introductionhttps://forum.knime.com/t/write-multiple-csv-file-to-a-loop/44989/2?u=mlauber71Create 4 different Excel Files in a folder /data/collection/ KNIME Flow Control Guide (Loops and Flow Variables)https://docs.knime.com/latest/analytics_platform_flow_control_guide/index.html#introductionImport all 4 files from the subfolder /data/collection/ at once with one node Deprecated (old) Excel writer nodes Excel Reader - force sheet_selection "NAME" with Flow variablehttps://forum.knime.com/t/bug-or-no-bug-excel-reader-preconfigured-work-sheet-selection-options/38226/7?u=mlauber71 create sample file in the subfolder /data/ under this workflow named:"new_excel_multiple_sheets.xlsx" with one sheet per Cluster Membership Write and read simple Excel file "new_excel_file_1.xlsx" Import EXCEL - This workflow will act as a meta workflow trying to answer questions about importing data from Excel filesOne of the most frequently asked questions is the one how to import data from Excel files under various circumstances.At the right 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-knimeAlso 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 Excel Import Blocks - one common question is how to import blocks of data from excel sheetshttps://forum.knime.com/t/move-head-rows-to-a-column/45094/2?u=mlauber71 Excel – loop overwrite in the first loop https://forum.knime.com/t/group-loop-not-working-with-sheets-renaming/61194/2?u=mlauber71 Excel – allow changing file schema to import several fileshttps://forum.knime.com/t/excel-reader-node-doesnt-refresh-the-data-when-reset-and-executed-again/45672/6?u=mlauber71 KNIME and Excel — An Overview of What You Can Dohttps://medium.com/p/c6d400319745 Medium: KNIME, Paths and Loops — Automate Everythinghttps://medium.com/p/213455788685 sheet 1100 lineswith 4 groups of"Cluster Membership"knime://knime.workflow/data/excel_file_1.xlsxsheet1knime://knime.workflow/data/excel_file_1.xlsxsheet2knime://knime.workflow/data/excel_file_2.xlsxsheet3knime://knime.workflow/data/excel_file_1.xlsxsheet2new_excel_file_1.xlsxknime://knime.workflow/data/new_excel_file_1.xlsxloop byCluster MembershipCluster_<...>.xlsxxlsx_nameall .xlsx files from/data/collection/xlsx_namenew_excel_multiple_sheets.xlsxcreate path variables:https://forum.knime.com/t/write-multiple-csv-file-to-a-loop/44989/2?u=mlauber71loop byCluster MembershipSheets fromnew_excel_multiple_sheets.xlsx=> force the use of the NAMEfor sheetsnew_excel_multiple_sheets.xlsxwith 4 sheetstry to initially deletenew_excel_multiple_sheets.xlsxraed sheet namesfromnew_excel_multiple_sheets.xlsxvar_sheet_selectionStart to readsheetscollect read sheetsExcel Reader (XLS)(deprecated) Data Generator Excel Writer (XLS)(deprecated) Excel Sheet Appender(XLS) (deprecated) Excel Writer (XLS)(deprecated) Excel Sheet Appender(XLS) (deprecated) Excel Writer Excel Reader Group Loop Start Excel Writer Create File/FolderVariables Variable Loop End Excel Reader Create File/FolderVariables Variable Loop End Group Loop Start Excel Reader Excel Writer DeleteFiles/Folders Try (VariablePorts) Catch Errors(Var Ports) Merge Variables Read ExcelSheet Names Java EditVariable (simple) Table Row ToVariable Loop Start Loop End Collection of additional Excel / KNIME Links-------------------------------------KNIME and Excel - what you can and cannot do (as of now)https://forum.knime.com/t/excel-reader-transformation-removing-columns-multiple-sheets/38326/4?u=mlauber71-------------------------------------020 - The Excel Reader Node - short video 2021https://youtu.be/vyJXSP1e_LI021 - First Steps into KNIME Analytics Platforms for Excel Users - longer video by Kathrin Melcherhttps://youtu.be/pyulPrrOYNM025 - the very new Excel Reader (06/2020) should bring a huge improvement to a lot of Excel-Import related taskshttps://kni.me/n/I129Ntc6bhMN6EpL030 - This workflow demonstrates how to use the new Excel Reader and path variables and read Excel sheetshttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_new_node_list_sheets?u=mlauber71035 - Collect data from 4 different Excel files and put it into sheets of a single filehttps://kni.me/w/LjB2JAnV2mEOi104036 - Read the sheet names from an existing excel file and add the sheet name as a column and write to a new excel filehttps://kni.me/w/X581zhevHB312F5-037 - Force KNIME to use individual Excel Sheet names (workaround for AP-18133)https://forum.knime.com/t/bug-or-no-bug-excel-reader-preconfigured-work-sheet-selection-options/38226/7?u=mlauber71038 - a "bulk load" of Excel files listing sheets (using the "Read Excel Sheet Names" node)https://forum.knime.com/t/bulk-load-of-excel-sheet-with-empty-worksheet/43934/2?u=mlauber71039 - Combine Excel Files from two Folders and export to a new Excel File - matching the file nameshttps://forum.knime.com/t/consolidate-multiple-excel-files-into-one/66846/12?u=mlauber71040 - Excel Import Blocks - one common question is how to import blocks of data from excel sheetshttps://forum.knime.com/t/move-head-rows-to-a-column/45094/2?u=mlauber71 041 - create path variables: https://forum.knime.com/t/write-multiple-csv-file-to-a-loop/44989/2?u=mlauber71 050 - the (now deprecated) excel import node can also handle multiple files at once from a single folderhttps://hub.knime.com/knime/extensions/org.knime.features.ext.poi/latest/org.knime.ext.poi2.node.read4.XLSReaderNodeFactory100 - the standard import - Read an XLS file - by scottfhttps://hub.knime.com/knime/spaces/Examples/latest/01_Data_Access/01_Common_Type_Files/07_Reading_Excel_Files=> most of the time you will just be fine using these examples110 - if you want to skip some lines or you want to start reading a header from a different line that can also be donehttps://forum.knime.com/t/excel-import-numbers-recognized-as-string-which-leads-to-an-automatic-roundup-of-all-numbers/12425/14?u=mlauber71https://hub.knime.com/mlauber71/spaces/Public/latest/forum/kn_forum_12425_import_excel115 - construct new header from 2 lines in imported excel file (insert column header)https://forum.knime.com/t/excel-reader-merged-cells/24916/3?u=mlauber71https://hub.knime.com/mlauber71/spaces/Public/latest/forum/kn_forum_excel_2lines_header118 - use the current timestamp to construct a filenamehttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_timestamp_as_filename119 - try to untangle a complex Excel file, use RowIDs and fill blockshttps://forum.knime.com/t/filtering-for-a-subset-of-data-from-a-single-table/40107/9?u=mlauber71120 - Loop thru same Excel sheet names from various sources and collect them backhttps://forum.knime.com/t/multiple-excels-files-with-multiple-sheets/27953/2?u=mlauber71150 - 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 flexibilityhttps://forum.knime.com/t/how-to-read-multiple-excel-files-in-a-folder-and-multiple-sheets-in-an-excel-file/10723/9?u=mlauber71170 - and you want to keep some information about the file you just imported. You could annotate them with the name of the file/sheethttps://forum.knime.com/t/can-knime-help-me-input-words-in-excel-automatically/25071/5?u=mlauber71 172 - Read and annotate multiple Excel files https://kni.me/w/32TRHcgHTUeWoUup175 - another example extracting some information about your Excel file and locationhttps://hub.knime.com/mlauber71/spaces/Public/latest/forum/kn_forum_excel_read_multiple_files_and_sheets------------------------ KNIME, Excel and Continental Formats and AF Community Nodes ------------------------200 - you can use the Continental Nodes for Excel to do a lot of formatting (https://www.knime.com/community/continental-nodes-for-knime)https://kni.me/e/JPEQiK3qwxAJb5tU201 - Excel Formatting with the Continental nodeshttps://forum.knime.com/t/problem-with-decimal-point/38724/5?u=mlauber71202 - collection about how to use the Continental nodes for Excelhttps://forum.knime.com/t/excel-formating/44593/2?u=mlauber71205 - create an Excel file with a complex header with the help of Continental Excel nodeshttps://kni.me/w/FLsPoDBusoKLMTWb220 - Manipulate Excel Cells with the (new) Excel cell updaterhttps://kni.me/n/pfKae1pgEo_UWX2K240 - Re-Evaluate Formulas in Excelhttps://forum.knime.com/t/excel-reader-and-excel-writer-reevaluate/38380/2?u=mlauber71https://forum.knime.com/t/new-excel-writer-having-problems-with-evaluate-formulas/36393/6?u=mlauber71-----------------------------------------------------300 - people want to have the formulas in Excel re-evaluated when opening the file in KNIME - there are settings for thathttps://forum.knime.com/t/xl-eval-error/13258/2?u=mlauber71302- it is possible to append data to an existing Excel file with the help of Python Pandas and OpenPyxlhttps://forum.knime.com/t/data-extraction-from-xls-file-s/29528/12?u=mlauber71305 - if the re-evaluation does not work there is a possibility to use Python to force a re-evaluation from within KNIMEhttps://forum.knime.com/t/how-to-apply-excel-expression-on-excel-through-knime/19358/19?u=mlauber71https://forum.knime.com/t/excel-based-queries-refresh-through-knime/21336/2?u=mlauber71------------------------ KNIME, Excel and Python/OpenpyXL (cf. also 600s) ------------------------400 - you can manipulate individual cells in Excel and add values or formulas using Python OpenpyXLhttps://forum.knime.com/t/excel-reader-dumps-formulas/14672/2?u=mlauber71405 - Manipulating Excel sheets with KNIME and Pythonhttps://forum.knime.com/t/manipulating-excel-sheets/15541/2?u=mlauber71410 - KNIME - openpyxl and xlsxwriter to add formulas to excel files and also PNGhttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_enter_formula?u=mlauber71415 - KNIME - openpyxl and xlsxwriter to add formulas to excel fileshttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_enter_formula_openpyxl?u=mlauber71420 - Write excel data file with column with drop down listhttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_drop_down?u=mlauber71------------------- some more complicated scenarios that can be solved easily with the help of R (and Python) -----------500 - use R library(readxl) to read XLSX/Excel file into KNIME (and store the data in various formats)https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_r_read_single_xlsx_file505 - as an alternative import Excel/XLSX file with R's openxlsx packagehttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_r_openxlsx_read_excel_file~3_qd7tq5wPIC3u56510 - you want to force a certain type on all your data or let R/KNIME decide for youhttps://hub.knime.com/mlauber71/spaces/Public/latest/forum/kn_example_r_readxl/m_001_r_readxlhttps://forum.knime.com/t/how-to-force-column-data-type-in-excel-reader-xls-node/9088/8?u=mlauber71=> sometimes there is a problem with data types when importing data from excel. You could empliy R to help you solve the problem by guessing or forcing a type and deal with it later515 - you can also use R to list all sheets from excel file without opening them in KNIMEhttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_r_excel_multiple_sheets/m_001_import550 - you want to import a lot of data from various excel sheets and only keep the ones that are present in all sheetshttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_r_excel_find_united_fields/m_001_import_r_excel_unite_fieldshttps://forum.knime.com/t/reading-excel-sheets-with-different-column-counts-and-different-data-types/19245/2?u=mlauber71Openpyxl in Python is the way to go if you want more complicated manipulationshttps://hub.knime.com/search?tag=openpyxl=> how to set up KNIME and Python, please check here: https://kni.me/w/AvjrddXKOIoZYLV3KNIME 4.6 will have an integrated Python which makes it easier to use600 - it is possible to use R’s openxlsx to add sheets from within KNIME to an existing Excel file which contains Macros (xlsm)https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_macros_xlsm605 - use Python Pandas and Openpyxl to import and export Excel (sheets) and Export them backhttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_import_export_openpyxl?u=mlauber71610 - use R package gdata to import older Excel files (think like Excel 5.0/7.0)https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_old_files_import=> if you encounter very old excel file formats not all is lost. You can employ R to still import them into KNIME615 - you can also read binary Excel files (.xlsb) with the help of Pythonhttps://forum.knime.com/t/xlsb-files/23402/2?u=mlauber71https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_xlsb620 - if you encouter encrypted Excel files there is also a way to read them with Pythonhttps://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_python_excel_read_password_protected_file625 - OK you want to filter data from Excel by the color of a cell - that is also possiblehttps://forum.knime.com/t/filter-excel-table-by-colour/20633/3?u=mlauber71https://hub.knime.com/mlauber71/spaces/Public/latest/forum/kn_forum_python_openpyxl_colour630 - How about extracting pictures stored in Excel - no easy task but there was a debate with some suggestions that might workhttps://forum.knime.com/t/export-pictures-from-excel-xls-or-xlsx/14714/10635 - Append data to an existing Excel sheet with the help of OpenPyxl / Pythonhttps://forum.knime.com/t/data-extraction-from-xls-file-s/29528/12?u=mlauber71640 - KNIME - use Pandas and OpenPyxl to import a protected and formatted excel sheet - where import is otherwise slowhttps://forum.knime.com/t/knime-4-5-2-excel-reader-extremely-slow-when-reading-from-other-sheet-different-than-the-first/41008/15?u=mlauber71641 - use KNIME and Python OpenPyxl to read the index numbers of every sheet in an Excel file - then move a sheet from one index position to a new onehttps://forum.knime.com/t/ordering-of-excel-worksheets/43929/4?u=mlauber71642 - KNIME and Python's openpyxl to extract named tables (ranges) from Excel filehttps://forum.knime.com/t/read-from-excel-table/38190/8?u=mlauber71643 - Python - import a large Excel file with 2,000 columns and 25,000 lines - 300 MB sizehttps://forum.knime.com/t/increased-excel-file-size-and-excel-writer-sheet-append-issue-with-knime-4-6-0/62130/5?u=mlauber71 relative path for sub-folder of workflownot working? KNIME File Handling Guidehttps://docs.knime.com/latest/analytics_platform_file_handling_guide/index.html#introductionhttps://forum.knime.com/t/write-multiple-csv-file-to-a-loop/44989/2?u=mlauber71Create 4 different Excel Files in a folder /data/collection/ KNIME Flow Control Guide (Loops and Flow Variables)https://docs.knime.com/latest/analytics_platform_flow_control_guide/index.html#introductionImport all 4 files from the subfolder /data/collection/ at once with one node Deprecated (old) Excel writer nodes Excel Reader - force sheet_selection "NAME" with Flow variablehttps://forum.knime.com/t/bug-or-no-bug-excel-reader-preconfigured-work-sheet-selection-options/38226/7?u=mlauber71 create sample file in the subfolder /data/ under this workflow named:"new_excel_multiple_sheets.xlsx" with one sheet per Cluster Membership Write and read simple Excel file "new_excel_file_1.xlsx" Import EXCEL - This workflow will act as a meta workflow trying to answer questions about importing data from Excel filesOne of the most frequently asked questions is the one how to import data from Excel files under various circumstances.At the right 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-knimeAlso 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 Excel Import Blocks - one common question is how to import blocks of data from excel sheetshttps://forum.knime.com/t/move-head-rows-to-a-column/45094/2?u=mlauber71 Excel – loop overwrite in the first loop https://forum.knime.com/t/group-loop-not-working-with-sheets-renaming/61194/2?u=mlauber71 Excel – allow changing file schema to import several fileshttps://forum.knime.com/t/excel-reader-node-doesnt-refresh-the-data-when-reset-and-executed-again/45672/6?u=mlauber71 KNIME and Excel — An Overview of What You Can Dohttps://medium.com/p/c6d400319745 Medium: KNIME, Paths and Loops — Automate Everythinghttps://medium.com/p/213455788685 sheet 1100 lineswith 4 groups of"Cluster Membership"knime://knime.workflow/data/excel_file_1.xlsxsheet1knime://knime.workflow/data/excel_file_1.xlsxsheet2knime://knime.workflow/data/excel_file_2.xlsxsheet3knime://knime.workflow/data/excel_file_1.xlsxsheet2new_excel_file_1.xlsxknime://knime.workflow/data/new_excel_file_1.xlsxloop byCluster MembershipCluster_<...>.xlsxxlsx_nameall .xlsx files from/data/collection/xlsx_namenew_excel_multiple_sheets.xlsxcreate path variables:https://forum.knime.com/t/write-multiple-csv-file-to-a-loop/44989/2?u=mlauber71loop byCluster MembershipSheets fromnew_excel_multiple_sheets.xlsx=> force the use of the NAMEfor sheetsnew_excel_multiple_sheets.xlsxwith 4 sheetstry to initially deletenew_excel_multiple_sheets.xlsxraed sheet namesfromnew_excel_multiple_sheets.xlsxvar_sheet_selectionStart to readsheetscollect read sheetsExcel Reader (XLS)(deprecated) Data Generator Excel Writer (XLS)(deprecated) Excel Sheet Appender(XLS) (deprecated) Excel Writer (XLS)(deprecated) Excel Sheet Appender(XLS) (deprecated) Excel Writer Excel Reader Group Loop Start Excel Writer Create File/FolderVariables Variable Loop End Excel Reader Create File/FolderVariables Variable Loop End Group Loop Start Excel Reader Excel Writer DeleteFiles/Folders Try (VariablePorts) Catch Errors(Var Ports) Merge Variables Read ExcelSheet Names Java EditVariable (simple) Table Row ToVariable Loop Start Loop End

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2020-08-04
On NodePit since: 2020-08-05
Last update: 2024-11-23
Created with KNIME version: v4.7.8
Tags: excelimportsheetRmicrosoftdataknimexlsmxlsxtypedatacolumnguessautomaticxlsbinsertcolumnheaderopenxlsxopenpyxl

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!