Icon

KNIME_​Excel to TXT

KNIME_Excel Matrix to Text file

Demo workflow for taking Excel input in a matrix format and turning it into an output text file.


28 April 2021 @takbb Brian Bates (v2)

Step 1 - Convert from Matrix format to Tabulated format :ItemCode - Date - Value Step 2 - Determine what needs to be the generated output for each row Step 3 - Turn the generated output into separate lines in a data table to be usedfor writing the file Step 4 - Tidy up the rows (removal ofsuperflous final blank line) and thenwrite the file For ease of demo - and ease of reading - I make use of my "LocalFile Name Variables" component, which easily provides a variable fora file in the workflow's .\data folder. You can lose these and replacewith details of your own files directly in the Excel Reader node, andCSV Writer node Summary ....hmmm... mostly successful, except Ihaven't worked out how to persuade CSVwriterto throw a blank line NOTE!I had to resort to deprecated CSV writer toget it to output a blank line! SourceFile.xlsxNode 1We need to changedata from being a matrixto being based on rowsRemove "noise" createdby unpivotMake sensiblecolumn namesSort by ItemCode and DateConvert to trueDate to make it sortableGet a line numberwithin Item CodeGet Number of linesper item. This will be usedto determine if any givenline is the LAST line forthis itemPut Item countback into tableWe make use of line number within groupand whether this is 1st or last item for anitem to determinewhat we are outputting.If we want to output more than oneline, we separate with pipe |character to be interpreted later by cell splitterwrite... and have acoffee! :-)Output.txtKeep just the"line text"created by columnexpressions nodeSplit cells intoarraysby dividing at the pipe | charactercreated in ColumnExpressions nodeThe fun bit!Turn the arrays of lines into separaterowsRemove blank lineswhere there was an emptyarray element created by unpivotRemove "noise"columns createdby previous manipulationsTrim the additional space character includedthrown after each END (to force a blank line throw)so that it is just an empty stringwrite with blank linesOutput2.txtRemove thefinal "blank" rowGet row count Local FileName Variables Excel Reader Unpivoting Row Filter Column Rename Sorter String to Date&Time Rank GroupBy Joiner Column Expressions CSV Writer Local FileName Variables Column Filter Cell Splitter Unpivoting Row Filter Column Filter Rule Engine CSV Writer(deprecated) Local FileName Variables Rule-basedRow Filter Extract TableDimension Step 1 - Convert from Matrix format to Tabulated format :ItemCode - Date - Value Step 2 - Determine what needs to be the generated output for each row Step 3 - Turn the generated output into separate lines in a data table to be usedfor writing the file Step 4 - Tidy up the rows (removal ofsuperflous final blank line) and thenwrite the file For ease of demo - and ease of reading - I make use of my "LocalFile Name Variables" component, which easily provides a variable fora file in the workflow's .\data folder. You can lose these and replacewith details of your own files directly in the Excel Reader node, andCSV Writer node Summary ....hmmm... mostly successful, except Ihaven't worked out how to persuade CSVwriterto throw a blank line NOTE!I had to resort to deprecated CSV writer toget it to output a blank line! SourceFile.xlsxNode 1We need to changedata from being a matrixto being based on rowsRemove "noise" createdby unpivotMake sensiblecolumn namesSort by ItemCode and DateConvert to trueDate to make it sortableGet a line numberwithin Item CodeGet Number of linesper item. This will be usedto determine if any givenline is the LAST line forthis itemPut Item countback into tableWe make use of line number within groupand whether this is 1st or last item for anitem to determinewhat we are outputting.If we want to output more than oneline, we separate with pipe |character to be interpreted later by cell splitterwrite... and have acoffee! :-)Output.txtKeep just the"line text"created by columnexpressions nodeSplit cells intoarraysby dividing at the pipe | charactercreated in ColumnExpressions nodeThe fun bit!Turn the arrays of lines into separaterowsRemove blank lineswhere there was an emptyarray element created by unpivotRemove "noise"columns createdby previous manipulationsTrim the additional space character includedthrown after each END (to force a blank line throw)so that it is just an empty stringwrite with blank linesOutput2.txtRemove thefinal "blank" rowGet row countLocal FileName Variables Excel Reader Unpivoting Row Filter Column Rename Sorter String to Date&Time Rank GroupBy Joiner Column Expressions CSV Writer Local FileName Variables Column Filter Cell Splitter Unpivoting Row Filter Column Filter Rule Engine CSV Writer(deprecated) Local FileName Variables Rule-basedRow Filter Extract TableDimension

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: Brian Bates
Created at: 2021-04-28
On NodePit since: 2022-02-06
Last update: 2024-04-28
Created with KNIME version: v4.3.2
Tags: exceltext filetxt

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

Try NodePit Runner!