Losing decimals on import from excel

[AkisM]

As the title says, I import an excel file (97-2003 .xls) into Alteryx, but all fields are read in as strings (this may be due to the fact that the excel I'm importing from has a custom formats for the fields). That would be fine, if it kept the decimals too, but it doesn't. All decimals are left behind from the import tool. I open the excel file and I see that the value of the cell in the formula bar actually has 2 decimals, even if the cell doesn't show them. I would've thought that when importing an excel sheet with Alteryx it takes the formula bar value (i.e. cell contents) rather than what the cell looks like visually.

Any ideas what could be causing this and how I can keep the decimals?

[afv2688]

Hello @AkisM,

I am not sure if I'm getting what you say, but when I do import excels with formulas in them I get the value of the cell, not the formula or anything else (taking xls input and custom values). Could you share a little portion or some dummy file where you are having the issue? That would help me better see the problem.

Regards

[Assaf_m]

I have tried the same scenario where you have a numeric field with text in some cells, Alteryx was reading only the visible value without the decimals. to fix it I had to remove any text values a keep only numeric value, after that, I was able to see the full decimals.

let me know if that would work for you.

[AkisM]

@afv2688 See attachment below. Notice how the number 497.860.724 under the "Zero rated sales" column has no decimals in alteryx, but does have them in the excel sheet.

@Assaf_m I'm not sure if that plays a role, but unfortunately even if it does, editing the original data is not possible. I need a way to fix it within alteryx.

[afv2688]

Hello @AkisM ,

The issue seems to be the file format. In xls the information taken is the one displayed on the cell and in xlsx the information taken is the one the cell actually has.

If you save the file as xlsx before working on it everything should go smoothly

Regards

[AkisM]

Thanks @afv2688 . Do you think there's any way this could be done within alteryx without re-saving the file? Maybe something like a file conversion tool in alteryx? I would like to avoid extra user steps if possible.

[danilang]

hi @AkisM 

Check out the answer in this post where you use excelcnv in a run command to convert your files before you read them into alteryx

Dan

[AkisM]

Hey @danilang , thanks for the link but I'm getting an access denied error. I think that post isn't available to all users.

[danilang · Accepted solution]

@AkisM 

Try it now.  The specific message you're looking for is from @MarqueeCrew 

Dan

[afv2688]

Clean and beatiful solution!