Hi~ Everyone,
I want to mass update existing 3,000 rows in attribute table using an excel table without deleting the geometry. Any tools or methods can do that?
Thank you
I'm guessing the problem they would face with performing a join is that it would basically add several new fields to the existing table and not necessarily solve the problem in one-shot. Though, they can always perform a Calculate Field after and copy the data over that way, after the join.
as long as you have a common field between the tables, use
Add Join....
But it would be a good idea to use
Excel to Table to bring the table into your geodatabase first
Yes, you are right. Join the table creates additional fileds, and I just want to use the existing schema for update.Because I have 3,000 rows with 20+ fields, Calculate Field can not update so many things in one-shot. I am looking for some tools in ArcGIS with that function, or some good method can achieve fast update, thank you~
Make the join permanent by saving to a new feature class, then just delete the redundant fields. Deletion is way quicker than calculating. If you need speed, you can do this externally with arcpy and numpy and/or arcpy or numpy, but I would suggest just using what you have and the tools you are familiar with, do the join, save the combined, then delete (fields) what you don't want
Sometimes it is also best just to split the data into rows to fix, rows to keep. then extract the rows you are need updating, perform the above operation, delete extra columns then append with the rows that didn't need fixing. Sometimes it is quicker to divide and recombine into a new incarnation. Often people spend too much time, trying to make changing featureclasses work for them when recreating new ones in the desired structure is often quicker
Thank you Dan, I will try this way. But it looks like no quick tool can do this...That's a pity.
Zhan, is it only one field that you are using as the linking variable between the two tables? Or is it more complicated than that?
There is one common field, I want to keep the schema and just update 3000 of the the attribute rows, and there are more than 20 fields in this attribute table with 50000+ rows.
I'm guessing you could use a 'simple' python script to do this update but it would be no-turning-back sort of deal (no undo button).
Maybe something like this (and I'm sure there's a way to make this more efficient.. I'm just not the most python savvy)
<SPAN class="comment token">#import modules</SPAN> <SPAN class="keyword token">import</SPAN> arcpy <SPAN class="keyword token">from</SPAN> time <SPAN class="keyword token">import</SPAN> strftime <SPAN class="comment token"># start the timer to see how long the script take</SPAN> <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'Start Script: '</SPAN> <SPAN class="operator token">+</SPAN> strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'%Y-%m-%d %H:%M:%S'</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># identify variables for feature class and table</SPAN> workspace <SPAN class="operator token">=</SPAN> <SPAN class="string token">'C:/Users/Name/Documents/GISData.gdb'</SPAN> fc <SPAN class="operator token">=</SPAN> <SPAN class="string token">'FeatureClassName'</SPAN> <SPAN class="comment token"># tbl = 'TableName' #if using a table in your geodatabase</SPAN> <SPAN class="comment token"># if not, use this for a CSV</SPAN> tbl <SPAN class="operator token">=</SPAN> <SPAN class="string token">'C:/Users/Name/Documents/TableName.csv'</SPAN> <SPAN class="comment token"># set the workspace environment to our workspace</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> workspace <SPAN class="comment token"># identify fields used for updating - only pick the ones you need</SPAN> <SPAN class="comment token"># for this example, we are only using 10 fields</SPAN> fieldsFC <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Field1'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Field2'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Field3'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Field4'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Field5'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Field6'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Field7'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Field8'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Field9'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Field10'</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># Here is what we're going to do:</SPAN> <SPAN class="comment token"># One-by-one, check each row in the Feature Class against</SPAN> <SPAN class="comment token"># each row in the Table, and update if there are changes...</SPAN> <SPAN class="comment token"># first, it helps to "zero" out the arrays and the counter</SPAN> fcrow <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">]</SPAN> tblrow <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">]</SPAN> counter <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN> <SPAN class="comment token"># putting this all in a try-catch statement to catch any errors</SPAN> <SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN> fieldsFC<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> tblCursor<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">for</SPAN> tblrow <SPAN class="keyword token">in</SPAN> tblCursor<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> fieldsFC<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> fcCursor<SPAN class="punctuation token">:</SPAN> fcrow <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">''</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># ...need to zero out the row again...</SPAN> <SPAN class="comment token"># in this example, I am seeing if the first column (tblrow[0])</SPAN> <SPAN class="comment token"># matches AND if the fourth column (tblrow[3]) matches,</SPAN> <SPAN class="comment token"># then I go through with the updating of the rows in the </SPAN> <SPAN class="comment token"># feature class (fc) with the table rows</SPAN> <SPAN class="keyword token">for</SPAN> fcrow <SPAN class="keyword token">in</SPAN> fcCursor<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">(</SPAN>tblrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">==</SPAN> str<SPAN class="punctuation token">(</SPAN>fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">and</SPAN> str<SPAN class="punctuation token">(</SPAN>tblrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">==</SPAN> str<SPAN class="punctuation token">(</SPAN>fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> tblrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> tblrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN> fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> tblrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">4</SPAN><SPAN class="punctuation token">]</SPAN> fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">5</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> tblrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">5</SPAN><SPAN class="punctuation token">]</SPAN> fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">6</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> tblrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">6</SPAN><SPAN class="punctuation token">]</SPAN> fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">7</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> tblrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">7</SPAN><SPAN class="punctuation token">]</SPAN> fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">8</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> tblrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">8</SPAN><SPAN class="punctuation token">]</SPAN> fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">9</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> tblrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">9</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Row number '</SPAN> <SPAN class="operator token">+</SPAN> str<SPAN class="punctuation token">(</SPAN>fcrow<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">' was updated.'</SPAN><SPAN class="punctuation token">)</SPAN> fcCursor<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>fcrow<SPAN class="punctuation token">)</SPAN> counter <SPAN class="operator token">=</SPAN> counter <SPAN class="operator token">+</SPAN> <SPAN class="number token">1</SPAN> <SPAN class="keyword token">continue</SPAN> <SPAN class="comment token"># except statement to catch the errors</SPAN> <SPAN class="keyword token">except</SPAN> Exception<SPAN class="punctuation token">:</SPAN> e <SPAN class="operator token">=</SPAN>sys<SPAN class="punctuation token">.</SPAN>exc_info<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>e<SPAN class="punctuation token">.</SPAN>args<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddError<SPAN class="punctuation token">(</SPAN>e<SPAN class="punctuation token">.</SPAN>args<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">except</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ExcecuteError<SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>GetMessages<SPAN class="punctuation token">(</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># how many rows were updated?</SPAN> <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'Updated '</SPAN> <SPAN class="operator token">+</SPAN> str<SPAN class="punctuation token">(</SPAN>counter<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">' rows.'</SPAN> <SPAN class="comment token"># end the timer to see how long the script took</SPAN> <SPAN class="keyword token">print</SPAN> <SPAN class="string token">'Finshed Script: '</SPAN> <SPAN class="operator token">+</SPAN> strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'%Y-%m-%d %H:%M:%S'</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></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>
Just an update, finally, I used field calculator to update each filed after joining the two tables. It takes long to calculate 3000+ records, but it works. Thank you for all your input~
I'm glad to hear that using field calculator essentially solved your issues!
サインインしたメンバーは投稿、更新のフォローなどができます。初めてですか？無料アカウントを登録してください。
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
