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

Options
HowDoIDomo
HowDoIDomo Contributor
in Datasets

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!

Best Answer

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓
    Options

    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.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • GrantSmith
    GrantSmith Coach
    Options

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • HowDoIDomo
    HowDoIDomo Contributor
    Options

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

  • GrantSmith
    GrantSmith Coach
    Options

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • HowDoIDomo
    HowDoIDomo Contributor
    Options

    XLSX :)

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓
    Options

    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.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • GrantSmith
    GrantSmith Coach
    Options

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories