topic Re: How to set text field width default to values less than 255 when importing tables from MS Excel in Mapping Questions

How to set text field width default to values less than 255 when importing tables from MS Excel

JakeKrall — Mon, 08 Oct 2018 16:47:52 GMT

I import many tables from Excel to a geodatabase regularly. Most have numerous columns (20 or more) that are Text type fields. The default text width value when setting Output Field Properties is 255, much larger than needed. Seems ridiculous users can't change the default to something like 30 or 50, something other than 255 as a width for all text fields being imported. Like many other things in Esri software modifications have to be done individually. Am I just missing something or is there a setting somewhere in the software when running the gp tool Table to Table, where the default width for text fields (under Field Map) can be changed?

Re: How to set text field width default to values less than 255 when importing tables from MS Excel

DanPatterson_Retired — Mon, 08 Oct 2018 16:53:06 GMT

Do you explicitly format it in Excel? Or is it resetting when imported?

Re: How to set text field width default to values less than 255 when importing tables from MS Excel

JakeKrall — Mon, 08 Oct 2018 17:28:03 GMT

I get Excel from many different sources and clean up as much as possible before import. I usually set numeric fields to Number and leave the others alone. I have not found a way to set text field widths in Excel.

Re: How to set text field width default to values less than 255 when importing tables from MS Excel

DanPatterson_Retired — Mon, 08 Oct 2018 18:33:52 GMT

right-click format column width, give it a number, see if it persists when using ExcelToTable.

I usually use csv as the output format, sometimes it is good when bringing it into ArcGIS Pro.

When I am batch processing, I use TableToNumPyArray, format check there, then NumPyArrayToTable to get it back.

Re: How to set text field width default to values less than 255 when importing tables from MS Excel

JakeKrall — Tue, 16 Oct 2018 13:28:56 GMT

Dan, tried your suggestion again just to verify. Setting column widths in Excel has no effect on output width; remains at 255 after setting several to 50 or less. Also, if I convert to .csv 1st, then use Table To Table the text field width increases to 8000. Any other suggestions or comments are appreciated.

Re: How to set text field width default to values less than 255 when importing tables from MS Excel

TedKowal — Sat, 11 Dec 2021 06:39:45 GMT

No answer to your problem, I use MS Access Geodatabases here in my shop and a little VB import script solves my issues like yours. MS Access is a powerful little tool that ESRI is overlooking in pro!

Enable ArcGIS Pro to access ESRI Personal Geodatabases

The ESRI import has a default value of 255 for text fields, anything bigger comes in as a blob. In addition, the import reads the first 8 rows of the excel data imported from which data type is determined..... A template example of resetting text field widths in a personal geodatabase:

Public Sub change_field_size(DBPath as string, _
  tblName As String, fldName As String, fldSize As Integer)
    ' this routine changes the field size
    
    Dim db As Database
    Dim td As TableDef
    Dim fld As field
        
    On Error GoTo errhandler

    Set db = OpenDatabase(DBPath)
    Set td = db.TableDefs(tblName)
    
    If td.Fields(fldName).Type <> dbText Then
        ' wrong field type
        db.Close
        Exit Sub
    End If
    
    If td.Fields(fldName).size = fldSize Then
        ' the field width is correct
        db.Close
        Exit Sub
    End If
    
    ' create a temp feild
    td.Fields.Append td.CreateField("temp", dbText, fldSize)
    td.Fields("temp").AllowZeroLength = True
    td.Fields("temp").DefaultValue = """"""

    ' copy the info into the temp field
    db.Execute "Update " & tblName & " set temp = " & fldName & " "
    
    ' delete the field
    td.Fields.Delete fldName
    
    ' rename the field
    td.Fields("temp").Name = fldName
    db.Close
    
'======================================================================
Exit Sub

errhandler:
MsgBox CStr(Err.Number) & vbCrLf & Err.Description & vbCrLf & "Change Field Size Routine", vbCritical, App.Title

End Sub‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: How to set text field width default to values less than 255 when importing tables from MS Excel

JakeKrall — Tue, 30 Oct 2018 13:13:48 GMT

@Ted. Thank you for the code, but I got away from Access long ago. So many people I work with in the office and outside the office, either hate Access or so used to the ease of data entry in Excel (probably as bad as I hate "Pro"(longed) -verb meaning: extend the duration of.". I'll just post it on the "ideas" page and see where it goes from there.

Re: How to set text field width default to values less than 255 when importing tables from MS Excel

TedKowal — Tue, 30 Oct 2018 15:23:29 GMT

Yes I can understand why people love Excel for data entry... but it is not a database and it starts to fall apart when you try and use it as such... Same thing for the File Geodatabase..... It is not a database . Our field folks love Excel so I have written many of a data validation scripts within excel just to ease my import to GIS pains!

Good luck!

Ted

Re: How to set text field width default to values less than 255 when importing tables from MS Excel

RichardFairhurst — Tue, 30 Oct 2018 15:33:30 GMT

How can you say a File Geodatabase is not a database? Are you saying only an Enterprise Geodatabase is a database? If so, what is the distinguishing feature that makes one a database and the other not? For my uses a File Geodatabase is a database, so is Access or a Personal Geodatabase. If you are saying a File Geodatabase is not easily deployed to people that do not use ArcGIS or Pro, then I have to agree that it has definite limitations.

Re: How to set text field width default to values less than 255 when importing tables from MS Excel

TedKowal — Tue, 30 Oct 2018 15:53:45 GMT

A database is a repository(Infrastructure related software) which serves the purpose of storing data and retrieving the stored data by using some specific structured language such as SQL. The data and retrieval methods are not limited in scope to one specific application or function. A database provides various functions that allow entry, storage and retrieval of large quantities of information and provides ways to manage how that information is organized. The query language(s) used to access the database (such as SQL), and their internal engineering, which affects performance, scalability, resilience, and security.

File Geodatabase is only a structured link list which is only readable and usable to ArcGIS. It is not scalable nor can its internals be tuned by the user. It has no compatibility in other third party applications, nor does it implement anywhere close to a full SQL language for retrieval or using NonSQL structure any of its means. Whereas MS Access does fit the bottom end of a DBMS definition.