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
  • what do you by it creates a DBF on strings instead of numbers? the columns? be very specific – ziggy Oct 4 '17 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 '17 at 14:14
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
  • 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 '17 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 '17 at 15:02

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.