topic Re: Using the first line from an Excel table as field names in ArcGIS Pro Questions

Using the first line from an Excel table as field names

LR — Fri, 05 Aug 2016 13:38:27 GMT

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.

Re: Using the first line from an Excel table as field names

DanPatterson_Retired — Fri, 05 Aug 2016 14:03:10 GMT

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?

Re: Using the first line from an Excel table as field names

JakeSkinner — Fri, 05 Aug 2016 14:05:54 GMT

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?

Re: Using the first line from an Excel table as field names

LR — Fri, 05 Aug 2016 14:53:55 GMT

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.

Re: Using the first line from an Excel table as field names

LR — Fri, 05 Aug 2016 14:54:44 GMT

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.

Re: Using the first line from an Excel table as field names

DanPatterson_Retired — Fri, 05 Aug 2016 18:03:21 GMT

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

Re: Using the first line from an Excel table as field names

LR — Mon, 08 Aug 2016 13:59:21 GMT

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.

Re: Using the first line from an Excel table as field names

LR — Mon, 08 Aug 2016 14:02:04 GMT

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

Re: Using the first line from an Excel table as field names

ColinZwicker — Tue, 09 Aug 2016 18:59:17 GMT

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

Re: Using the first line from an Excel table as field names

RachaelChasse — Mon, 24 Oct 2016 14:05:33 GMT

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.

Re: Using the first line from an Excel table as field names

MaribethPrice — Thu, 27 Oct 2016 20:06:24 GMT

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.

Re: Using the first line from an Excel table as field names

MaribethPrice — Fri, 28 Oct 2016 15:32:52 GMT

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

Re: Using the first line from an Excel table as field names

RachaelChasse — Fri, 04 Nov 2016 14:33:34 GMT

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.

Re: Using the first line from an Excel table as field names

ShanaBritt — Wed, 06 Sep 2017 21:41:36 GMT

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.

Re: Using the first line from an Excel table as field names

KoryKramer — Tue, 22 Jun 2021 08:40:32 GMT

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

Re: Using the first line from an Excel table as field names

Vidya_SagarGurung — Wed, 27 Nov 2019 12:07:00 GMT

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

Re: Using the first line from an Excel table as field names

Anonymous User — Tue, 03 Mar 2020 23:59:39 GMT

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.