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; } } }

Problems with EXPORTING a large shapefile containing a join

ElenaLopez

Hi,

I have joined an excel table to a shapefile with multiple registers (more than 200,000) and I???m trying to export it as a new shapefile but it takes more than 4 hours to do it. Does anybody know another way to do it?

I have to repeat the process more that 16 times (using Model Builder) and I need to find a faster way to do it.

Any help will be much appreciated !!

Thank you.

Encuentra más publicaciones etiquetadas con

Accepted answers

RichardFairhurst

Hello,

I run the model using ADD JOIN instead of JOIN FIELD tool but the result is the same (i.e., it is slower than when I worked with shapefiles). The problem I think is the copy features tool (at the end of the process). It takes four hours to process this step. Any recomendation?

I send you a better version of the Model Builder:

[ATTACH=CONFIG]28510[/ATTACH]

Thank you very much.

You are using a Personal Geodatabase (.mdb) in your model, not a File Geodatabase (.gdb). Personal Geodatabases do not perform well. File Geodatabases are almost exclusively what I use. Create a File Geodatabase and replace the Personal Geodatabase in the model with that. Try the Copy Feature with that geodatabase type as the output. It should take much less time. Also create the File Geodatabase on a local drive, not a network drive if you want better performance.

What type is the field that you are joining on? Some field types like long text field values can cause problems in joins. Also, how many fields are in the feature class and table when combined? Part of what is taking so much time is the time the join cursor is searching through the joined data to find a matching record. You could try the Sort tool on the Feature Class and the Join Table and sort both of them on the join field before indexing and creating the join, which should improve performance.

If you only intend to keep the final shapefile and do not actually intend to use the File Geodatabase feature class, I think you can remove the Copy Feature step and go straight to the Feature Class to Feature Class step that outputs to a shapefile directly.

All comments

RichardFairhurst

Cool, thanks. I will give this a try.

Just another note. Overwriting files does not work in Python scripts if you decide to schedule this. You have to explicitly delete a file before you can overwrite it. In Model Builder the delete of the existing file becomes a precondition of a file creation tool that would replace it.

TrishRice

That will work if you create a model to do the update. The Add Join is the same, other than one operates in a model and the other is made visible in desktop. If the model ran while the map was closed and afterward the map was open, it should open everything just fine as long as the data sources and file names had not changed.

Cool, thanks. I will give this a try.

RichardFairhurst

Thanks for the detailed info. I have a bit of a different problem and I think this might help. Does this sound like it would work?

I have a file geodatabase feature class joined with an Excel table and set up in data driven pages. When I try to export selected records to pdf it gives an error. The problem has something to do with the join because it works as soon as I remove the join. I want to try your idea of importing the table to geodatabase to see if that fixes it. I don't want to set up the join all over again each time the Excel table is updated. So I think I would work from ArcCatalog to overwrite the join data with the same name without opening the mxd. Sound good? (This is in 10.0.)

Also is there a difference between the add join from the toolbox, and the add join from right-clicking the layer in the TOC?

That will work if you create a model to do the update. The Add Join is the same, other than one operates in a model and the other is made visible in desktop. If the model ran while the map was closed and afterward the map was open, it should open everything just fine as long as the data sources and file names had not changed.

TrishRice

For Excel use the Table to Table tool to copy it into your new geodatabase, also in the Conversion toolbox and the Geodatabase toolset.

Use the Add Attribute Index tool under the Data Management toolbox in the Indexes toolset on the Join fields in both the new feature class and the table to optimize the join performance.

Use the Make Feature Layer tool on the converted shapefile feature class. Use the Join tool with that layer as the input layer and the geodatabase table as the join source.

Thanks for the detailed info. I have a bit of a different problem and I think this might help. Does this sound like it would work?

I have a file geodatabase feature class joined with an Excel table and set up in data driven pages. When I try to export selected records to pdf it gives an error. The problem has something to do with the join because it works as soon as I remove the join. I want to try your idea of importing the table to geodatabase to see if that fixes it. I don't want to set up the join all over again each time the Excel table is updated. So I think I would work from ArcCatalog to overwrite the join data with the same name without opening the mxd. Sound good? (This is in 10.0.)

Also is there a difference between the add join from the toolbox, and the add join from right-clicking the layer in the TOC?

KristinBlondal

Thanks rfairhur24 for an excellent help.
Solved my problems in a few minutes after hours of non-successful try with joining large shapefiles, using the join field stuff.
Will use GDB and the Join tools from now on.

RichardFairhurst

Yes, that was it. I created a File Geodatabase and now it only takes 5 minutes to conduct the join and create the shapefile. That is great !!

Thank you very much for all your help !

That is a lot more like it. So now you see why I use File Geodatabases almost exclusively.

ElenaLopez

Yes, that was it. I created a File Geodatabase and now it only takes 5 minutes to conduct the join and create the shapefile. That is great !!

Thank you very much for all your help !

RichardFairhurst

Hello,

I run the model using ADD JOIN instead of JOIN FIELD tool but the result is the same (i.e., it is slower than when I worked with shapefiles). The problem I think is the copy features tool (at the end of the process). It takes four hours to process this step. Any recomendation?

I send you a better version of the Model Builder:

[ATTACH=CONFIG]28510[/ATTACH]

Thank you very much.

ElenaLopez

Hello,

I run the model using ADD JOIN instead of JOIN FIELD tool but the result is the same (i.e., it is slower than when I worked with shapefiles). The problem I think is the copy features tool (at the end of the process). It takes four hours to process this step. Any recomendation?

I send you a better version of the Model Builder:

[ATTACH=CONFIG]28510[/ATTACH]

Thank you very much.

Join_Oct13.jpg

RichardFairhurst

Hello,

Thanks for the explanation. I was able to run the model, but ... unfortunately is not running faster than before.

I send you the Model I have generated and I would really appreciate your feedback if you find any error.

[ATTACH=CONFIG]28443[/ATTACH].

Some specific questions I was not sured when I generate the model:
1. I use the tool Join Field and not Add field in the model. Is that correct ?
2. When I used the Copy Feature tool I saved the feature within the geodatabase folder. Is that correct ?
3. In the last step (Feature Class to Feature Class tool), I was not sured how I had to do it, so I saved the final shapefile in a folder, NOT in the geogatabase folder. Is that correct ???

Thank you very very much.

Here are my answers to your questions.

1. I have never used Join Field after trying it once and getting horrible performance. I always use the Add Join and Remove Join tools pair in the Data Management Tools Toolbox in the Joins Toolset. Add Join works in memory and is fast. Join Field directly alters your actual input feature class itself and writes the changes to disk, which performs horribly.

2. Correct. Copy Feature after using Add Join (not Join Field).

3. Correct. The last step is to output to shapefile. That step is only necessary if you intend to keep using shapefiles. Personally, if I could use a file geodatabase only I would never go back to a shapefile.

I can't read your model. It is too small and low resolution. Please confirm that you have converted the shapefile and excel spreadsheet to a file geodatabase. Also confirm that before you do the Add Join that you have added an Attribute Index to the join attribute in both the source and the join tables. I could not tell if you did that in your model.

ElenaLopez

Hello,

Thanks for the explanation. I was able to run the model, but ... unfortunately is not running faster than before.

I send you the Model I have generated and I would really appreciate your feedback if you find any error.

[ATTACH=CONFIG]28443[/ATTACH].

Some specific questions I was not sured when I generate the model:
1. I use the tool Join Field and not Add field in the model. Is that correct ?
2. When I used the Copy Feature tool I saved the feature within the geodatabase folder. Is that correct ?
3. In the last step (Feature Class to Feature Class tool), I was not sured how I had to do it, so I saved the final shapefile in a folder, NOT in the geogatabase folder. Is that correct ???

Thank you very very much.

PrintScrn.jpg

RichardFairhurst

Thanks for your answer. It seems very promising but ... I haven't worked with geodatabase files. Could you please explain me more in detail these steps ??

Thank you very much!!

You can create a geodatabase for your project in ArcCatalog or Desktop's ArcCatalog window in to a local workspace.

In Model Builder use the Feature Class to Feature Class tool in the Conversion toolbox and the To Geodatabase toolset to convert the shapefile into the geodatabase by choosing it as your output workspace.

For Excel use the Table to Table tool to copy it into your new geodatabase, also in the Conversion toolbox and the Geodatabase toolset.

Use the Add Attribute Index tool under the Data Management toolbox in the Indexes toolset on the Join fields in both the new feature class and the table to optimize the join performance.

Use the Make Feature Layer tool on the converted shapefile feature class. Use the Join tool with that layer as the input layer and the geodatabase table as the join source.

Use the Copy Features tool under the Data Management toolbox in the Features toolset to export the joined features to a new feature class.

Then use the Feature Class to Feature Class tool in the Conversion toolbox and the To Geodatabase toolset to convert the geodatabase feature class to a shape file by choosing the ulitimate shapefile workspace as your output workspace and naming the file with .shp.

ElenaLopez

Thanks for your answer. It seems very promising but ... I haven't worked with geodatabase files. Could you please explain me more in detail these steps ??

Thank you very much!!

RichardFairhurst

Hi,

I have joined an excel table to a shapefile with multiple registers (more than 200,000) and I�??m trying to export it as a new shapefile but it takes more than 4 hours to do it. Does anybody know another way to do it?

I have to repeat the process more that 16 times (using Model Builder) and I need to find a faster way to do it.

Any help will be much appreciated !!

Thank you.

I personally have no use for either shapefiles or excel tables when it comes to geoprocessing. I would convert them both into a file geodatabase, perform the join, export to a geodatabase feature class in the same geodatabase and then export to a shapefile. If the geoprocessing occurs on a local workspace file geodatabase and not one installed on your network you will get additional performance gains. I am sure it would cut down the time involved by hours. There is no way to optimize Excel for a join and shapefiles are also poor performers compared to file geodatabase feature classes and tables.

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

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