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?

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