ArcPy Feature Class to Feature Class blank results - ArcGIS Pro

Question

I have this Feature Class to Feature Class script that creates a feature class of patient visits for each store for every month for every year it has been open. However, when I run the script it will create feature classes by month and year with the correct labels, for example (Jan2015_NWPatientVisits2000Gresham), but when I look at the attribute table it contains no data.

I'm sure I am missing something small, but I just can't seem to figure out what it is.

Here is my script:

import arcpy

# Set environment settings
arcpy.env.workspace = r"C:\arcGIS_Shared\Python\CenterHeatMaps.gdb"

#Declare variables
fc = 'Open_Store_Centers'
fields = ['USER_market_id','USER_Store_ID','USER_Store_Center_Name']
fieldname = 'USER_market_id'

#Define WHERE clause statement
whereclause = """{} = 2000""".format(arcpy.AddFieldDelimiters(fc, fieldname))
sqlclause = (None, 'Order By USER_market_id, USER_Store_ID')

# loop through months
years = [2015, 2016, 2017]
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']

for year in years:
    for month in months:
        with arcpy.da.SearchCursor(in_table = fc, field_names = fields, where_clause=whereclause, sql_clause=(None, 'ORDER BY USER_market_id, USER_Store_ID')) as cursor:
        #Loop through each row established in cursor
            for row in (cursor):
            # Set local variables for FeatureClasstoFeatureClass
                inFeatures = "PatientVisitsGeocoded"
                outLocation = r"C:\arcGIS_Shared\Python\CenterHeatMaps.gdb"
                outFeatureClass = "{2}{3}_NWPatientVisits{0}{1}".format(row[0], row[2], month, year)
                delimitedfield = arcpy.AddFieldDelimiters(arcpy.env.workspace,"USER_CenterID")
                expression = delimitedfield + "= {0}".format(row[1]) + " AND USER_DOSYear = {0}".format(year) + " AND USER_DOSMonth = '{0}'".format(month) 

#         Execute FeatureClassToFeatureClass
                arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass, expression)

        #Print Results
                print(row[2])
                count = arcpy.GetMessageCount()
                print (arcpy.GetMessage(count-1))

Answer 1

I suspect it has something to do with your expression. Try removing it from arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass, expression) to test if you get an output. That will help narrow down the problem. If you do get output then the problem is related to your expression. If you don't then there's something else causing the lack of output.

Your expression line is a bit messy:

expression = delimitedfield + "= {0}".format(row[1]) + " AND USER_DOSYear = {0}".format(year) + " AND USER_DOSMonth = '{0}'".format(month) 

Instead of concatenating a number of strings together, why not put them all into a single string using String formatting (as you've already partially done for each)?

expression = "{0} = {1} AND USER_DOSYear = {2} AND USER_DOSMonth = {3}".format(delimitedfield, row[1], year, month)

If removing/changing the expression doesn't make any difference I would look next at your whereclause in the Search Cursor.

---

On second look at your code, I noticed you've used the AddFieldDelimiters() twice. Once refers to your fc, the other refers to your arcpy.env.workspace. Try pointing both to the same to see if that changes anything.

---

Your comment that Year is LONG and Month is TEXT has hinted at a possible cause. Try putting quote marks around the month value in your expression:

expression = """{0} = {1} AND USER_DOSYear = {2} AND USER_DOSMonth ='{3}' """.format(delimitedfield, row[1], year, month)

Answer 2

I honestly don't even know what I did to fix this. As I said in the comments "USER_Store_ID" and "USER_CenterID" contained the same data but one was text and one was numeric, so I added a field and copied the numeric field (CenterID) to the new field but converted it to text and name the new field "USER_StoreID". I thought this would fix the problem but it didn't. Then I just rearranged the order of DOSMonth and DOSYear in my expression and put the numeric field (CenterID) back where it was and it ran correctly. So once again not really sure what I did, but it works.

Here is my final code:

import arcpy
import datetime 
from datetime import timedelta
import time

#Document Start Time in-order to calculate Run Time
time1 = time.clock()

# Set environment settings
arcpy.env.workspace = r"C:\arcGIS_Shared\Python\CenterHeatMaps.gdb"

#Declare variables
fc = 'Open_Store_Centers'
fields = ['USER_market_id','USER_Store_ID','USER_Store_Center_Name', 'USER_Opening_Date']
fieldname = 'USER_market_id'

#Define WHERE clause statement
whereclause = """{} = 2000""".format(arcpy.AddFieldDelimiters(fc, fieldname))
sqlclause = (None, 'Order By USER_market_id, USER_Store_ID')

# loop through months
years = [2014, 2015, 2016, 2017]
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']

for year in years:
    for month in months:
        with arcpy.da.SearchCursor(in_table = fc, field_names = fields, where_clause=whereclause, sql_clause=(None, 'ORDER BY USER_market_id, USER_Store_ID')) as cursor:
            #Loop through each row established in cursor
            for row in (cursor):
                # Set local variables for FeatureClasstoFeatureClass
                inFeatures = "PatientVisitsGeocoded"
                outLocation = r"C:\arcGIS_Shared\Python\CenterHeatMaps.gdb"
                outFeatureClass = "{2}{3}_NWPatientVisits{0}{1}".format(row[0], row[2], month, year)
                delimitedfield = arcpy.AddFieldDelimiters(arcpy.env.workspace,"USER_CenterID")

                expression = """{0} = {1} AND USER_DOSMonth = '{2}' AND USER_DOSYear = {3}""".format(delimitedfield, row[1], month, year)


#               Execute FeatureClassToFeatureClass
                arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass, expression)

                #Print Results
                print(row[2])
                count = arcpy.GetMessageCount()
                print (arcpy.GetMessage(count-1)) 

#Document End Time
time2 = time.clock()

#Run Time in seconds
runtime = (time2-time1)

print (str(timedelta(seconds=runtime)))