Hi all,
how can I import/read files that don't have column delimiters but are fixed length?
Reinhold
Currently, KNIME does not offer this functionality. To overcome this issue, you need to manually manipulate your file to provide a column delimiter to the File Reader. Another workaround would be to read all the data into one big column and then afterwards use a number of Java Snippet nodes to split the column again. But this is of course not applicable when the number of columns is large. Anyway, thanks for this advice which we put to our list of improvements.
I believe this is one of the most used modes for reading file perhaps next only to a comma delimited file. I would like to see this in Knime. Remeber most of the older mainframe systems even support variable file lengths with no delimiters. Probably this would be an extension to the fixed width reader. Most of the raw data resides in mainframes even today.
Acutally you can upload the file in one column using the file reader node and then use the "cell splitter by position" node to split it.
This node is really messy and can take a bit to get right but it does work.
If your adding this to the list of improvments you might want to mirror how SAS Enterprise guide handles this. It displays the file that has been loaded and uses a ruler at the top of the page which allows the user to click on where they need the column splits to be. The next screen then allows you to name the columns and assign data types and formats.
When I need to use a fixed with file in Knime I use sas to load and format the data and then save the output as a csv. I then read this file back into knime.
That is a good example. An elegant way is offered by ETL vendors like IBM Datastage. Their file reader can read the file based on the "Schema" you have specified at the design time. You have an output table for the output port where one can specify the column names and their lengths in the order of how they appear in the record.The reader will scan the schema sequentially and assigns the columns based on the specified name and lengh. Sounds very simple and straight forward, isnt it?