Using the first line from an Excel table as field names

14365
16
08-05-2016 06:38 AM
LR
by
Occasional Contributor III

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.

0 Kudos
16 Replies
DanPatterson_Retired
MVP Emeritus

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
by
Occasional Contributor III

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.

0 Kudos
DanPatterson_Retired
MVP Emeritus

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

0 Kudos
LR
by
Occasional Contributor III

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

0 Kudos
JakeSkinner
Esri Esteemed Contributor

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
by
Occasional Contributor III

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.

0 Kudos
LR
by
Occasional Contributor III

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
Esri Contributor

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
Occasional Contributor

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