0

I have a table with data based on intersections of a cities general plan, zoning, and parcel data. I exported the resulting information into a table and cleaned it up in Excel. Now I want to bring it back into ArcMap and table join this data back into a clean parcels feature class using the APN numbers. The problem I am having is that the APN column includes a string of 8 numbers AND letters which, when brought into Arcmap, leaves the number-letter APN's as NULL.

Likely, this is because bringing this table back into Arcmap makes the column LONG when it should be TEXT to maintain the format of number-letter for purposes of table joining.

I can not, for the life of me, get the 'text' cell format in excel to stick when I bring it back into ArcMap. It always changes it to LONG which then results in NULL values.

I have even tried to save it as .txt rather than .csv and this still happened.

1 Answer 1

2

For these cases, I prefer to use the Table to Table Tool in ArcGIS. This allows you to specify the column type before importing into ArcGIS, so you would be able to specify the APN as a string/text. From there, you can perform any joins based on this output.

This occurs because:

Excel does not enforce field types for values during data entry as standard databases do. Therefore, the field type specified in Excel is not used in determining the field type exposed in ArcGIS. Instead, field type in ArcGIS is determined by a scan of the values in the first eight rows for that field. If the scan finds mixed data types in a single field, that field will be returned as a string field, and the values will be converted to strings.

It seems like the first few rows of your data be those APN numbers that are only numbers, so Arcmap is automatically converting them to LONG.

See: https://desktop.arcgis.com/en/arcmap/latest/manage-data/tables/understanding-how-to-use-microsoft-excel-files-in-arcgis.htm

6
  • The table to table tool is still prohibiting a proper import of the information in my parcels layer. For instance, I have an APN which has a format of 12345A67 and the main parcel layer I am trying to table join shows the 12345A67 that I want to use in the table join. When using the table-to-table tool I import the intersection table which has 12345A67, which I convert to text because Arcmap wants it to be LONG and I set the total number of characters to 8. When I finish the import, however, 12345A67 is NULL and unable to be table joined. This also occurs with APN's with lead zeros.
    – Therow
    Feb 24, 2020 at 18:47
  • Is 12345A67 NULL in your table after using the Table to Table tool, or after attempting a join with your parcel layer?
    – GISHuman
    Feb 24, 2020 at 18:58
  • 1
    Leading zeros can also be tricky in Arc if I recall correctly, see related: gis.stackexchange.com/questions/251661/…
    – GISHuman
    Feb 24, 2020 at 19:01
  • I was able to work out the lead zero issue with the link you gave me but I am still unable to resolve the NULL value which occurs on APN's with a similar setup as "12345A67" despite importing the APN field as text using the table-to-table tool.
    – Therow
    Feb 24, 2020 at 19:09
  • 2
    One dirty trick I use in this case is to create a temporary first row with the right value ( in this case I will fill all text field with something like AAAAA and numeric field with 1234.56789) to made sure arcmap pick the right field type. After exporting to table don't forget to remove the false row....
    – J.R
    Feb 25, 2020 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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