Convert Result Object to GDB Table

[JohnDye]

I have a table being returned from the Network Analyst OD Cost Matrix...

I want to convert the result object to a GDB Table and I tried using this..

ODCM_Table = arcpy.na.Solve(outNALayer) #Solves the Origin-Destination Cost Matrix and produces the ODCM_Table
arcpy.TableToTable_conversion(ODCM_Table, workspace + "\Default.gdb", "ODCM") # Thought this should convert result to GDB Table

But it throws an error:
Runtime error Traceback (most recent call last): File "<string>", line 1, in <module> File "c:\program files\arcgis\desktop10.1\arcpy\arcpy\conversion.py", line 1904, in TableToTable raise e ExecuteError: Failed to execute. Parameters are not valid. ERROR 000732: Input Rows: Dataset GPL0 does not exist or is not supported Failed to execute (TableToTable).

I thought maybe the cause of the error was the formatting of the result object being held on the 'ODCM_Table' variable, so I printed it to see what it returns...

print ODCM_Table
GPL0

No problem I can see there.

So how can I convert this result object to an actual table?

[RhettZufelt]

If you table is just a standard row/column table and the table2table isn't working, maybe something like this?

import arcpy

arcpy.CreateTable_management(temp_gdb, "temp_table", "D:\\temp.gdb\\template_table", "")  ## use template table with column headings to create new empty table

with da.InsertCursor(temp_table,['FIELD1', FIELD2']) as insert:    ## if the column names match the table, should just insert right in.

  for row in ODCM_Table:
     insert.insertRow(row)
  del row
del insert

Also, sometimes when table2table doesn't work, you can use copyRows to get the same thing.

R_

[BradShore1]

If you are trying to get the lines object from an od cost matrix, you will need to use the "Select" tool. It would look something like this (sorry it's a dirty example):

ND = "your network dataset"
OD_Lines = "output location for table
Lines = r"OD_Matrix\Lines"
OD_Matrix = "OD_Matrix"

arcpy.MakeODCostMatrixLayer_na(ND,OD_Matrix,"Distance","","1")
arcpy.AddLocations_na(OD_Matrix, "Origins", "your input")
arcpy.AddLocations_na(OD_Matrix, "Destinations", "your input")
arcpy.Solve_na(OD_Matrix, "SKIP","CONTINUE")
arcpy.Select_analysis(Lines, OD_Lines)

[JohnDye]

If you are trying to get the lines object from an od cost matrix, you will need to use the "Select" tool. It would look something like this (sorry it's a dirty example):

ND = "your network dataset"
OD_Lines = "output location for table
Lines = r"OD_Matrix\Lines"
OD_Matrix = "OD_Matrix"

arcpy.MakeODCostMatrixLayer_na(ND,OD_Matrix,"Distance","","1")
arcpy.AddLocations_na(OD_Matrix, "Origins", "your input")
arcpy.AddLocations_na(OD_Matrix, "Destinations", "your input")
arcpy.Solve_na(OD_Matrix, "SKIP","CONTINUE")
arcpy.Select_analysis(Lines, OD_Lines)

It seems this only works if you're doing it from the Python Window in ArcMap. Otherwise, I can't seem to get an instantiation of the Lines object. I've tried directly referencing the result object through a variable but then when I try to do something with it it says it doesnt exist.

When I run the exact same code in Catalog...no workie.

[RhettZufelt]

Often when things like this work in ArcMap, but not stand alone python, I find that it is because ArcMap converts needed layers/tables to featureLayers/featuretables on the fly, and is transparent to the user.

When this happens, I just makeFeatureLayer (or makefeaturetable) first, then do my select on that.

You might also need to update your paths.  These look like they are "relative" to the map document scratch gdb or something, as they are not complete.

ND = "your network dataset"
OD_Lines = "output location for table
Lines = r"OD_Matrix\Lines"
OD_Matrix = "OD_Matrix"

In case this is your issue,

R_

[RhettZufelt]

Often when things like this work in ArcMap, but not stand alone python, I find that it is because ArcMap converts needed layers/tables to featureLayers/featuretables on the fly, and is transparent to the user (what you pick from the dropdown in ArcMap is often a featurelayer, not the layer itself).

When this happens, I just makeFeatureLayer (or makefeaturetable) in python first, then do my select on that.

Not sure if you just edited these for posting, but if not, You might also need to update your paths.  These look like they are "relative" to the map document scratch gdb or something, as they don't look "pytonic", so not sure it knows where to look/save.

I.e., how does python know what drive/server/etc to look for the path "OD_Matrix\"?  Is that r"C:\OD_Matrix\", r"\\servername\share\OD_Matrix\" or where?

ND = "your network dataset"
OD_Lines = "output location for table
Lines = r"OD_Matrix\Lines"
OD_Matrix = "OD_Matrix"

I have no experience in OD matricies, so not sure if I'm even in the ballpark here.  But, In case this is your issue,

R_

[JohnDye]

Here's the full code, though it's evolved a bit since the original post.
For the sake of brevity, I'm not posting the entire code here. Just the section causing issues but I do know for a fact that the issue is within the section. I'm also just using the 'cbsaFD' variable statically for testing purposes however, in the actual code 'cbsaFD' represents an list item to iterate through using a for-loop (ie: for cbsaFD in cbsaFD_set).

In a prior step, the 'bgCentroids', 'proForma' and 'AOIs' were clipped to the extent of the the CBSA boundary with which they intersect using SelectLayerByLocation.

arcpy.env.workspace = r"C:\ArcGIS\SYSTEM\COF\Data\CapitalOne.gdb\CBSA_12345"
arcpy.env.overwriteOutput = True
GDB = r"C:\ArcGIS\SYSTEM\COF\Data\CapitalOne.gdb"
cbsaFD = "CBSA_12345"
bgCentroids = r"C:\ArcGIS\SYSTEM\COF\Data\CapitalOne.gdb\CBSA_12345\CBSA_12345_bgCentroids"
proForma = r"C:\ArcGIS\SYSTEM\COF\Data\CapitalOne.gdb\CBSA_12345\CBSA_12345_proForma"
AOIs = r"C:\ArcGIS\SYSTEM\COF\Data\CapitalOne.gdb\CBSA_12345\CBSA_12345_RN"
inOrigins = bgCentroids
inDestinations = proForma
inNetworkDataset = r"C:\ArcGIS\Business Analyst\US\Data\StreetMapData\streets"
outNALayerName = "BG2Branches"
impedenceAttribute = "Length"
outLayerFile = outNALayerName + ".lyr"
outNALayer = arcpy.MakeODCostMatrixLayer_na(inNetworkDataset, outNALayerName, "Length", "", "", "", "", "", "USE_HIERARCHY", "", "NO_LINES")

outNALayer = outNALayer.getOutput(0)

subLayerNames = arcpy.na.GetNAClassNames(outNALayer)
originsLayerName = subLayerNames["Origins"]
originsFieldMap = arcpy.na.NAClassFieldMappings(outNALayer, originsLayerName)
originsFieldMap["Name"].mappedFieldName = "BG_ID"
arcpy.na.AddLocations(outNALayer, originsLayerName, inOrigins, originsFieldMap)

destinationsLayerName = subLayerNames["Destinations"]
destinationsFieldMap = arcpy.na.NAClassFieldMappings(outNALayer, destinationsLayerName)
destinationsFieldMap["Name"].mappedFieldName = "DID"
arcpy.na.AddLocations(outNALayer, destinationsLayerName, inDestinations, destinationsFieldMap)

arcpy.na.Solve(outNALayer)

So in an ArcMap Session, I can grab the result object exactly as you said by referencing the Feature Layer directly out of the TOC and export it to a table:

arcpy.TableToTable_conversion(r"BG2Branches\Lines", cbsaFD + "_ODCM")

The problem arises when I do this through Catalog. Since there is no way to reference the feature directly because ArcMap did not automatically convert the result object to a Feature Layer, I have to do this myself. No sweat, right?

arcpy.MakeFeatureLayer_management(outLayerName, cbsaFD + "ODCM", "", GDB)
Runtime error  Traceback (most recent call last):   File "<string>", line 1, in <module>   File "c:\program files (x86)\arcgis\desktop10.1\arcpy\arcpy\management.py", line 5748, in MakeFeatureLayer     raise e ExecuteError: ERROR 000732: Input Features: Dataset BG2Branches.lyr does not exist or is not supported

So...I tried doing an arcpy.Describe and get an Object Does Not Exist error.
So then I tried instantiating the result object under a variable:

Branch2BG_ODCM_LYR = arcpy.Solve(outNALayer)
Branch2BG_ODCM_LYR = Branch2BG_ODCM_LYR.getOutput(0)
print Branch2BG_ODCM_LYR
GPL0

Whoo hoo, have the object reference under a variable, yay!

arcpy.Describe(Branch2BG_ODCM_LYR)
Runtime error 
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "c:\program files (x86)\arcgis\desktop10.1\arcpy\arcpy\__init__.py", line 1200, in Describe
    return gp.describe(value)
  File "c:\program files (x86)\arcgis\desktop10.1\arcpy\arcpy\geoprocessing\_base.py", line 374, in describe
    self._gp.Describe(*gp_fixargs(args, True)))
IOError: "GPL0" does not exist

What in holy heck is going on here?!

[JamesCrandall]

I have a table being returned from the Network Analyst OD Cost Matrix... 

I want to convert the result object to a GDB Table and I tried using this.. 

ODCM_Table = arcpy.na.Solve(outNALayer) #Solves the Origin-Destination Cost Matrix and produces the ODCM_Table
arcpy.TableToTable_conversion(ODCM_Table, workspace + "\Default.gdb", "ODCM") # Thought this should convert result to GDB Table

But it throws an error: 
Runtime error Traceback (most recent call last): File "<string>", line 1, in <module> File "c:\program files\arcgis\desktop10.1\arcpy\arcpy\conversion.py", line 1904, in TableToTable raise e ExecuteError: Failed to execute. Parameters are not valid. ERROR 000732: Input Rows: Dataset GPL0 does not exist or is not supported Failed to execute (TableToTable).

I thought maybe the cause of the error was the formatting of the result object being held on the 'ODCM_Table' variable, so I printed it to see what it returns... 

print ODCM_Table
GPL0

No problem I can see there. 

So how can I convert this result object to an actual table?

I'm not sure what your source table is (.dbf?), but another possible solution is to convert this source into a NumPyArray first then convert this nparray into a gdb table. This could very easily be done by implementing arcpy.da.TableToNumPyArray (http://resources.arcgis.com/en/help/main/10.1/index.html#//018w00000018000000), then issue arcpy.da.NumPyArrayToTable on that nparray (http://resources.arcgis.com/en/help/main/10.1/index.html#/NumPyArrayToTable/018w00000016000000/).

I tested this with a dbf table (numpytab_out.dbf) and converted it to the Default.gdb as a table with the above described method.

import arcpy
import numpy as np

oLoc = r"C:\Documents\ArcGIS\Default.gdb"
tLoc = r"C:\Documents\ArcGIS"
datasource = r"H:\Documents\ArcGIS\numpytab_out.dbf"

#list out your fieldnames
flds = ['staname', 'change']

#convert the dbf to a numpyarray obj
numpytab = arcpy.da.TableToNumPyArray(datasource, flds)

##check to see if intermediate table numpytab exists and delete it if so
arcpy.env.workspace = oLoc
arcpy.env.overwriteOutput = True
tabs2 = arcpy.ListTables()
for tab2 in tabs2:
   if tab2=="numpytab_out":
     arcpy.Delete_management(r"H:\Documents\ArcGIS\Default.gdb\numpytab_out")
     arcpy.AddMessage("....Deleted numpytab_out")
  
##now covert the numpyarray to the gdb table in the default.gdb   
arcpy.da.NumPyArrayToTable(numpytab, r"H:\Documents\ArcGIS\Default.gdb\numpytab_out")

Sorry if this is way off base or sets you off into an unecessary direction, but it is an option. And I do have some similar methodology in production now.

[JohnDye]

I'm not sure what your source table is (.dbf?),

Hey James, any insight is useful. The ODCM_Table should just be holding the result object from the OD Cost Matrix in Network Analyst. The OD Cost Matrix analysis layer 'outNALayer' is composed of six network analyst classes, one of which is 'Lines' which stores the path information, like Total Length from Origin to Destination.

So really the question here is how can I convert the an NA Class to a Table, or Feature Layer outside of an ArcMap Session. It doesn't seem possible.

[JamesCrandall]

Hey James, any insight is useful. The ODCM_Table should just be holding the result object from the OD Cost Matrix in Network Analyst. The OD Cost Matrix analysis layer 'outNALayer' is composed of six network analyst classes, one of which is 'Lines' which stores the path information, like Total Length from Origin to Destination.

So really the question here is how can I convert the an NA Class to a Table, or Feature Layer outside of an ArcMap Session. It doesn't seem possible.

There is a FeatureClassToNumPyArray function (http://resources.arcgis.com/en/help/main/10.1/index.html#//018w00000015000000) that you could try to pass in this NA Class as it accepts a feature class, layer, table, or table view as input parameter.  In my previous example, just replace arcpy.da.TableToNumPyArray with this FeatureClassToNumPyArray.

Are you writing this outNALayer to disk somewhere?  Just specifiy this as the input FeatureLayer rather than how you have it in your OP:

outNALayer = outNALayer.getOutput(0)

Again, sorry if this is way off.  I guess worth a try though.