topic Re: Why would ArcPro create longer Text Field lengths than ArcMap? in ArcGIS Pro Questions

Why would ArcPro create longer Text Field lengths than ArcMap?

CharlieKaufman — Wed, 03 Apr 2019 12:03:03 GMT

I recently brought in an excel table of schools in ArcPro, and loaded the x/y coordinates to export out a layer to a geodatabase. When I looked at the layer properties of the new data I just created I was shocked to see that ArcPro was creating "monster" text fields of 2,147,483,647 in length. When I exported the exact same excel file in ArcMap the text fields were the generic 225 in length.

This is going to cause some serious issues when I try to start merging older and newer files together. Not to mention space savings in the long run.

Is there a setting I am missing in Pro, or is this another case of it not liking excel?

Re: Why would ArcPro create longer Text Field lengths than ArcMap?

curtvprice — Wed, 03 Apr 2019 12:13:00 GMT

I don't know the whole story here (it isn't mentioned in the docs) but I do know that (unlike shapefile/.dbf) the file geodatabase isn't actually allocating 2GB of spaces for each record, that field width is a maximum size of the string, not what is stored. If you convert this dataset to shapefile/.dbf, this could be an issue because those formats actually DO write spaces to fill out string fields.

If you load data into the database with the Table to Table or Feature Class To Feature Class tool, you can modify the field map to specify any text field maximum length you want, as long as the data you have fits. If it doesn't fit, you can even truncate it with the field map start and end columns. The field map also gives you full control over field names and numeric data types. For example, you can specify float, Short, or Long, when the default is to bring all Excel numeric data in as Double.

Re: Why would ArcPro create longer Text Field lengths than ArcMap?

DanPatterson_Retired — Wed, 03 Apr 2019 12:41:49 GMT

Try using

Excel To Table—Conversion toolbox | ArcGIS Desktop

It wasn't clear whether you just loaded the excel file or used the above tool to attempt a conversion to a geodatabase table.

Re: Why would ArcPro create longer Text Field lengths than ArcMap?

curtvprice — Sat, 11 Dec 2021 21:54:12 GMT

I've been meaning to do this exercise in Pro 2.3 to see how this is different from ArcMap so I did a little test.

Here's my excel table and .csv version:

FIELD1,FIELD2,FIELD3
aaaaaa,01,1
aaaaaa,02,2
aaaaaa,03,3‍‍‍‍

See the screenshots below. The top table view is what I see when I view .xlsx and .xls format in Pro, the middle is what happens when I view the .csv, the bottom is a table when I read in the .xls using the Excel to Table tool (reading the .xlsx version of the file). As you can see these three imports use different tools to read the data so we get three different interpretations of the same Excel data file.

Note my text codes ('01, '02, '03) get numerified in the CSV import but are still text in the excel imports. Not a trivial deal when trying to load in county FIPS codes or USGS site IDs that can have meaningful leading zeros! This is an improvement over ArcMap, even if the field widths are silly.

Excel is not a database.

Re: Why would ArcPro create longer Text Field lengths than ArcMap?

DanPatterson_Retired — Wed, 03 Apr 2019 13:47:18 GMT

hmmmm maybe the Canadian versions of excel and Pro work differently

If I have to work with excel, I always explictly format the columns though

Re: Why would ArcPro create longer Text Field lengths than ArcMap?

curtvprice — Wed, 03 Apr 2019 13:54:05 GMT

Dan, your results for the excel to table tool are exactly the same as mine (Pro 2.3b2 was the exact version). The difference in the screen shots is that yours are from table views opened from a map's TOC, my screenshots are tables opened directly from the catalog.

I agree formatting the excel sheet can help. But you gotta check! Trust, but verify.

Re: Why would ArcPro create longer Text Field lengths than ArcMap?

DanPatterson_Retired — Wed, 03 Apr 2019 14:29:26 GMT

even more interesting, is that NumPyArrayToTable handles the conversions even better maintaining the data's field width for string/text data

/blogs/dan_patterson/2018/12/13/excel-arrays-tables-in-arcgis-pro

arr = excel_np("c:/temp/x.xlsx")

arr
array([('aaa', '01', 1), ('bbb', '02', 2)],
dtype=[('field1', '<U4'), ('field2', '<U4'), ('field3', '<i4')])

import arcpy

arcpy.da.NumPyArrayToTable(arr, r"C:\...space_less_path\a.gdb\x_arr")

Re: Why would ArcPro create longer Text Field lengths than ArcMap?

Scott_Harris — Wed, 03 Apr 2019 15:38:37 GMT

Hi Charlie,

In Esri Technical Support, we have the following defect report logged:

[BUG-000097684 XLS and XLSX files opened in ArcGIS Pro have extremely large text field lengths.]

The status of the defect is "In Product Plan", meaning the developers intend to fix this issue in a future release of ArcGIS Pro.

If you want to track (or have a case attached) to this defect, please contact Esri Technical Support - https://support.esri.com/en/contact-tech-support

-Scott

Re: Why would ArcPro create longer Text Field lengths than ArcMap?

CharlieKaufman — Wed, 03 Apr 2019 17:59:37 GMT

Thanks Scott. I will go about seeing if I can add my case to this defect.

-Charlie