nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using the first line from an Excel table as field names

When I add Excel tables to a project, AGP doesn't recognize the first line as fields like ArcMap. Instead it gives me default names and treats the field names as first row. How do I fix this? I'd rather not lose flexibility so importing the tables into a gdb would be rather suboptimal.

Find more posts tagged with

Accepted answers

All comments

Anonymous User

In case this hasnt been answered. Seems the easiest way is to open your .csv or other format and then 'save as' an .xlsx extension. The file imports fine after that.

Vidya_SagarGurung

I sorted it by adding numeric column with unique values e.g. a serial number or object id.

KoryKramer

https://community.esri.com/ideas/16192

Shana Britt

Here is the documentation that outlines the limitations for using Excel spreadsheets in ArcGIS Pro

https://pro.arcgis.com/en/pro-app/help/data/excel/work-with-excel-in-arcgis-pro.htm. You can use the Table to Excel conversion tool in ArcCatalog or ArcMap to export the sheet to a new Excel .xls file to use in ArcGIS Pro if you need to keep the data in Excel format.

RachaelChasse

Another route that I have found that works is using ArcCatalog. If I import this excel data as a table into a file geodatabase I am then able to generate a feature class (within ArcCatalog) that can be used in ArcGIS Pro. ArcCatalog also lets me define the data type on fields, as I also had issues with coordinates being read as text even when the Excel file had it as numeric data.

MaribethPrice

Oh, and I did also try saving as a .xlsx file (office 2013). That didn't work either.

MaribethPrice

The ArcMap and Pro Excel readers do appear different. I had an issue with opening an .xls file containing precip data, which had always worked fine in AM. In Pro, it insisted on reading all the precip values as text. I had to go to a CSV file to get it to read correctly.

RachaelChasse

Hi Colin,

I have this issue with Microsoft Excel for Mac 2011 as well, only when using data in ArcGIS Pro.

IN ARCMAP

I can take an Excel for Mac file into ArcMap and it recognizes the first row as field values:

Excel for Mac Table read by ArcMap

I can then successfully Display XY data in ArcMap:

Display XY Data in ArcMap

And I can successfully save this XY layer as a shape file:

Shape file created by ArcMap

I can successfully use the Microsoft Excel for Mac 2011 data in ArcMap without problems.

IN ARCGIS PRO

When I open this SAME file in ArcGIS Pro, the program does not recognize the first row as field values:

The Same table viewed in ArcGIS Pro

I am unable to display XY data with this table:

Unable to Display XY Data in ArcGIS Pro

I am also unable to export this table, receiving an error message that the file cannot be exported to any location with spaces in the file path. Note, the default file path has spaces in it.

MY CURRENT WORKAROUND

Right now in order to work around the problem I have to load the data first into ArcMap to generate a Shapefile that can be read by ArcGIS Pro/BAO.

RELATED TO EXISTING BUG REPORT?

I have similar issues using this data with BAO, and I believe that there is a bug report on it that was submitted in 2012. From what I understand the architecture of BAO and ArcGIS Pro are similar. Please investigate if this is part of the existing bug report with Mac/Excel, or requires a new bug report.

Excel BAO Bug Report

Colin_Zwicker

I can reproduce the problem in a very specific scenario, however it is unclear the exact excel workflow that yields these results.

The problem is happening because of how the cell is being stored, or in this case the absence of a cell value reference in the xlsx underlying table structure. To support a 64bit application along side potential 32bit Microsoft Office installs AGP utilizes a gdal driver to read excel files. This driver is not happy when the first row read appears to contain 4 cell values (number1, text1, text2, text3), but a subsequent row appears to only contain 3 cell values (1, value1, value2). This forms a jagged edge table and the driver gives up on trying to maintain the user defined headers.

A few potential workarounds:

1. Ensure the last column has no "missing" cell values, this is harder then I thought, as I cant actually narrow down the workflow that removes only the value reference, and not the entire cell reference.

2. Add a fake "calculated" field at the far right (fakefield, contains 1 for all rows)

- or reorder your existing fields to have a fully populated field on each end of the table

3. Export sheet via the geoprocessing tool to a table, python utilizes a different excel driver that handles the jagged edge table in a more reasonable manner.

I hope this explanation allows you to work with your excel data in ArcGIS Pro.

Colin

That's the first thing I look for, but it looks like something's wrong with the file reading. See below

Some new findings: when I copy the data into a new workbook (copy paste, values only), the attribute fields are recognized in AGP. Manually doing anything in a sheet (adding a field, or a formula) seems to break something. Once a workbook is busted (= contains any table that causes problems), adding a new sheet and copying data into it, from within itself or another workbook, results in sheets with the same problem.

This problem only exists when the excel file is used directly, when converting the sheets to a table, they're read correctly.

DanPatterson_Retired

sorry... I though it was the field1 etc line, it is the line underneath. I was more interested in the excel table version, but from what is beneath that (aka from excel) you are using underscores or they were replaced, they aren't excessively long for a geodatabase table etc

Your table works. Mine's a xlsx file made with Excel 2010, German version. AGP version is 1.3. Specifying the encoding when saving the table doesn't do anything.

The field names are in the image. The table works in ArcMap. Using the Excel to table tool also generates a valid table that uses the correct field names. I tried saving the sheet in multiple encoding formats, utf-8 among them, but the result is the same.

jskinner

What version is your excel table? Are you able to open the attached excel table and have the fields display correctly?

Also, what version of ArcGIS Pro are you using?

XY.xlsx

DanPatterson_Retired

These field names may be renamed during the validation process to prevent any errors or duplicate names.

ValidateTableName—ArcPy Functions | ArcGIS for Desktop

Validating table and field names in Python—ArcPy Get Started | ArcGIS for Desktop from which

The field shares its name with a word reserved by the data source (for example, Table).
The field shares its name with a previously defined field.
The field contains an invalid character (for example, *).
The field name exceeds the data source's maximum length for field names.

So what are the field names you are using? Are you using utf-8 encoding or some internationalization that isn't being carried over?

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.