Excel import is adding decimal places

Follow

Mike Coffey

• April 27, 2016 20:10

I'm importing data from an Excel spreadsheet into Datameer.  One of the columns contains part numbers, and since some PN's contain alphas, the field in the file import is defined as a STRING.  In Excel, the column is defined as GENERAL (although I've also tried it as TEXT with the same result).

When the import runs, some of the values that are numeric (e.g. part number 12345) have an added decimal after import (e.g. 12345.0).  It's not happening consistently, but it is happening frequently for those values.

I hesitate to use SUBSTITUTEALL because I can't rule out the possibility a part may come in with a valid .0 embedded in it.  I'd like to eliminate the addition of that decimal.

Anyone else run into something like this?

0

• 

  Gido

  • April 28, 2016 11:26

  I haven't observed an issue like the described one till now. To be able to setup a small test, can you share your used Datameer and Excel version? And how many records are in the column usually? 

  0

  Comment actions Permalink
• 

  Mike Coffey

  • April 28, 2016 13:36

  The column has a couple hundred values.  Excel version is 2013, Datameer is 5.4.2

  0

  Comment actions Permalink
• 

  Gido

  • April 29, 2016 13:59

  Thanks for sharing additional details.

  Meanwhile I've created a sample file and think I was able to reproduce the issue you are observing. 

  There was always a decimal point, and an integer number like 12345 was shown as 12345.0. The reason seems to be a Java .toString() method for conversion.

  To workaround this you may export the Excel spreadsheet to CSV instead of saving as XLS. 

  0

  Comment actions Permalink

Please sign in to leave a comment.