Keeping 0s in front of an integer (ID numbers)

HowDoIDomo

Hi, everyone! I have a file that has ID numbers about half of which have 0s in front. When I upload the dataset through workbench, it drops the zeros even when I have it set to integer.

I am trying to merge this data set to another using the ID numbers as a matching column. But, one dataset kept the 0s while the other dropped it.

Does anyone have any ideas on how I can preserve those 0s in the front? Thanks!

DavidChurchman

I'm not experienced with workbench, so don't know how to fix the upload issue, but you could always add the leading zeros back in with LPAD() when you're joining it to the other file.

GrantSmith

On the schema tab in your workbench job, set your ID field to be a string instead of a number. This should keep it as a string with the leading 0s.

HowDoIDomo

Hi Grant, I set it as a string in the schema, but the leading 0s are still dropped.

GrantSmith

What type of file are you importing from? CSV? XLSX?

HowDoIDomo

XLSX :)

DavidChurchman

I'm not experienced with workbench, so don't know how to fix the upload issue, but you could always add the leading zeros back in with LPAD() when you're joining it to the other file.

GrantSmith

Are the values explicitly formatted as text in your Excel document?

As a workaround, you can do as @DavidChurchman mentioned and just LPDD them in a MagicETL after it's been ingested.