2

I am attempting to join census data (county level) to a county base map, using the FIPS county code as the join field.

I can join successfully if I do not alter the original csv file from the census download. However, ArcMap will make all data fields into string types that way and I want a numeric type. According to the ArcMap help file, I should remove all text entries from the first eight rows of the table and make them numerals (I used zero). After making this change, ArcMap won't let me select the FIPS column as the join column anymore. Frustrating! Any suggestions?

3

You cannot choose the join field because they are of different field types.

Create a new field of the same type as the one you are trying to join too, then field calculate your values into your new field. The join should work.

  • 1
    To elaborate on this, ArcMap won't let you join a Text field to a numeric field, or even a long integer to a float (both numeric). To join data on attributes, both fields need to be of the same type. – John Nov 4 '14 at 14:14
  • This worked! ArcMap recognized the FIPS column as text if I inserted a row at the top of the table and entered text in the cell for that one column. It allowed me to join to the FIPS column in the basemap which was also text type. Because the FIPS code has numerals ArcMap was importing them as numerical types. The other data columns were properly recognized as numerical. – Matt S. Nov 4 '14 at 16:07
1

Building on the previous answer, here's a full explanation that may serve others who land on this question.

ArcGIS has a few types of fields (or ways in which it stores information in a column of data): short interger, long interger, float, double, text/string, date. Those types are assigned to a field (column) and may not reflect the contents of a field (i.e. you may have numbers in your field but it is stored as text or you may have intergers stored as float). When joining columns from Excel that are saved as field type .txt (and perhaps others that I haven't checked yet), ArcGIS decides what type of field it is based on the contents of the column (if there are numbers then it will decide that it is interger).

The technical explation above means that you may have a column full of numbers in your ArcGIS field (but that is stored as a text type) and numbers in your table (that you want to join) but ArcGIS won't recognize them as the same type and will not allow you to make a join based on them. In order to make the join, you need to find a way to change the type of column so that they are both recognized as the same (changing the input table type more malleable).

If you have a field saved as text/string in ArcGIS (with numbers) that you want to join to a column of numbers in a table, you can set the type of the column in in your text/csv file by making a few tweaks. In Excel (or other spreadsheet software), create at least eight rows of "fake data" with text (e.g. a, b, c, d, etc) in the table you want to join. Save this new .csv/.text and use it for your join. Then ArcGIS will recognize your column as "text"/"string" and able to join.

  • 1
    @AndreSilva Silva Not sure entirely. It has worked for me before... I've edited the text to make it more clear, hopefully. Although, I admit, I've switched over almost entirely to QGIS in the past 6 months, partly because of frustrations with lack of flexibility in ArcGIS. In QGIS, you can set the type with a .csvt file. – user3386170 May 24 '18 at 18:07
1

I continue to have issues with joins in ArcGIS. I've attempted all of the steps outlined above, but ArcGIS continues to not read my CSV file. I created 8 rows of "fake data," = nada. I created new fields as float, double, and others, but wasn't successful. I couldn't create a field of integers...could this be the problem.

Any help would be much appreciated. Is there a way to modify the data source on the CSV side?

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • Adding to the join issue, they are right, the fields you want to join the tables both have to be the same format, text-=text, float=float, or integer = integer. However if text, they must match case also. You might use trim to remove leading and trailing spaces. Careful some people put numbers in a text field and then it's really text. If you put numbers inside quotes on a csv then it's text. If it has a decimal, float, nothing is an int. If there is one value in a field with a letter or character then it comes in as a text, even if the rest is numbers. – Bill Chappell Jun 5 '18 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.