topic Re: 2 problems with joining data and exporting them using ArcGIS pro 2.8 in ArcGIS Pro Questions

2 problems with joining data and exporting them using ArcGIS pro 2.8

Seyed-MahdySadraddini — Wed, 20 Apr 2022 20:13:05 GMT

I am using ArcGIS pro 2.8

1. There is a simple task I need to do, like joining a csv to a layer based on a common field, and then export it to save all the information in a new layer. It was really a simple task in ArcMap, but I don't know why after exporting the joined-up layer (selected layer from a shapefile) and the csv table, all data belonging to the csv table are turned into 0s in the exported output layer. That was my first issue.

2. Then I thought, ok maybe I can create a new field in the selected layer, and update it with the information from the csv table. I get the message below saying the column specified does not exist!! Well, I am updating SCI_Score column based on SCI column coming from the csv table; both columns have Double type. They are clearly both there in the joined up layer.

Anybody knows why this is happening?

Re: 2 problems with joining data and exporting them using ArcGIS pro 2.8

KimberlyGarbade — Wed, 20 Apr 2022 21:32:56 GMT

Could it be that someone has the CSV file open, or even may have edited it? If not I'm not sure why it doesn't work (I just tested it and it worked for me), but I have seen similar messages in the past. What might work is:

1) open the csv in excel

2) convert the columns into the data types you want (I.E. make sure number fields are formatted as numbers with the correct number of decimals).

3) Select all the rows in excel and click "Table" on the Insert tab (the selected rows will turn into alternating blue and white rows):

4) Save the Excel

5) Import the data into a file geodatabase as a table (using Table to Table tool) and try you're "relate > export" or "relate > calculate" again.

You could probably skip all the Excel steps and just load your data directly into a table using Pro and Table to Table using the .csv file, but formatting the data has worked for me in the past (depending on how complex it is).

I have found that ArcGIS prefers your data in a nice predictable table format, whereas .csv and Excel can be less than predictable (I.E. can be open in other programs, could have numbers and text stored in the same column, stuff like that).

Re: 2 problems with joining data and exporting them using ArcGIS pro 2.8

curtvprice — Wed, 20 Apr 2022 23:56:44 GMT

I would look for funky field names in either table.

You also may want to try copying the .csv to a file geodatabase and then doing the update. If the table is relatively small, you can even copy it to the memory workspace (fast!).

Of course @DanPatterson would say - load the tables into numpy and do it there (faster).

I also like Kim's idea of formatting data in Excel and loading from there to enforce data type. .csv files can be a real pain.

Re: 2 problems with joining data and exporting them using ArcGIS pro 2.8

Seyed-MahdySadraddini — Thu, 21 Apr 2022 14:40:00 GMT

Thanks for the suggestion. Unfortunately neither of the proposed work arounds resolved my problem.

The reason I had to export the selected layer was because I could not share it on ArcGIS Online

So, I decided not to use the selected layer. Instead, I used the entire shapefile. This resolved one problem.

Then I exported the joined up shapefile and table into a new layer. It maintained all the numbers from the csv/excel table. And then I was able to share that layer online.

Question is then, why can't the same be done with a selected layer.

Re: 2 problems with joining data and exporting them using ArcGIS pro 2.8

RhettZufelt — Thu, 21 Apr 2022 14:49:37 GMT

You say all the fields are double, but it looks like your attached image says SCI_Score is Text.

I have found that ArcMap and ArcPro don't honor the data type set in Excel. They will look at the value in the first row below the column headers (row 2) and whatever type of value is in the field is what ArcGIS assumes the data to be. So, make sure ALL columns on the second row are populated with something as blanks are set to type Text.