POST
|
I think that I found a solution. 1. Used MakeQueryTable_management() to make a view to the table with a where clause that limits the rows. 2. Use TableToTable_conversion() to copy to a table inside the GDB. This worked in both ArcMap and a stand-alone script. I appreciate you taking the time to help.
... View more
04-24-2014
10:47 AM
|
0
|
0
|
626
|
POST
|
Yes, this is the code that ends up exporting the entire table. Here is what I have done so far and the results: 1. Added AddFieldDelimiters() to the build of the where clause. This built the where clause differently - no double quotes around the field names. Result: No difference, the entire table was exported to the GDB. Note: I ran these steps in ArcMAP - MakeTableView_management() with the where clause then TableToTable_conversion() and it did the same thing. The entire table was exported to the GDB. 2. I moved the where clause from the MakeTableView_management() to the TableToTable_conversion() function.
arcpy.MakeTableView_management(dbtPivot, viewPivot)
arcpy.TableToTable_conversion(viewPivot, gdbWorkFull, tblRouteSingleName, sqlWhere)
Result: Execution Error "ExecuteError: ERROR 000339: Input DMS_ADM.DPIVOT_T does not have OIDs Failed to execute (TableToTable)" Note: same results when ran in ArcMap 3. I removed MakeTableView() (I left in the AddFieldDelimiters()) and I just called TableToTable_conversion using the name of the table in Oracle as the input.
# arcpy.MakeTableView_management(dbtPivot, viewPivot)
arcpy.TableToTable_conversion(dbtPivot, gdbWorkFull, tblRouteSingleName, sqlWhere)
Result: Execution Error "ExecuteError: ERROR 000339: Input DMS_ADM.DPIVOT_T does not have OIDs Failed to execute (TableToTable)" Note: Same results when ran in ArcMap
... View more
04-24-2014
10:31 AM
|
0
|
0
|
626
|
POST
|
Can you copy in your entire code you executed? If you have set your workspace to SDE use a staging area to save your table view.
#IMPORTS - PYTHON
import os
#IMPORTS - THIRD PARTY
import arcpy
#IMPORTS - PROJECT
#ESRI - CHECKOUT LICENSE
arcpy.CheckOutExtension("Network")
#ESRI - Set Geoprocessing environments
arcpy.env.XYResolution = "1 Meters"
arcpy.env.XYTolerance = "1 Meters"
#LOCAL GDB
gdbWorkLoc = "D:\\arc10data\\DMS\\Scripts\\Work"
gdbWorkName= "work.gdb"
gdbWorkFull= "{0}\\{1}".format(gdbWorkLoc, gdbWorkName)
#DATABASE PIVOT TABLE
sdeDmsConn = "D:\\arc10data\\DMS\\Scripts\\DBConnections\\DAISGS-DMS.sde"
dbtPivot = "{0}\\DMS_ADM.DPIVOT_T".format(sdeDmsConn)
def main():
#ESTABLISH ZIP AND ROUTE TO WORK WITH
strZipCode = "38111"
strCridId = "C061"
strZipCrid = strZipCode + strCridId
#OUTPUT GDB TABLE
tblRouteSingleName = "Route_{0}_T".format(strZipCrid)
tblRouteSingleFull = "{0}\\{1}".format(gdbWorkFull, tblRouteSingleName)
#OUTPUT VIEW OF PIVOT TABLE
viewPivot = "PivotView"
#CREATE OUTPUT GDB
if not os.path.exists(gdbWorkFull):
arcpy.CreateFileGDB_management(gdbWorkLoc, gdbWorkName)
#BUILD WHERE CLAUSE TO LIMIT ROWS
sqlWhere="\"ZIP_CODE\"='{0}' and \"CRID_ID\"='{1}'".format(strZipCode, strCridId)
#DELETE GDB TABLE IF EXISTS
if arcpy.Exists(tblRouteSingleFull):
arcpy.Delete_management(tblRouteSingleFull)
r1=arcpy.MakeTableView_management(dbtPivot, viewPivot, sqlWhere)
r2=arcpy.TableToTable_conversion(viewPivot, gdbWorkFull, tblRouteSingleName)
if __name__ == '__main__':
main()
... View more
04-24-2014
07:52 AM
|
0
|
0
|
626
|
POST
|
I am having difficulty finding a way that will copy a subset of rows from a table stored in Oracle to a GDB. Here is an example: Source table contains the following fields - "SCHEDULE_TIME", "ADDRESS", "CITY", "STATE", "ZIP_CODE", "ROUTE_ID", "x", "y" I am trying to extract all of the records for a route_id in one zip_code. Here is what I have tried so far: 1. The following code ended up copying the entire table to the GDB. strZipCode = "72645" strRouteId = "C024" sqlWhereStmt="\"ZIP_CODE\"='{0}' and \"ROUTE_ID\"='{1}'".format(strZipCode, strRouteId) #MAKE A VIEW OF THE TABLE AND LIMIT THE ROWS BY THE WHERE CLAUSE arcpy.MakeTableView_management("SourceRoute_T", "SourceRoute_V", sqlWhereStmt) #COPY THE TABLE BY USING THE VIEW CREATED ABOVE arcpy.TableToTable_conversion("SourceRoute_V", "C:\\DDT\\Work\\work.gdb", "route_72645C024_T") 2. This following code failed during the call to TableToTable_conversion with an error about the source table not having OIDs The only change is that the where clause has been moved to the TableTable_conversion function. strZipCode = "72645" strRouteId = "C024" sqlWhereStmt="\"ZIP_CODE\"='{0}' and \"ROUTE_ID\"='{1}'".format(strZipCode, strRouteId) #MAKE A VIEW OF THE TABLE AND LIMIT THE ROWS BY THE WHERE CLAUSE arcpy.MakeTableView_management("SourceRoute_T", "SourceRoute_V") #COPY THE TABLE BY USING THE VIEW CREATED ABOVE arcpy.TableToTable_conversion("SourceRoute_V", "C:\\DDT\\Work\\work.gdb", "route_72645C024_T", sqlWhereStmt)
... View more
04-24-2014
07:29 AM
|
0
|
5
|
3107
|
POST
|
That isn't very easy if you need to take in to account all of the possible variations for street names and you just want the "core" name. For example: N FRONT AVE S FRONT ST FRONT PKWY If you just want to pull 'FRONT' out of that string, then you would need to know all of the possible street directional and suffix words. (Suffixes: ROAD, RD, AVENUE, AVE, STREET, ST, etc... Directionals: N, NORTH, NW, NORTHWEST, NORTH WEST, etc...) Then it gets trickier if you have a street like : NORTH PARKWAY AVENUE In that case you would have to take in to account the positional value of each string and maybe assume that since AVENUE is last it must be the suffix while PARKWAY is the street name - even though PARKWAY is a valid suffix word. My work involves handling addresses all over the United States and we have significant parsing algorithms to handle all of the possible variations. Having said all that, you could probably write something that is relatively simple and that would handle 70% of the possible addresses correctly.
... View more
02-07-2013
11:18 AM
|
0
|
0
|
272
|
Online Status |
Offline
|
Date Last Visited |
11-11-2020
02:23 AM
|