0 was unexpectedly added

[mysteri_guy]

HI Team,
I’m facing a strange case where 0 was unexpectedly added into data after running CSV reader (same to excel reader or file reader after a few runs)
it didn’t happened to all but only a small portion.
I was trying to create unique Key for lookup and this caused a small group to create wrong key and failed in looking up data.
I can’t simply replace 0 for something else since some of other row also have 0 and need to be used separately

I’m really hoping for your advice on this.

This is the number from Raw data as 12 has no 0

this is the result after executed in CSV reader. 0 was added.

[MartinDDDD]

Hey there,

is it always the first character digit or are there cases where it is added elsewhere? Is it always only one “0” or can it be multiple?

If it is always the first character and only one “0” you can remove it using column expressions:

if(left(column("column1"),1) == "0") {
    substr(column("column1"),1)
} else {
    column("column1")
}

Alternatively you can do it with the new Expression Node:

if(starts_with($["column1"], "0"), substring($["column1"], 2, length($["column1"]) - 1), $["column1"])

With Expression I used K-AI with the following prompt:

Output:

Example WF:

removeleading0.knwf (75.5 KB)

[rfeigel]

Kudos to @MartinDDDD for the solution. However, that begs the question of why you had this problem. I tried to reproduce it with strings and integers and had no problem. Is there something unusual about your data?
CSV Reader Adds Leading 0.knwf (84.1 KB)

[takbb]

My suspicion would be that KNIME is behaving correctly and that the csv file contains leading zeros.

Excel will drop leading zeros even if they are in the original data, unless it is explicitly contained within quotes in the csv (in which case it will treat them as a string).

If the csv were opened in a text editor such as notepad, I believe that the leading zeros would be present. If I’m right, KNIME’s behaviour here is preferable because it is not corrupting the data and the user has the option of keeping our removing the leading zeros such as in a manner @MartinDDDD proposed. Excel gives no such option and simply corrupts the data.