Excel import improperly converting decimal to percentage

0
I have an entity full of decimal attributes. In order to discard any accidental text from a spreadsheet or “N/A” or stuff like that I created a temporary entity just for importing and all the attributes are strings. There is a microflow that handles each field in the import that tries to convert values to a decimal and if they fail, return a 0.    I’m importing a spreadsheet that has values in decimal format (all less than 1) and all the decimal values are actually formulas that refer to different sheets. I checked to make sure all the source values are formatted properly and they are. I have one value A that’s 0.1300 and another value B that’s 0.0628. These are just plain numbers with 4 decimals. They;’re formatted as numbers with 4 decimals in Excel.  The fields that I’m importing from that refer to these source fields look fine as well: Value A shows 0.1300 and B shows 0.0628. Again, both the fields I’m importing from and the source fields are numbers.. formatted as numbers. All less than 1.    When I import them, they import as 0.1300 and 6.28 respectively. Keep in mind these are “string” values since that’s what my temporary entity’s attributes are. When i debug on the microflow that processes these values directly from the spreadsheet, I can see the strings that are coming in directly from the parameters indeed show “0.1300” and “6.28”. I don’t know if the problem lies in Excel or in Mendix’ excel importer, but it looks like either of the two is confused, thinking one of these values is a percentage, when it isn’t. Does anyone know why this might be happening>?   Does anyone know why this would happen when the excel values are clearly both formatted as numbers, both show the proper decimal format  (0.1300 and 0.0628).. there is NO percentage formatting anywhere AT ALL. There is nothing at all done inside excel to make Excel think this is a percentage.
asked
1 answers
0

Brian

I don’t know the answer to that off the top of my head.  However, one thing I have tried in the past that has corrected things like this is to create another sheet in the workbook to be used for importing, select all of the rows and columns in the sheet you are using (with the formulas) and paste into your new sheet, but use Paste Values in Excel.  After doing that, try importing again with the ‘values only’ spreadsheet. 

 

The other thing you could try is to create a simple microflow to parse the decimal values when you import the spreadsheet.  In that microflow, explicitly convert the decimal values to strings with the formatDecimal() function.  Use this microflow in the parseWith for the correct columns in your spreadsheet.  Seems like maybe you have an implicit conversion of some kind taking place, and this will make that conversion implicit.

 

Hope one of those ideas helps.

Mike

answered