Heatmap script gives ERROR 000358 from SQL expression using ArcPy with ArcMap

Question

I have a heatmap script that will eventually print 10 PDF maps. At the moment it is failing at the SQL expression in the Make Feature Layer right before Kernel Density Raster creation because the column datatype is string. I'm having trouble changing it to an integer in the loop.

Traceback (most recent call last): File "\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\test.py", line 58, in int(stringA) ValueError: invalid literal for int() with base 10: '%d'

Here is the part of the script that is failing:

arcpy.CheckOutExtension('Spatial')
#use 'CURRENT' if running from arcmap, when published use MXD on disk
mxd = arcpy.mapping.MapDocument(workspace+"\\working_environment.mxd")
df = arcpy.mapping.ListDataFrames(mxd)[0] # address location data frame and turn on select layers
df.zoomToSelectedFeatures()



arcpy.env.extent = df.extent #set extent to current one
arcpy.env.overwriteOutput = True #overwrite existing files

my_point_features = autoLayerGDB +"\\coded_xy_points_FIPS4203"
scratch_output = workspace+"scratch"
    

#Select each district via script
    
   for i in range(1, 11):
    stringA = '%d'
    int(stringA)
    my_clause = 'COUNCILDISTRICT =' + stringA % i
    my_filename = "kden_d%d" % i
    arcpy.MakeFeatureLayer_management(my_point_features, 'templyr', where_clause=my_clause,)
    outKDens = KernelDensity("templyr", "None", cell_size=100)
    outKDens.save(autoLayerGDB+"\\"+my_filename)
    print("\tRaster created for District %d" % i)
    

I also tried this:

for i in range(1, 11):
    my_clause = 'COUNCILDISTRICT = "%d"' % i
    my_filename = "kden_d%d" % i
    arcpy.MakeFeatureLayer_management(my_point_features, 'templyr', where_clause=my_clause,)
    outKDens = KernelDensity("templyr", "None", cell_size=100)
    outKDens.save(autoLayerGDB+"\\"+my_filename)
    print("\tRaster created for District %d" % i)

But received this error message:

raceback (most recent call last): File "\coacd.org\dfs\SWS\Code Enforcement\ACD Admin Operations\GIS\Operations\Council District Analysis Reporting\Auto_HeatMap_WIP\test.py", line 59, in arcpy.MakeFeatureLayer_management(my_point_features, 'templyr', where_clause=my_clause,) File "C:\Program Files (x86)\ArcGIS\Desktop10.5\ArcPy\arcpy\management.py", line 6997, in MakeFeatureLayer raise e arcgisscripting.ExecuteError: ERROR 000358: Invalid expression COUNCILDISTRICT = "1" Failed to execute (MakeFeatureLayer).

I found gis.stackexchange.com/questions/137517/… and docs.microsoft.com/en-us/sql/odbc/microsoft/…. Based on this information I changed the schema file to interpret the COUNCILDISTRICT Collumn as "short" instead of "text" and it worked

Answer 1

I found the links, Cannot convert string to int using ArcPy Calculate Field? and https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-2017. Based on this information I changed the schema file to interpret the COUNCILDISTRICT Collumn as "short" instead of "text" and it worked

1. Find the schema.ini file that is next to the .csv file.

2. Open the file and check what it says:

   [SOURCE_DATA.csv] Col14=COUNCILDISTRICT text

3. Change the "text" to "short" so that it reads:

   [SOURCE_DATA.csv] Col14=COUNCILDISTRICT Short

4. Save the file and run the script.

And now it works.