Using the first line from an Excel table as field names

[LR]

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.

[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?

[LR]

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.

[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

[LR]

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

[JakeSkinner]

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?

[LR]

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.

[LR]

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.

[ColinZwicker]

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

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

I can then successfully Display XY data in ArcMap:

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

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:

I am unable to display XY data with this table:

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.