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

1720
4
Jump to solution
04-20-2022 01:13 PM
Seyed-MahdySadraddini
New Contributor III

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.

SeyedMahdySadraddini_0-1650484166117.png

Anybody knows why this is happening?

 

0 Kudos
1 Solution

Accepted Solutions
Seyed-MahdySadraddini
New Contributor III

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

SeyedMahdySadraddini_0-1650550098555.png

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

SeyedMahdySadraddini_1-1650550834257.png

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.

View solution in original post

0 Kudos
4 Replies
KimGarbade
Occasional Contributor III

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):

KimGarbade_0-1650489844626.png

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).

 

0 Kudos
curtvprice
MVP Esteemed Contributor

I would look for funky field names in either table. How To Name Things In ArcGIS 

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.

0 Kudos
Seyed-MahdySadraddini
New Contributor III

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

SeyedMahdySadraddini_0-1650550098555.png

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

SeyedMahdySadraddini_1-1650550834257.png

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.

0 Kudos
RhettZufelt
MVP Frequent Contributor

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.

R_

0 Kudos