One column have multi data, how to convert

KNIME Analytics Platform
1

Hello,
I have a raw data as below pic , how to convert to expected format ,thanks.
Knime Data.xlsx (9.9 KB)

image
image966Ă—425 10.7 KB

2

Hi @ycbing77 , thank your for including sample input and output data as a spreadsheet.

I have a few questions.

Firstly, can we assume that there will only ever be at most two items in any one cell that is to be split or can there be more?

Secondly, the cells containing 124,232 and 456,479 are actually numeric on your sample data and so those commas don’t really exist in the data (they are just an artefact of the “thousands” format mask when displayed in excel). As such, they would actually be seen as two numbers (124232 and 456479). Can we assume that in reality they would be presented as text data in Excel and the commas would therefore be present when imported into KNIME?

My final comment is that within KNIME tables you cannot have column names which are blank or repeated. Are you looking for additional ideas on how to make the column headings exactly as you show them when exporting back to an excel file or can you live with slightly different output column headings to the ones you are showing?
Thanks.

1 Like
3

Thanks for your replying, I will define the rule for operator to input or key data, should be only use “/” to separate , cannot use “,”.

4

What about the cells with numbers separated by space?

1 Like
5

Hi @ycbing77, did you have an answer to my other question, as the approach taken may well depend on the answer to that.

Then whilst commas clearly present a very specific problem in Excel, as per @rfeigel 's question, are there a variety of other delimiters that may be present?

6

@ycbing77
Here’s a workflow that produces your desired output. I’m curious why the input data was out in the middle of a worksheet with no headers. I have not done extensive testing with data formatted differently so this workflow may not work properly with arbitrarily formatted data. If this data is manually input, why not have the data entry person(s) just input correctly?

7

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