nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Slow Table Merge: Python

BenjaminMittler

Hi all,

I'm merging together 4416 tables all within a single geodatabase, each table has two records in it. I've run this simple python script twice now, once it made it to table 343 after 7 hours, the second time 791 after 16 hours. Both times the script averaged 49 merged tables per hour which means this script would take a total of 90 hrs to run. Is there any other tool i can run that would make this run faster?

Thanks

<SPAN class="keyword token">import</SPAN> arcpy
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> <SPAN class="string token">"C:\\DeleteMe\\tomerge\\tables.gdb"</SPAN>
tblList <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListTables<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>Merge_management<SPAN class="punctuation token">(</SPAN>tblList<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"Merged"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Najděte více příspěvků označených jako

Accepted answers

MicahBabinski

Good morning Benjamin,

Do the tables all have the same schema? If not, are you doing any kind of field mapping for your merge? I am not 100% sure how the merge tool works when no field mapping info is supplied, but I am thinking it may be adding the fields of each input table into the output, which could be hurting your performance.

You may wish to create your output table ahead of time, and then use iteration to append the input tables. Something along the lines of:

<SPAN class="keyword token">import</SPAN> arcpy

arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> <SPAN class="string token">"C:\\DeleteMe\\tomerge\\tables.gdb"</SPAN>
target_table <SPAN class="operator token">=</SPAN> <SPAN class="string token">"C:\\DeleteMe\\output\\final.gdb\\target_table"</SPAN>
tblList <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListTables<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">for</SPAN> t <SPAN class="keyword token">in</SPAN> tblList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>Append_management<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>t<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> target_table<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NO_TEST"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

I was using the Append tool a couple weeks ago to append 60+ feature classes to one target. I think it took about ten minutes.

Good luck!

Micah

All comments

curtvprice

I think this would be faster:

arcpy<SPAN class="punctuation token">.</SPAN>Append_management<SPAN class="punctuation token">(</SPAN>tblList<SPAN class="punctuation token">,</SPAN> target_table<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NO_TEST"</SPAN><SPAN class="punctuation token">)</SPAN>‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

BenjaminMittler

perfect, this saved me 89 hours of processing time.

Thanks!

Hornbydd

Like you and others I have found the Merge tool performs poorly when merging thousands of datasets. I came across this problem a few years ago and gave up and wrote the following VBA code. This code was designed to run in ArcCatalog and is hardwired to specific folders and file names, it is up to you to edit this macro to suit your needs. It works on the same principal as what Joshua Bixby was suggesting in python. It turned what was taking all day to merge into a matter of minutes.

‌

If you are familiar with VBA and have it installed and licensed (which is free) then you could adapt this script?

Public Sub <SPAN class="token function">LoadData</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
 ' Description<SPAN class="punctuation token">:</SPAN> This VBA macro will search a folder <SPAN class="keyword token">for</SPAN> all tables beginning with zs 
 ' and then check to see <SPAN class="keyword token">if</SPAN> they contain <SPAN class="number token">1</SPAN> row<SPAN class="punctuation token">,</SPAN> <SPAN class="keyword token">if</SPAN> they <SPAN class="keyword token">do</SPAN> then the row is copied into 
 ' the output Merged table<SPAN class="punctuation token">.</SPAN>
 '
 ' Usage<SPAN class="punctuation token">:</SPAN> You need to create an output table with the same schema as the tables that 
 ' will be loaded into it<SPAN class="punctuation token">,</SPAN> this is the pTable_Merged object<SPAN class="punctuation token">.</SPAN> All files must be in the 
 ' same workspace<SPAN class="punctuation token">,</SPAN> in this example this would be c<SPAN class="punctuation token">:</SPAN>\temp\zs<SPAN class="punctuation token">.</SPAN> The VBA macro was designed 
 ' to run within ArcCatalog<SPAN class="punctuation token">.</SPAN>
 '
 ' Created<SPAN class="punctuation token">:</SPAN> <SPAN class="number token">24</SPAN><SPAN class="operator token">/</SPAN><SPAN class="number token">9</SPAN><SPAN class="operator token">/</SPAN><SPAN class="number token">10</SPAN>
 ' Author<SPAN class="punctuation token">:</SPAN> Duncan D Hornby <SPAN class="punctuation token">(</SPAN>ddh@geodata<SPAN class="punctuation token">.</SPAN>soton<SPAN class="punctuation token">.</SPAN>ac<SPAN class="punctuation token">.</SPAN>uk<SPAN class="punctuation token">)</SPAN>
 
 ' Get a handle on workspace <SPAN class="punctuation token">(</SPAN>folder<SPAN class="punctuation token">)</SPAN>
 Dim pWorkspaceFactory As IWorkspaceFactory
 Set pWorkspaceFactory <SPAN class="operator token">=</SPAN> New ShapefileWorkspaceFactory
 Dim pWorkspace As IWorkspace
 Set pWorkspace <SPAN class="operator token">=</SPAN> pWorkspaceFactory<SPAN class="punctuation token">.</SPAN><SPAN class="token function">OpenFromFile</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"c:\temp\zs"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN>
 Dim pFeatureWorkspace As IFeatureWorkspace
 Set pFeatureWorkspace <SPAN class="operator token">=</SPAN> pWorkspace
 
 ' Open table to load data into<SPAN class="punctuation token">,</SPAN> this must have the same schema
 ' as the tables that will be loaded into it<SPAN class="punctuation token">.</SPAN> Note we drop the <SPAN class="punctuation token">.</SPAN>dbf
 Dim pTable_Merged As ITable
 Set pTable_Merged <SPAN class="operator token">=</SPAN> pFeatureWorkspace<SPAN class="punctuation token">.</SPAN><SPAN class="token function">OpenTable</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"merged_sed04"</SPAN><SPAN class="punctuation token">)</SPAN>
 
 ' Create an insert cursor
 Dim pCursor As ICursor
 Set pCursor <SPAN class="operator token">=</SPAN> pTable_Merged<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Insert</SPAN><SPAN class="punctuation token">(</SPAN>True<SPAN class="punctuation token">)</SPAN>
 
 ' Get a list of tables in workspace
 Dim pEnumDataset As IEnumDataset
 Set pEnumDataset <SPAN class="operator token">=</SPAN> pWorkspace<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Datasets</SPAN><SPAN class="punctuation token">(</SPAN>esriDTTable<SPAN class="punctuation token">)</SPAN>
 
 ' Declare some objects
 Dim pTable_Load As ITable
 Dim pRow_load As IRow
 Dim pRowBuffer As IRowBuffer
 Dim pFields As IFields
 Dim pDataset As IDataset
 Dim i As Byte
 Dim n As Long ' The number of tables processed
 Dim rej As Long ' The number of tables that are rejected
 Let n <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN>
 Let rej <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>
 
 ' Main processing loop
 Set pDataset <SPAN class="operator token">=</SPAN> pEnumDataset<SPAN class="punctuation token">.</SPAN>Next
 Do While Not pDataset Is Nothing
 ' If the table begins with zs then this is a table we will want to load
 If <SPAN class="token function">Left</SPAN><SPAN class="punctuation token">(</SPAN>pDataset<SPAN class="punctuation token">.</SPAN>Name<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="string token">"zs"</SPAN> Then
 
 ' Report table name then get a handle on it
 Debug<SPAN class="punctuation token">.</SPAN>Print <SPAN class="token function">CStr</SPAN><SPAN class="punctuation token">(</SPAN>n<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">&</SPAN> <SPAN class="string token">" : "</SPAN> <SPAN class="operator token">&</SPAN> pDataset<SPAN class="punctuation token">.</SPAN>Name
 Set pTable_Load <SPAN class="operator token">=</SPAN> pDataset
 
 ' Check <SPAN class="keyword token">if</SPAN> table has <SPAN class="number token">1</SPAN> row in it<SPAN class="punctuation token">,</SPAN> <SPAN class="keyword token">if</SPAN> it does not then reject
 If pTable_Load<SPAN class="punctuation token">.</SPAN><SPAN class="token function">RowCount</SPAN><SPAN class="punctuation token">(</SPAN>Nothing<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN> Then
 
 ' Get row and copy into a new row <SPAN class="keyword token">for</SPAN> merged table
 Set pRow_load <SPAN class="operator token">=</SPAN> pTable_Load<SPAN class="punctuation token">.</SPAN><SPAN class="token function">GetRow</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN>
 Set pRowBuffer <SPAN class="operator token">=</SPAN> pTable_Merged<SPAN class="punctuation token">.</SPAN>CreateRowBuffer
 Set pFields <SPAN class="operator token">=</SPAN> pRow_load<SPAN class="punctuation token">.</SPAN>Fields
 For i <SPAN class="operator token">=</SPAN> <SPAN class="number token">1</SPAN> To pFields<SPAN class="punctuation token">.</SPAN>FieldCount <SPAN class="operator token">-</SPAN> <SPAN class="number token">1</SPAN>
 With pRowBuffer
 <SPAN class="punctuation token">.</SPAN><SPAN class="token function">Value</SPAN><SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">=</SPAN> pRow_load<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Value</SPAN><SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN>
 End With
 Next i
 pCursor<SPAN class="punctuation token">.</SPAN>InsertRow pRowBuffer
 Else
 ' Increment rejected
 Let rej <SPAN class="operator token">=</SPAN> rej <SPAN class="operator token">+</SPAN> <SPAN class="number token">1</SPAN>
 End If
 End If
 
 ' Get next table in folder
 Set pDataset <SPAN class="operator token">=</SPAN> pEnumDataset<SPAN class="punctuation token">.</SPAN>Next
 Let n <SPAN class="operator token">=</SPAN> n <SPAN class="operator token">+</SPAN> <SPAN class="number token">1</SPAN>
 Loop
 
 ' Clean up
 pCursor<SPAN class="punctuation token">.</SPAN>Flush
 Debug<SPAN class="punctuation token">.</SPAN>Print <SPAN class="string token">"Number of tables rejected: "</SPAN> <SPAN class="operator token">&</SPAN> <SPAN class="token function">CStr</SPAN><SPAN class="punctuation token">(</SPAN>rej<SPAN class="punctuation token">)</SPAN>
End Sub‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<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>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

bixb0012

I agree with Micah Babinski‌, consider using the Append tool if all of the schemas are the same, and you just need to dump the records from all of the tables into one table. Also, if the Append tool runs into problems, you could consider using the ArcPy Data Access cursors to accomplish the task. For example, create a new empty table and use an insert cursor on the new table and search cursors on all of the existing tables to insert the records from the existing tables into the new table.

MicahBabinski

Good morning Benjamin,

You may wish to create your output table ahead of time, and then use iteration to append the input tables. Something along the lines of:

<SPAN class="keyword token">import</SPAN> arcpy

arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> <SPAN class="string token">"C:\\DeleteMe\\tomerge\\tables.gdb"</SPAN>
target_table <SPAN class="operator token">=</SPAN> <SPAN class="string token">"C:\\DeleteMe\\output\\final.gdb\\target_table"</SPAN>
tblList <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListTables<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">for</SPAN> t <SPAN class="keyword token">in</SPAN> tblList<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>Append_management<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN>t<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> target_table<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NO_TEST"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

I was using the Append tool a couple weeks ago to append 60+ feature classes to one target. I think it took about ten minutes.

Good luck!

Micah

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.