1

I've had a polygon shapefile joined to an XLS file with no issue until today. Although the category of the column category is designated as "Number" in Excel, ArcMap recognizes the column as "String" when the table is added into Arc or when a Join is performed. This is good since it allows for the Join to be performed.

However, something must have happened today as Arcmap now reads the column as "Double". This of course prevents the Join between the shapefile and XLS. I suspect I entered some value incorrectly, but I can't seem to find the problem cell.

I firstly tried changing the column's category in Excel from "number" to "text", but that didn't solve it, nor did changing the category to "general". So I then created a new column ("B", whereas the column I'm joining with the shapefile is column "A"), and used the formula " =TEXT(A2,"0") " . ArcMap will now allow me to join the shapefile to this newly created column.

Although the problem is solved, I want to pinpoint which cell may have causing the original problem. I have thousands of 5 digit numeric values in this column which were joining with no issue before today.

My process is that I work from an XLSX file and then, when I want to see the updates reflected in the joined shapefile, I close Arc (to remove the schema lock from the XLS), and then save the XLSX to XLS. My XLSX file does have a "highlight duplicates" conditional formatting rule, but I don't think that was the problem since it has otherwise worked up to this point. Additionally, the Excel file has a "custom sort" option with two levels of sorting rules, but again that hasn't caused any issues before.

So I just want to know whether there is some way to check for which cell is causing Arc to read the column as "double" as opposed to "string" as it was before.

  • 1
    Welcome to GIS SE! As a new user please take the tour to learn about our focused Q&A format. – Midavalo Apr 7 '17 at 0:36
2

The field type setting in Excel is just a formatting and handling setting, it doesn't actually enforce the contents of the cells to actually match what has been set.

Because of this ArcMap ignores this setting, and instead looks at the first n rows (I think it's the first 8 rows) to determine the field type. If it finds any text in those first few rows, it will treat that column as a text field.

If, however, you have sorted your column in Excel so that those first few rows are now all numeric, ArcMap will now treat that column as a numeric column.

To get around this, and try to force ArcMap to see my columns as text, I will often add a dummy record in the first row (under the headers) in my Excel file, using the word text in any text fields, and just a number such as 1.2 in a double field, and a date in a date field etc., so that hopefully ArcMap will treat those columns as such.

  • That worked! I didn't realize Arc treated Excel columns that way. Until today, most of the first rows had been blank, so I guess Arc had just automatically viewed it as string. I added the dummy row with "Text" under the heading and now it's fixed! Thank you! – Krewe Chief Apr 7 '17 at 1:44

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.