Uploading Excel - Data Type Issues

ecenteno
ecenteno Member
in Connectors

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 Employee
    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

  • Rich
    Rich Domo Employee
    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

     

  • kshah008
    kshah008 Contributor

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

  • rado98
    rado98 Contributor

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

  • PacoTaco
    PacoTaco Member

    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 Employee

    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 Member

    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.

  • Alyc120
    Alyc120 Member

    I worked around this by adding a CONCAT formula in Magic ETL. My File# needs to be 6 characters long. This worked for me.

    CASE
    WHEN LENGTH(File#) = 5 THEN CONCAT('0',File#)
    WHEN LENGTH(File#) = 4 THEN CONCAT('00',File#)
    WHEN LENGTH(File#) = 3 THEN CONCAT('000',File#)
    WHEN LENGTH(File#) = 2 THEN CONCAT('0000',File#)
    WHEN LENGTH(File#) = 1 THEN CONCAT('00000',File#)
    ELSE File#
    END

    I have no clue why we have the ability to change a column to be text, which then shows correctly and still converts back to integer when run.

  • carljose
    carljose Member

    Hi @Rich

    Seconding @Alyc120's point:

    "I have no clue why we have the ability to change a column to be text, which then shows correctly and still converts back to integer when run."

    This would therefore be a bug on the column's "Change data type" feature.

    Upload csv file with product ID column > change product ID column type to text > comes out as data type integer after dataset is processed.

Categories