Split data with multiple space separators / based on a specific number of characters

Hi,

I have a file I want to import the date from, but the column splitting method of the “File Reader” node does not work the way I want it.

Here is a sample of formatted data (downloadable with the link below):


data.txt (1.3 KB)

All the data are written based on a specific number of characters and spaces as displayed below.

I am looking to find two different ways to separate the columns :

  • Split the columns based on the number of characters (1st column : first 13 characters, 2nd column : following 14 characters, 3rd column : following 14 characters, …)
  • All the columns are separated by at least 2 space character, and there is no blank value so I would like to split by column all the values that are separated by at least 2 space character.

I thought I would find plenty of similar topics on this forum as it corresponds to the first step of data processing but I could not find any of these solutions, maybe they are too basic for everyone :cry: .

When I import the data with a comma as a column separator, this is what I obtain (columns are not split) :
image

But when I use a (or multiple) space as a separator, I obtain a large number of empty columns, so I don’t think this is the solution :

Maybe “regex split” node can be helpful, I am exploring it.

Does anyone have ideas for the two ways to split the column data ?

Thank you.

Hi @yilog78096 Welcome to KNIME Forum

I found a way to split your data into columns. KNIME_project.knwf (17.8 KB)
My first step was to adjust the column name in the csv reader node (looks better).
In the String Manipulation node I used;

removeDuplicates($smaller_column_name$)

Replaces all occurrences of two or more spaces with a single space character.

The the final step is to split the column on the whitespace.
image

gr. Hans

4 Likes

Thanks @HansS, it works really well.

Do you have an idea on how to split the columns based on the number of characters in each of them ?

Hi @yilog78096

Take a look at

Best Regards.

2 Likes

Thank you for these answer, it worked well.

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.