1

If I perform a join of an xls file to a shapefile and then save it as another shapefile, my numeric fields from the xls are displayed with many decimal ceroes.

Here is an example: The upper table is the result of saving the joined table (which is below).

enter image description here

When I check the type of field in the properties window of the original (joined) shape, it is a double - real- 0 length - 0 precision. In the new saved shapefile the type of field is double - real- 23 length - 15 precision.

How can I get the numbers with no decimals automatically?

  • what were the format of those columns when they lived in Excel? – DPSSpatial Mar 26 '18 at 16:31
  • They were numbers – Pescariz Mar 28 '18 at 18:34
1

I could reproduce this behavior using Excel2013.

Observations:

  • If a table is saved as an .xlsx (current Excel format), input integer value (31661) is kept as integer when drag & dropped into QGIS.
  • If a table is saved as an .xls (older Excel 97-2013 format), input integer value (31661) becomes a double (31661.000...) when drag & dropped into QGIS. (Confirmed Pescariz's OP).
  • If a table is saved as an .csv and read by Add vector layer they become text fields (31661).
  • Join operation keeps the inherited field type. Saving the layer as a new Shapefile also does not affect the type.

Workaround:

(Option 1) Save it as latest Excel format (.xlsx) before reading it to QGIS.

(Option 2) Read the .xls file same as before, then use the QGIS Refactor tool to change the type to Integer before Join.

(Option 3) Save .xls as a .csv and use Add Delimited Text Layer... it will try to read number field as number (unlike the Add vector layer).

  • I usually work with .xls file because is the format allowed for the tool "Open Excel as point table", very useful for me. – Pescariz Mar 28 '18 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.