Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
Replies
12 replies
Subscribers
7 subscribers
Views
10157 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
AI and Rules
Parsing data from an Excel file
angadc
Certified Lead Developer
over 7 years ago
When using readexcelsheet() to parse data from an excel spreadsheet we're experiencing a strange issue. We notice that ".0" is automatically added to text that is numeric. For example, "2290" is parsed as "2290.0" in Appian.
The data is copy pasted as values. The formatting of the cell is set to Text.
Has anyone experienced this issue? If so, do you have a workaround?
OriginalPostID-270062
Discussion posts and replies are publicly visible
0
angadc
Certified Lead Developer
over 7 years ago
To add to the above, copy/pasting the data into notepad and then into excel is one workaround but this is not feasible to the end users.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
martinc8721
over 7 years ago
you could use the function toInteger() in order to display your data
load(
local!numbersFromExcel_txt: {
"2290.0",
"101.0"
},
apply(
fn!tointeger(
_
),
local!numbersFromExcel_txt
)
)
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
angadc
Certified Lead Developer
over 7 years ago
Thanks Martin. Unfortunately some of the cells have non-numeric values too.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
martinc8721
over 7 years ago
ooh!, and what if you set the excel cells format as general ?
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
angadc
Certified Lead Developer
over 7 years ago
No luck with that either. Tried cell format as general, text, custom, number.
Try readexcelsheet(ri!attachedDoc, 0,2,1) for the attached doc to recreate the issue. You'll notice that 222960 is parsed as 222960.0. If you manually click on the cell before parsing, it addresses the issue, but that isn't quite a feasible approach.
AppianSupport_Decimal.xlsx
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
martinc8721
over 7 years ago
you can use the following code in order to remove the ".0" from the values
load(
local!excelValues: readexcelsheet(
ri!attachedDoc,
0,
2,
0
).result.values,
apply(
substitute(
_,
".0",
""
),
local!excelValues
)
)
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
martinc8721
over 7 years ago
or you could try adding a blank space after the number, it works for me as well
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
angadc
Certified Lead Developer
over 7 years ago
Thanks for the suggestions. Unfortunately some cells have legit ".0" so we can't substitute them. Also, adding a blank space after the number is not quite an option either since this data is coming from another system.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
martinc8721
over 7 years ago
maybe trying another plugin? thats my last option, sorry I was not helpful
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
angadc
Certified Lead Developer
over 7 years ago
Thanks for offering suggestions.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
>