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
-
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:
I hope this helps. Let us know if the suggested workaround won't work for your data.
Thanks
1
Answers
-
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:
I hope this helps. Let us know if the suggested workaround won't work for your data.
Thanks
1 -
Uploading the spreasheet using the workbench will allow you set the column type to text at source level.
1 -
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
0 -
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
0 -
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.
0 -
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#
)
ELSEFile#
ENDI 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.
1 -
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.0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive