Upload Connector - Field with "%" in it coming over as scientific notation (E-)
Hi! So I have a bunch of spreadsheets that I'm loading into Domo using the Upload Connector. These are far from ideal and are manually populated by users but it's the world I'm living in right now.
The problem I ran into today is there is one set of fields where the field in Excel contains a value with a % symbol keyed into it (not just displaying in the formatting but actually stored in the field) Example "6.4%"
When I pull the data into Domo using the Upload Conenctor the field is coming over as "6.400000000000001E-2" which is subsequently causing it to read it as a text field in my ETL and throw an error when I try to convert it to decimal (this was previously working fine before I started pulling in this new field).
I have an idea of a sort of cumbersome way to solve for this in my ETL (see below) but wanted to check in here and see if anyone else has run into this and has a more elegant solution (or thinks this is really a defect that I should open for Domo to look at).
Here's my idea:
1. Split the field into two columns using the decimal as the delimiter
2. Use text operations to strip out all non-numeric values (including the E and -)
3. Combine columns using decimal as the separator
4. Set Column Type to decimal
Comments
-
Hi,
Ideally if you are loading the spreadsheets yourself, you could fix it in the spreadsheet before uploading it so that the feed is cleaner.
with the spreadsheets already in Domo in this case, you can also use the excel plugin which allow you to download the spreadsheet from Domo into your computer, make changes and re-feed to the same dataset.
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive