topic Re: Exporting a joined layer results in empty dataset, need help. in Geoprocessing Questions

Exporting a joined layer results in empty dataset, need help.

DavidMedeiros — Thu, 07 Jun 2012 15:09:17 GMT

I have two seemingly related problems I need some help with:

1) I have a feature class of points that I joined to a table of data saved as an .xls file. The join works perfectly, however when I make the join permanent by exporting the newly joined layer the new layer has no features, zero entries in the table where it should have 143. I tried several methods of extracting the dynamically joined layer to a new permanent feature class, in the end the only thing that worked was using the copy features tool.

2) Using that now permanent copied points layer, I made a new join to a layer of polygons keeping only matching records. The new table has 7 entries out of 143 possible. Using the normal data>export... method to make the join permanent I get a new feature class but this time instead of 7 features (or none like above) I get 143 features.

Does this sounds familiar to anyone? Any idea why this is happening?

Dave

Re: Exporting a joined layer results in empty dataset, need help.

MathieuCain — Thu, 07 Jun 2012 19:10:59 GMT

I have two seemingly related problems I need some help with:

1) I have a feature class of points that I joined to a table of data saved as an .xls file. The join works perfectly, however when I make the join permanent by exporting the newly joined layer the new layer has no features, zero entries in the table where it should have 143. I tried several methods of extracting the dynamically joined layer to a new permanent feature class, in the end the only thing that worked was using the copy features tool.

2) Using that now permanent copied points layer, I made a new join to a layer of polygons keeping only matching records. The new table has 7 entries out of 143 possible. Using the normal data>export... method to make the join permanent I get a new feature class but this time instead of 7 features (or none like above) I get 143 features.

Does this sounds familiar to anyone? Any idea why this is happening?

Dave

I'm assuming you are joining your xls layer to the points feature class (not the other way around). You could create dummy empty fields before the join (with same data types as the xls table fields) and then after the join, populate those fields based on the joined table fields. That way, the data will remain permanently in the points feature class, even after the join is removed.

Re: Exporting a joined layer results in empty dataset, need help.

DavidMedeiros — Thu, 07 Jun 2012 19:33:34 GMT

I'm assuming you are joining your xls layer to the points feature class (not the other way around). You could create dummy empty fields before the join (with same data types as the xls table fields) and then after the join, populate those fields based on the joined table fields. That way, the data will remain permanently in the points feature class, even after the join is removed.

Joining table to points, then using Data>Export Data... to make a permanent copy of the points layer. I've done this hundreds of times and is basically the primary method for making a join permanent in ArcGIS. I've encountered the empty export issue a few times here and there but not the problem I described 2) above. Either way this is generally a straightforward function that we use all the time in Arc. It may be here data that's causing the problem, but I can't think of why.

Re: Exporting a joined layer results in empty dataset, need help.

MathieuCain — Thu, 07 Jun 2012 19:40:41 GMT

Joining table to points, then using Data>Export Data... to make a permanent copy of the points layer. I've done this hundreds of times and is basically the primary method for making a join permanent in ArcGIS. I've encountered the empty export issue a few times here and there but not the problem I described 2) above. Either way this is generally a straightforward function that we use all the time in Arc. It may be here data that's causing the problem, but I can't think of why.

That is indeed the usual approach. I was simply offering another approach, since the first appeared to be giving mixed results.

Re: Exporting a joined layer results in empty dataset, need help.

DavidMedeiros — Thu, 07 Jun 2012 19:52:59 GMT

That is indeed the usual approach. I was simply offering another approach, since the first appeared to be giving mixed results.

I appreciate the help, my exasperation wasn't pointed at you. I'm frustrated with the software! For a workaround using the copy features tool seemed to work ok and requires less manual work than the method above but its good to have multiple solutions.

But I really need to understand the underlying issue and how to resolve it not just for this student but for future ones as well. I work with dozens of students a quarter here and rely heavily on this function to make permanent copies of their analysis results and table joins and I can't be resorting to workarounds for such a basic function all the time.

Re: Exporting a joined layer results in empty dataset, need help.

MathieuCain — Thu, 07 Jun 2012 21:11:58 GMT

I appreciate the help, my exasperation wasn't pointed at you. I'm frustrated with the software! For a workaround using the copy features tool seemed to work ok and requires less manual work than the method above but its good to have multiple solutions.

But I really need to understand the underlying issue and how to resolve it not just for this student but for future ones as well. I work with dozens of students a quarter here and rely heavily on this function to make permanent copies of their analysis results and table joins and I can't be resorting to workarounds for such a basic function all the time.

Likely a silly question, but you're exporting "All Features" as opposed to "All Features In View Extent" I presume.

Re: Exporting a joined layer results in empty dataset, need help.

JonathanQuinn — Thu, 07 Jun 2012 23:52:02 GMT

I assume you've checked for leading or trailing spaces in the field names? Is the data available to share?

Re: Exporting a joined layer results in empty dataset, need help.

curtvprice — Fri, 08 Jun 2012 00:26:27 GMT

I assume you've checked for leading or trailing spaces in the field names? Is the data available to share?

I'm with the perceptive Mr. Quinn.

You need to be very careful with .xls and .txt tables that they follows Microsoft Jet/.shp/.dbf file naming limitations: No tables or field names starting with a number, no spaces, no special characters (except "_"), short (10-chars). To avoid trouble, I try to follow these rules with all datasets. You can always be more creative with field aliases and layer names, but the datasets themselves should use "safe" names. If you do that, field names are also less likely to be garbled if the geoprocessing workflow goes through .shp or .dbf formats.

If you violate these rules, the tabular data may be read okay, but joins are not likely to work.

As for the Copy Features tool, I believe it's a it's a much more useful method than data/export, since when you want to do it again (say after you change the underlying data), you don't need to go wandering through the interface, you can instead pull the Copy Features out of Geoprocessing results and re-run it. The Copy Features tool also is guaranteed to honor the specified environment settings for Current Workspace and Qualified Field Names.

Re: Exporting a joined layer results in empty dataset, need help.

DavidMedeiros — Tue, 12 Jun 2012 15:35:14 GMT

I'm with the perceptive Mr. Quinn.

You need to be very careful with .xls and .txt tables that they follows Microsoft Jet/.shp/.dbf file naming limitations: No tables or field names starting with a number, no spaces, no special characters (except "_"), short (10-chars). To avoid trouble, I try to follow these rules with all datasets. You can always be more creative with field aliases and layer names, but the datasets themselves should use "safe" names. If you do that, field names are also less likely to be garbled if the geoprocessing workflow goes through .shp or .dbf formats.

If you violate these rules, the tabular data may be read okay, but joins are not likely to work.

As for the Copy Features tool, I believe it's a it's a much more useful method than data/export, since when you want to do it again (say after you change the underlying data), you don't need to go wandering through the interface, you can instead pull the Copy Features out of Geoprocessing results and re-run it. The Copy Features tool also is guaranteed to honor the specified environment settings for Current Workspace and Qualified Field Names.

Just wanted to get back to this after having reviewed the input table. As far as I can see, no special characters, no numbers first, no spaces etc. I always follow the same rules but hadn't specifically checked this students table so wasn't sure. It may just be a bad table creation process leading to some underlying issue with the table itself. Although I can't see how it would successfully join to a layer but not export.

As for exporting dynamic joins and selections, I typically use the data>export method because its right there on the layer. For the researches and academics I work with the contextual right click tools are often easier to find than toolbox tools and it fits their one-off work processes better I think. That said, the Copy Features tool obviously works better with iffy table joins so maybe I need to switch to that tool right away.

Re: Exporting a joined layer results in empty dataset, need help.

curtvprice — Tue, 12 Jun 2012 17:12:49 GMT

As far as I can see, no special characters, no numbers first, no spaces etc.

One more potential problem that I should add to the list are any SQL keywords for field names: "IN","OVER","COUNT" etc.

Re: Exporting a joined layer results in empty dataset, need help.

HenriRiihimäki — Fri, 21 Feb 2014 05:47:04 GMT

Using validate join helps.

I got an error from a field with invalid character: ' '

Solution:

I went to excel and replaced space ' ' by nothing

-> save .xls
-> restart arcmap (yes. no idea why one would have to do this, but otherwise excel edits don't update, even if you remove .xls -fiele and add it again)

-> add updated excel after restart
-> perform join
-> export data

Re: Exporting a joined layer results in empty dataset, need help.

HenriRiihimäki — Fri, 21 Feb 2014 06:12:58 GMT

Re: Exporting a joined layer results in empty dataset, need help.

curtvprice — Fri, 21 Feb 2014 14:15:47 GMT

no idea why one would have to do this, but otherwise excel edits don't update, even if you remove .xls -fiele and add it again)

The restart is necessary because ArcMap caches the data after the first read and does not go back to the original table during that ArcMap session. This is done for performance reasons. Otherwise every time you opened a large linked table, the wait would be pretty unacceptable.

Sometimes you can get around this by creating a new arcmap document and then re-opening your old one. This is usually a lot faster than restarting ArcMap).

Re: Exporting a joined layer results in empty dataset, need help.

crystalmcdermott — Fri, 18 Sep 2015 13:21:14 GMT

I just had the exact same problem with exporting a joined table and getting an empty feature class. I used the copy features tool instead and it worked. Go figure.

Re: Exporting a joined layer results in empty dataset, need help.

KyleMonster — Fri, 22 Apr 2016 20:40:54 GMT

I find it helpful, sometimes, to export the xls(x) to csc and then open in notepad to edit. Then join the csv, not the excel file.

Re: Exporting a joined layer results in empty dataset, need help.

robstrange — Fri, 30 Sep 2016 16:34:33 GMT

Because of one "&" in all of my dataset, that was in .csv file contaminated the whole join. It was deceiving because it joined successfully, but on the export, all the new values were null. Once i removed the "&" in one of the values, the data exported correctly.

Re: Exporting a joined layer results in empty dataset, need help.

HossameldinElalkamy — Thu, 12 Jan 2017 00:44:31 GMT

I had the same problem. Copy Feature solved it in the best way because I can not edit the joined layers.

Re: Exporting a joined layer results in empty dataset, need help.

FaisalBasudan1 — Sun, 09 Sep 2018 10:53:21 GMT

I had the same issue. It's been solved by saving the table that you want to join with the Feature Class in CSV (Comma delimited) format, and then redo the Join and Export the data.

Re: Exporting a joined layer results in empty dataset, need help.

CoreGeoscience_Services_Inc_ — Tue, 27 Aug 2024 15:31:18 GMT

I had the same issue and the problem was that I had features selected in the data I was joining to that didn't match/relate/join with the data I was joining to and during the join I unchecked the "Keep all input features". When I went to export my data, the export tool was using the selected features for the output, which resulted in an empty output.