Hey guys!
I need your help!
How can I transform the cells, as an example Screenshot?
When there are two values in the cell, choose the second value, when there is one value, keep the first one.
Hi @aselau ,
It was a merge cell or you have 2 information at one cell? well, knime don’t work with merge cels, so you need to concatenate the string for one column as 0% + 100%, but another problem, breaklines won’t exists too.
It’s just for see or in the future you’ll make some calc with it?
BR,
Denis
Hello Denisfi.
The are two information in the cells (line break).
I need modify this cells, becasue the second value is the new.
So, the result need equal the image (TO).
You should upload a file or workflow that doesn’t have confidential info, but matches your exact input format. There are a lot of possible complicating factors depending on file reader settings, row spacing variations, possible anomalies and calculation grouping requirements that may or may not be needed in your use case. You are unlikely to get something easy to incorporate into your real task without more info.
Test_BD.xlsx (11.0 KB)
Hello Guys!
Hey guys! Thanks for the help on this problem.
I uploaded a file with information.
I’m confused. In the file you posted I can’t see any cells with two values.
After copying and pasting the invisible text from the Excel file, I believe that the problem is carriage returns. CR LF (0x0D 0x0A)
Hey guys
In the excel that I posted there are two scenarios, 1 is how the table is in the database, and 2 how I would like to leave it.
You can notice that in the first table, there are two values in a cell, and in this case I want to eliminate the first data that is inside the cell and keep only the second
See my above post. The problem is that the line break code in those cells are non-standard. They will have to be converted to supported characters before you can use the standard data manipulation approach to clean it up. Is this a one-off problem that you can fix in Excel before reading in the file, or will this problem persist in future files?
Hi,
If you need just to del the first information before the “space/breakline, carrier return…” use a simple string manipulation for it. With the string manipulation multi columns you can use a simple expression to it.
Example:
I’ve a table with some simple values and double values as the print below:
Then I use the string manipulation node to remove the first data until the space or breakline only.
\d+ => for digits sequence
\s+ => for a space type (space, tab, breakline…)
Result:
string manipulation.knwf (15.1 KB)
Can it solve for you?
BR,
Denis
1 Like
@denisfi The difficulty typically lies in the file reader. I tried various file type conversions / readers / settings with his uploaded file and couldn’t get a clean enough starting point to get a good result. It is a trial and error process for me, and I don’t have enough time to dial it in because of my workload. If you can get the data into KNIME cleanly like in your table creator, then it becomes easy.
@iCFO , Excel files won’t be so clean too… you can bring some merge cells, colored, formated cells and you need deals with it too… to bem mor clean, the original files could be works at the same thoughts… think like databases tables… you won’t have this situation to transform columns with double data as one.
Knime bring options that you can deals with some needs. I bring here a option to use correctly the data from the cell with 2 datas too… just 1 step to solve the problem, better then go back to excel files and edit directly there to solve the problem, don’t you think?
I hope that it helped you and the community, just a simple solution for the problem.
Thanks,
Denis
Definitely agree that a fix on the excel side is easiest if this is a 1 off problem. It could be cleaned up via Visual Basic as well. Typically I can eventually read it in cleanly if I try enough CSV conversions and reader settings.
Hello everyone,
Thank you Denisfi!
I tested Knime’s solution, but the cell still has the same value.
Can you put here your workflow without reset?
BR,
Denis
Hello Denisfi,
Thanks your help!
KNIME_project2.knwf (26.7 KB)
Hi @aselau ,
With the excel file, I could see that you have columns with missing value and because of it, you can make a simple calc. (columns: L, W, AO…)
So, in this case, you can use a missing node to put a fixed value as “0%” to solve this part of the problem.
Now you can use the “string manipulation multi columns node” to make changes at the strings.
For regex, I need to understand that you have numbers with and without decimals. For it, I make some changes into your expression:
\d+ for digitals
. for scape the point simbol
? to understand that can have or not decimals
\s+ for nay kind of space (tab, space, carrier return, newline…)
So, it bring only the final number if you have 2 at the same cell.
Result:
That’s It! follow your workflow changed.
KNIME_project2.knwf (51.4 KB)
BR,
Denis
2 Likes
Hello Denisfi!
Thanks for helping me!
your solution works very well!
Do you indicate any website to study the regex?
Nice one @denisfi !
It looks like you got a clean data import right out of the standard Excel Reader settings. I never got that clean of a file read, so I must have jumped straight into alternate file reader approaches… 
You have a lot of websites about this theme.
Well, and us from community! lol
Thanks,
Denis