Icon

kn_​example_​excel_​new_​node_​list_​files_​sheets_​skip_​missing

This workflow demonstrates how to use the new Excel Reader and path variables and read Excel sheets and skip a step when a file is missing

This workflow demonstrates how to use the new Excel Reader and path variables and read Excel sheets and skip a step when a file is missing

There seems to be some questions about how to use the new Excel Reader together with the new path files. There are several ways to use them, namely the new Excel reader can just read all the sheets in a file or folder at once. But sometimes this is not what you want.
Please familiarize yourself with the new path concept since it is essential.

more fun with URI, URL and File Paths - work with the new Path variable format to list and move/copy files
https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_uri_url_path_more_fun_with?u=mlauber71

[kn_meta] Import EXCEL - a meta workflow trying to answer questions about importing data from Excel files
https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_import_meta_workflow?u=mlauber71


This workflow demonstrates how to use the new Excel Reader and path variables and read Excel sheets and skip a step when a file is missingThere seems to be some questions about how to use the new Excel Reader together with the new path files. There are several ways to use them, namely the new Excel reader can just read all the sheets in a file or folder at once. But sometimes this is not what you want.Please familiarize yourself with the new path concept since it is essential.more fun with URI, URL and File Paths - work with the new Path variable format to list and move/copy files https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_uri_url_path_more_fun_with?u=mlauber71[kn_meta] Import EXCEL - a meta workflow trying to answer questions about importing data from Excel files https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_import_meta_workflow?u=mlauber71 Inner loop try to load the Excel sheets and record them in individual tables and also store all results in a database - skip if it fails. Record the success or failure /data/ subfoldercreate 3 excel filesStart Trylocal_URIextract file name andextension fromPathlist XLSX files and give back URLsuse KNIME protocolextract meta informations fromExcel filesextract string frompath column"Location"read sheetvar_sheet_namevar_file_locationdelete a singel fileselect onefile randomly to bedeletedlist .table files and give back URLsuse KNIME protocolclearresultfolderSTARTouterloopSTARTinnerloopcatchwrite individualtablesvar_export_table_path=> constructindividual file pathvar_export_table_path=> convert to newpath variableENDinner loop../data/h2_databasePUBLIC.result_001reset the DB tablecollecting the resultsPUBLIC.result_001add results to a tablePUBLIC.result_001ENDouterloopsuccessreturn "YES";successreturn "NO";overview_of_processed_files.table=> store the overview of whichfiles have been processedYou could later use this table to process themissing ones (again) GenerateExcel Files Try (VariablePorts) String to URI URL to File Path List Files/Folders Files/FoldersMeta Info Path to String Excel Reader ConstantValue Column Read ExcelSheet Names ConstantValue Column Delete Files/Folders(Table) Partitioning List Files/Folders Delete Files/Folders(Table) Table Row ToVariable Loop Start Table Row ToVariable Loop Start Catch Errors(Var Ports) Merge Variables Table Writer Java EditVariable (simple) String to Path(Variable) Variable Loop End Variable toTable Row H2 Connector DB Table Remover DB Writer Merge Variables DB Table Selector DB Reader Loop End Java EditVariable (simple) Java EditVariable (simple) Table Writer This workflow demonstrates how to use the new Excel Reader and path variables and read Excel sheets and skip a step when a file is missingThere seems to be some questions about how to use the new Excel Reader together with the new path files. There are several ways to use them, namely the new Excel reader can just read all the sheets in a file or folder at once. But sometimes this is not what you want.Please familiarize yourself with the new path concept since it is essential.more fun with URI, URL and File Paths - work with the new Path variable format to list and move/copy files https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_uri_url_path_more_fun_with?u=mlauber71[kn_meta] Import EXCEL - a meta workflow trying to answer questions about importing data from Excel files https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_excel_import_meta_workflow?u=mlauber71 Inner loop try to load the Excel sheets and record them in individual tables and also store all results in a database - skip if it fails. Record the success or failure /data/ subfoldercreate 3 excel filesStart Trylocal_URIextract file name andextension fromPathlist XLSX files and give back URLsuse KNIME protocolextract meta informations fromExcel filesextract string frompath column"Location"read sheetvar_sheet_namevar_file_locationdelete a singel fileselect onefile randomly to bedeletedlist .table files and give back URLsuse KNIME protocolclearresultfolderSTARTouterloopSTARTinnerloopcatchwrite individualtablesvar_export_table_path=> constructindividual file pathvar_export_table_path=> convert to newpath variableENDinner loop../data/h2_databasePUBLIC.result_001reset the DB tablecollecting the resultsPUBLIC.result_001add results to a tablePUBLIC.result_001ENDouterloopsuccessreturn "YES";successreturn "NO";overview_of_processed_files.table=> store the overview of whichfiles have been processedYou could later use this table to process themissing ones (again) GenerateExcel Files Try (VariablePorts) String to URI URL to File Path List Files/Folders Files/FoldersMeta Info Path to String Excel Reader ConstantValue Column Read ExcelSheet Names ConstantValue Column Delete Files/Folders(Table) Partitioning List Files/Folders Delete Files/Folders(Table) Table Row ToVariable Loop Start Table Row ToVariable Loop Start Catch Errors(Var Ports) Merge Variables Table Writer Java EditVariable (simple) String to Path(Variable) Variable Loop End Variable toTable Row H2 Connector DB Table Remover DB Writer Merge Variables DB Table Selector DB Reader Loop End Java EditVariable (simple) Java EditVariable (simple) Table Writer

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-12-29
On NodePit since: 2021-10-07
Last update: 2024-04-26
Created with KNIME version: v4.7.8

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

Try NodePit Runner!