Writing a table to memory in ArcPy?

ElbertdeHon

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:

<SPAN class="comment token">#Defining output path</SPAN>
output <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Routebestand_portaal_"</SPAN><SPAN class="operator token">+</SPAN>str<SPAN class="punctuation token">(</SPAN>nummer_portaal<SPAN class="punctuation token">)</SPAN><SPAN class="operator token">+</SPAN><SPAN class="string token">"_selectie"</SPAN>
portalen_join_met_totaal <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddJoin_management<SPAN class="punctuation token">(</SPAN>in_layer_or_view<SPAN class="operator token">=</SPAN><SPAN class="string token">"Routebestand_200605_totaalbestand_opgeschoond_copy"</SPAN><SPAN class="punctuation token">,</SPAN>
                                                    in_field<SPAN class="operator token">=</SPAN><SPAN class="string token">"Portal_OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> join_table<SPAN class="operator token">=</SPAN>Portaal_vultabel<SPAN class="punctuation token">,</SPAN>
                                                    join_field<SPAN class="operator token">=</SPAN><SPAN class="string token">"Portal_OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> join_type<SPAN class="operator token">=</SPAN><SPAN class="string token">"KEEP_COMMON"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>

Portaal_vultabel <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\Work\Portalen_met_intensiteit"</SPAN>

<SPAN class="comment token">#Write table to gdb</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>TableToTable_conversion<SPAN class="punctuation token">(</SPAN>in_rows<SPAN class="operator token">=</SPAN>portalen_join_met_totaal<SPAN class="punctuation token">,</SPAN> out_name<SPAN class="operator token">=</SPAN> output<SPAN class="punctuation token">,</SPAN> where_clause<SPAN class="operator token">=</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN> field_mapping<SPAN class="operator token">=</SPAN><SPAN class="string token">"Van___naar \"Van___naar\"</SPAN> true true false <SPAN class="number token">200</SPAN> Text <SPAN class="number token">0</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">,</SPAN>First<SPAN class="punctuation token">,</SPAN><SPAN class="comment token">#,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]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

<SPAN class="comment token">#Approach1  - using 'memory'</SPAN>
output1 <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"memory\tmpTable"</SPAN>     
portalen_join_met_totaal <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddJoin_management<SPAN class="punctuation token">(</SPAN>in_layer_or_view<SPAN class="operator token">=</SPAN><SPAN class="string token">"Routebestand_200605_totaalbestand_opgeschoond_copy"</SPAN><SPAN class="punctuation token">,</SPAN>
                                                    in_field<SPAN class="operator token">=</SPAN><SPAN class="string token">"Portal_OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> join_table<SPAN class="operator token">=</SPAN>Portaal_vultabel<SPAN class="punctuation token">,</SPAN>
                                                    join_field<SPAN class="operator token">=</SPAN><SPAN class="string token">"Portal_OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> join_type<SPAN class="operator token">=</SPAN><SPAN class="string token">"KEEP_COMMON"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>

Portaal_vultabel <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\Work\Portalen_met_intensiteit"</SPAN>           

arcpy<SPAN class="punctuation token">.</SPAN>TableToTable_conversion<SPAN class="punctuation token">(</SPAN>in_rows<SPAN class="operator token">=</SPAN>portalen_join_met_totaal<SPAN class="punctuation token">,</SPAN> out_name<SPAN class="operator token">=</SPAN> output1<SPAN class="punctuation token">,</SPAN> where_clause<SPAN class="operator token">=</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN> field_mapping<SPAN class="operator token">=</SPAN><SPAN class="string token">"Van___naar \"Van___naar\"</SPAN> true true false <SPAN class="number token">200</SPAN> Text <SPAN class="number token">0</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">,</SPAN>First<SPAN class="punctuation token">,</SPAN><SPAN class="comment token">#,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]</SPAN>
 <SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

 

..or these lines:

<SPAN class="comment token">#Approach2 - using 'in_memory'</SPAN>
output2 <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"in_memory\tmpTable"</SPAN>
portalen_join_met_totaal <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddJoin_management<SPAN class="punctuation token">(</SPAN>in_layer_or_view<SPAN class="operator token">=</SPAN><SPAN class="string token">"Routebestand_200605_totaalbestand_opgeschoond_copy"</SPAN><SPAN class="punctuation token">,</SPAN>
                                                    in_field<SPAN class="operator token">=</SPAN><SPAN class="string token">"Portal_OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> join_table<SPAN class="operator token">=</SPAN>Portaal_vultabel<SPAN class="punctuation token">,</SPAN>
                                                    join_field<SPAN class="operator token">=</SPAN><SPAN class="string token">"Portal_OBJECTID"</SPAN><SPAN class="punctuation token">,</SPAN> join_type<SPAN class="operator token">=</SPAN><SPAN class="string token">"KEEP_COMMON"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>

Portaal_vultabel <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\Users\Work\Portalen_met_intensiteit"</SPAN>

arcpy<SPAN class="punctuation token">.</SPAN>TableToTable_conversion<SPAN class="punctuation token">(</SPAN>in_rows<SPAN class="operator token">=</SPAN>portalen_join_met_totaal<SPAN class="punctuation token">,</SPAN> out_name<SPAN class="operator token">=</SPAN> output2<SPAN class="punctuation token">,</SPAN> where_clause<SPAN class="operator token">=</SPAN><SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN> field_mapping<SPAN class="operator token">=</SPAN><SPAN class="string token">"Van___naar \"Van___naar\"</SPAN> true true false <SPAN class="number token">200</SPAN> Text <SPAN class="number token">0</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">,</SPAN>First<SPAN class="punctuation token">,</SPAN><SPAN class="comment token">#,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]</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

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