Select Tool: Changing data type on xls leads to truncated figures

Question

hi there, i've been importing xls files and changing the fields from V_string to double and realized the numbers get truncated specifically at the "," and loses decimal places.

eg 20,888.90 becomes 20 and 1000.4032 becomes 1000.40

I repeated the same with xlsx folders and it works fine -20888.9 and 1000.4032 shows.

Is this a limitation working in xls?

Appreciate any workarounds.

I've attached screenshots and the working files.

Screenshot 2021-05-20 at 7.06.06 PM.png

Data Sample.xls

Data Sample.xlsx

Screenshot 2021-05-20 at 7.48.08 PM.png

domlow · Answer

Thanks @Jonathan-Sherman

I tried that, but the data is still not showing in full, ie 1000.4032 is only showing as 1000.4 from the xls file

Any way to show the full values?

Jonathan-Sherman · Answer

Hi @domlow,

You'll want to remove the commas before converting to numeric data types when using xls files. If you've got multiple fields to replace the commas from you could use a multi-field tool! 🙂

Once you've removed the commas we can then change the data type as normal.

I've attached my workflow for you to download if needed.

Kind regards,

Jonathan

20210520 - CommasInInputs.yxmd