Uploading Excel - Data Type Issues

When uploading an Excel document, where all of the data in the columns are formatted as "text", DOMO will change the data type to number, every time I upload it. 

 

I know there is an option to change the data type using MAGIC ETL, but here is my problem. In our data we have account numbers with preceeding zeros, for example 00123654789. When I upload it and the data type is changed to number, the preceeding 0's in my data are removed and I'm not able to link my spreadsheet with my other data sets. When I use MAGIC ETL to change it to text, it changes the data to text from the number format so the end result is the same (preceeding zero's not available). 

 

I'm sure I'm doing something wrong, please help!

Best Answer

  • Rich
    Rich

    domo

    💎

    Accepted Answer

    Hi ecenteno,

     

    Mixed news here. I've duplicated the trouble you're experiencing with leading zeros from an excel sheet - even when Excel has them formatted as TEXT. Domo does interpret them as whole numbers and strips the leading zeros at import. That means using Magic ETL is unable to restore those zeros natively.

     

    Here's the workaround I was able to work successfully, but it includes another step within Excel that may not work for you. If this suggestion doesn't work, we can migrate your question to a feature request and get some developers eyes on it.

     

    Workaround: If you add a text qualifier in front of the zeros in your excel sheet Domo won't strip the zeros. You can then safely strip the zeros within Magic ETL with a simple replace function. It looks like this:

     

    leading zeros in magic

     

    I hope this helps. Let us know if the suggested workaround won't work for your data.

     

    Thanks

     

Answers

  • @ecenteno, did Rich's reply help solve your problems?

  • Uploading the spreasheet using the workbench will allow you set the column type to text at source level.

  • PacoTaco
    PacoTaco Roswell, GA 🟡

    Hi @Rich !


    Was this ever submitted as a feature request?  If not, I'd like to have it submitted.

     

    Your ETL workaround will not work for our process and the Workbench workaround mentioned won't work b/c it prevents our end users from directly updating the dataset with a new Excel file easily/directly within the DOMO interface.

     

    Thanks!

    --Nick

  • Rich
    Rich

    domo

    💎

    Hi PacoTaco,

     

    Thanks for your post - it has been a long time since I last looked at this one. Since that time, our feedback process has changed a bit. Please submit your feedback about the leading zeros in one of these two ways:

     

    1. At the top of your Domo experience you'll see a menu button with a "Feedback" option. Follow those prompts to fill out a paragraph with the request.

     

    2. Submit a support ticket making it clear that you're looking for a feature improvement / request. That will then route to the same place as option 1 above.

     

    Thanks,

    Rich

  • PacoTaco
    PacoTaco Roswell, GA 🟡

    Thanks Rich, will do!

     

    FYI, I noticed the email connector includes an option to keep leading zeros, just not availble in the Excel/File Upload connector.