Split Data in DAT/txt file into Excel columns

[mmngeoh]

Hi All,

I am KNIME beginner and i want to split the data in DAT file to become columns. The raw data looks like this.

Anyone knows how to remove the first and last lines and split into columns like this:

Would be much appreciated if i could get some guidance

KNIME Testing.txt (1.8 KB)

[badger101]

Hi @mmngeoh , it’s better to share a workflow than a file, in this case, because we don’t know which node you used to open the file with, and from there, how did the output table appear in Knime - is it a multiline single cell? Is it exactly like the Excel screenshot you showed? Without a workflow, it’s not possible to assist you further.

[mmngeoh]

Hi @badger101,
I use file reader to read the DAT file and it looks like this:

There is no workflow yet as i could not work further on DAT file until it splits into columns
KNIME Discussion.knwf (5.9 KB)

[badger101]

@mmngeoh , I managed to restructure your table to something workable. But please note that this relies upon the fact that most of the columns are separated by at least 2 spaces. I use that info for my delimiter decision during file reading. If your new .dat files in the future doesn’t fit this rule, my method won’t work on them.

Anyways, here are my attempts:

One Way to Deal with DAT Files – badger101

I deliberately leave out the row filtering task to you, since it’s a basic task. You just need to identify which is the first row and which is the last in order to filter them. It can be done in many ways, all of which are simple enough for you to proceed on your own

[mmngeoh]

Hi @badger101 ,

Thanks for your help