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!

Find more posts tagged with

Accepted answers

Rich

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

All comments

Rich

Hi ecenteno,

leading zeros in magic

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

Thanks

kshah008

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

rado98

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

PacoTaco

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

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

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

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

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.

andyhicken

I had a similar issue - I had an Excel column with mixed data types (strings and ints); Domo decided that the column should be an integer and dropped all the strings from the column - even after I explicitly told Domo to make it a string column. The workaround for me has been to save the Excel spreadsheet as a CSV, and import it as a CSV.

art_in_sky

@andyhicken Apart from using text classifier, below one could also help

"Under Admin > Toolkit, there's a scheme management capability to modify a dataset's field types. But I use it as a last resort; when I can't seem to get Domo to accept my field types on import."