Fix leading zero troubles in Excel

Question
1

Hi,

I need to merge tweo Excel files. But one has values with leading zeros entered as numbers with the leading dot-notation, basically making the cell some sort of special text value.
The other file has the lookup field set to text and no special notation is needed for the leading zeros.

using the lookup transform, EDT compares “.008001” to “008001” and does not match.

I could easily remove the leading dot, but what is going to happen to the column when I output that file to an Excel? How can I change the format of a column in Excel?

Any other suggestion how to solve the problem?

Thanks, best Jochen

2

You can explicitly set the Excel format for each column when outputting to Excel:

image
image435×633 91.1 KB

3

Aha! Cool. Thank you

1 Like
4

Hi @joker

Make a copy of the column that has dot in its value and then remove the dot from the copied column and use this column for the lookup and then remove this copied column before the final output. this way your original dot column stays intact.

image

Transform file.
FixLeadingZero.transform (3.7 KB)