ArcPy Feature Class to Feature Class blank results - ArcGIS Pro

674
3
Jump to solution
01-23-2018 06:57 AM
Business_IntelligenceSoftware
Occasional Contributor

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 believe the problem is in my "expression" because when I alter it to run on only "USER_DOSYear" or "USER_DOSMonth" it will populate, but when I try and string them together that is when the problem occurs.

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))

Any help/advice would be much appreciated. Thanks.

0 Kudos
1 Solution

Accepted Solutions
Business_IntelligenceSoftware
Occasional Contributor

I honestly don't really even now what I did to fix this, 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))) 

View solution in original post

0 Kudos
3 Replies
JoshuaBixby
MVP Esteemed Contributor

To get the SQL syntax sorted out, what about trying a couple or few expressions manually in ArcMap GUI.  Without have a bit more information about the data itself, it is hard for me to provide any specific suggestions. 

0 Kudos
deleted-user-qzyyiYme1rz9
New Contributor III

My suggestion would be similar to Joshua Bixby, but first I'd put a print statement to print your expression clause so you can see what it looks like. When the expression is printed it may be obvious where the error is, if not, try to copy/paste that manually in ArcMap and make changes in ArcMap until you have the correct syntax for the clause, then work backwards and adjust your Python script to match. 

0 Kudos
Business_IntelligenceSoftware
Occasional Contributor

I honestly don't really even now what I did to fix this, 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))) 
0 Kudos