1

I need to convert a .xls file to a point shapefile. The Excel file contains points in Lat, Lon with many attribute fields. I wrote this script to convert from .xls to .dbf and then to shapefile but it creates a .dbf of strings instead of numbers.

How can I fix this?

enter image description here

import os
import glob
import arcpy
from arcpy import env
arcpy.env.overwriteOutput = True

env.workspace = r"E:\test"
os.chdir(r"E:\test")

xlslist = arcpy.ListFiles("*.xls")
for xlsfile in xlslist:
    InFile = xlsfile
    outFile = os.path.splitext(InFile)[0]+".dbf"
    arcpy.ExcelToTable_conversion(InFile, outFile, Sheet="Sheet")

    outlayer = outFile
    outlayer = os.path.splitext(InFile)[0]+"_Layer"
    spatialreference = "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]];-400 -400 1000000000;-100000 10000;-100000 10000;8.98315284119522E-09;0.001;0.001;IsHighPrecision"
         arcpy.MakeXYEventLayer_management(outFile,"Longitude","Latitude",outlayer,spatialreference,"#")

shpfile = os.path.splitext(xlsfile)[0]        
arcpy.CopyFeatures_management(outlayer,shpfile)
del outlayer
Improve this question
2
  • what do you by it creates a DBF on strings instead of numbers? the columns? be very specific
    – ziggy
    Oct 4, 2017 at 16:09
  • As suggested by Hornbydd below, one problem was in the .xls file. The cells were of type text instead of number. I fixed that but still the code has a problem. It stops at CpyFeatures_management with ERROR000210: cannot copy feature. Any idea?
    – F. Cian
    Oct 5, 2017 at 14:14

1 Answer 1

Reset to default
1

If you read the help file for this tool it clearly states under the Usage section:

The output field data type is based on the values and cell formatting found within the input column. Output field data types include float, text, and date. If an input column contains more than one type of data or formatting, the output field will be of type text.

So you need to format the columns as numbers in Excel and ensure you don't have random characters like ? or n/a in it.

In Excel the green triangles are telling you something about the formatting, don't ignore them!

If you format them correctly you should not need to export to dbf, create the XY layer directly from Excel. Faster and smarter.

Improve this answer
2
  • Thanks for your help. Fixing the cell type in excel solved partly the problem. How would you convert from excel to shape directly? With the same steps I followed from dbf to shp? Thanks a lot!
    – F. Cian
    Oct 5, 2017 at 14:15
  • Reference the worksheet directly using the Make XY layer tool, this should create your temporary IN MEMORY layer which you then export out with the copy features tool.
    – Hornbydd
    Oct 5, 2017 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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