Writing a table to memory in ArcPy?

[Elbertde_Hon]

Currently I try to improve an ArcPy script which is runned outside ArcGIS Pro 2.5 in Spyder.

I want to write a table to memory using the Table to Table tool. This tool is placed inside a loop and writes a new table to my workspace for every run. 

Part of my code:

#Defining output path
output = "Routebestand_portaal_"+str(nummer_portaal)+"_selectie"
portalen_join_met_totaal = arcpy.AddJoin_management(in_layer_or_view="Routebestand_200605_totaalbestand_opgeschoond_copy",
                                                    in_field="Portal_OBJECTID", join_table=Portaal_vultabel,
                                                    join_field="Portal_OBJECTID", join_type="KEEP_COMMON")[0]

Portaal_vultabel = r"C:\Users\Work\Portalen_met_intensiteit"

#Write table to gdb
arcpy.TableToTable_conversion(in_rows=portalen_join_met_totaal, out_name= output, where_clause="", field_mapping="Van___naar \"Van___naar\" true true false 200 Text 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.Van___naar,0,200;VTGKM_VWH \"VTGKM_VWH\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.VTGKM_VWH,-1,-1;Portal_OBJECTID \"Portal_OBJECTID\" true true false 4 Long 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.Portal_OBJECTID,-1,-1;SUM_VTGKM_TOT \"SUM_VTGKM_TOT\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Portalen_met_intensiteit.SUM_VTGKM_TOT,-1,-1;SUM_VTGKM_VWH \"SUM_VTGKM_VWH\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Portalen_met_intensiteit.SUM_VTGKM_VWH,-1,-1", config_keyword="")[0]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

..this code works and writes the table to my workspace. Now I want to write the table to memory.

My first approach was figure out how to write a table to memory outside a loop and without considering the variable output. 

#Approach1  - using 'memory'
output1 = r"memory\tmpTable"     
portalen_join_met_totaal = arcpy.AddJoin_management(in_layer_or_view="Routebestand_200605_totaalbestand_opgeschoond_copy",
                                                    in_field="Portal_OBJECTID", join_table=Portaal_vultabel,
                                                    join_field="Portal_OBJECTID", join_type="KEEP_COMMON")[0]

Portaal_vultabel = r"C:\Users\Work\Portalen_met_intensiteit"           

arcpy.TableToTable_conversion(in_rows=portalen_join_met_totaal, out_name= output1, where_clause="", field_mapping="Van___naar \"Van___naar\" true true false 200 Text 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.Van___naar,0,200;VTGKM_VWH \"VTGKM_VWH\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.VTGKM_VWH,-1,-1;Portal_OBJECTID \"Portal_OBJECTID\" true true false 4 Long 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.Portal_OBJECTID,-1,-1;SUM_VTGKM_TOT \"SUM_VTGKM_TOT\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Portalen_met_intensiteit.SUM_VTGKM_TOT,-1,-1;SUM_VTGKM_VWH \"SUM_VTGKM_VWH\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Portalen_met_intensiteit.SUM_VTGKM_VWH,-1,-1", config_keyword="")[0]
 ‍‍‍‍‍‍‍‍‍‍

..or these lines:

#Approach2 - using 'in_memory'
output2 = r"in_memory\tmpTable"
portalen_join_met_totaal = arcpy.AddJoin_management(in_layer_or_view="Routebestand_200605_totaalbestand_opgeschoond_copy",
                                                    in_field="Portal_OBJECTID", join_table=Portaal_vultabel,
                                                    join_field="Portal_OBJECTID", join_type="KEEP_COMMON")[0]

Portaal_vultabel = r"C:\Users\Work\Portalen_met_intensiteit"

arcpy.TableToTable_conversion(in_rows=portalen_join_met_totaal, out_name= output2, where_clause="", field_mapping="Van___naar \"Van___naar\" true true false 200 Text 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.Van___naar,0,200;VTGKM_VWH \"VTGKM_VWH\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.VTGKM_VWH,-1,-1;Portal_OBJECTID \"Portal_OBJECTID\" true true false 4 Long 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.Portal_OBJECTID,-1,-1;SUM_VTGKM_TOT \"SUM_VTGKM_TOT\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Portalen_met_intensiteit.SUM_VTGKM_TOT,-1,-1;SUM_VTGKM_VWH \"SUM_VTGKM_VWH\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Portalen_met_intensiteit.SUM_VTGKM_VWH,-1,-1", config_keyword="")[0]‍‍‍‍‍‍‍‍‍

..both these options delivered an error message:

ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000354: The name contains invalid characters
Failed to execute (TableToTable).

What I would like to get is something like r"memory\   + "Routebestand_portaal_"+str(nummer_portaal)+"_selectie"

Any suggestions how to solve this?

[DavidPike]

import os

os.path.join("in_memory","Routebestand_portaal_"+str(nummer_portaal)+"_selectie")

[Elbertde_Hon]

Thanks David for your reply! I tried to implement your suggestion in the following way:

#Approach1  - using 'memory'
import os

output1=os.path.join("in_memory","Routebestand_portaal_"+str(nummer_portaal)+"_selectie")
 
portalen_join_met_totaal = arcpy.AddJoin_management(in_layer_or_view="Routebestand_200605_totaalbestand_opgeschoond_copy",
                                                    in_field="Portal_OBJECTID", join_table=Portaal_vultabel,
                                                    join_field="Portal_OBJECTID", join_type="KEEP_COMMON")[0]

Portaal_vultabel = r"C:\Users\Work\Portalen_met_intensiteit"           

arcpy.TableToTable_conversion(in_rows=portalen_join_met_totaal, out_name= output1, where_clause="", field_mapping="Van___naar \"Van___naar\" true true false 200 Text 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.Van___naar,0,200;VTGKM_VWH \"VTGKM_VWH\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.VTGKM_VWH,-1,-1;Portal_OBJECTID \"Portal_OBJECTID\" true true false 4 Long 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Routebestand_200605_totaalbestand_opgeschoond_copy.Portal_OBJECTID,-1,-1;SUM_VTGKM_TOT \"SUM_VTGKM_TOT\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Portalen_met_intensiteit.SUM_VTGKM_TOT,-1,-1;SUM_VTGKM_VWH \"SUM_VTGKM_VWH\" true true false 8 Double 0 0,First,#,C:\\Users\\Work\\Data.gdb\\Routebestand_200605_totaalbestand_opgeschoond_copy,Portalen_met_intensiteit.SUM_VTGKM_VWH,-1,-1", config_keyword="")[0]
 ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

However, I still got the same error. Probably this tool just expects a table name (in relation to the set workspace) and not a whole path..

[DavidPike · Accepted solution]

TableToTable(in_rows, out_path, out_name, {where_clause}, {field_mapping}, {config_keyword})

out_path = "in_memory"

out_name = "Routebestand_portaal_" + str(nummer_portaal) + "_selectie" 

maybe

[Elbertde_Hon]

That works perfectly David! 

[JoshuaBixby]

in_memory represents the older in-memory workspace, for Pro I recommend transitioning to memory.

Since the error states invalid characters, can you paste one of the full paths (not the code to make the path) here for people to see?