Load/append data process should treat incoming data to be loaded as the same type as the target field it will be loaded into

Idea created by aschaffer on Mar 12, 2012
    • spag911
    • tbryant
    • BruceLang
    • rvestal
    • RHirsch
    • aschaffer
    • jimscace
    • 1_harrybowman
    • jstreeb
              I was using the load data process (also the append data process) to populate an existing table in a geodatabase. I discovered that it can make incorrect assumptions about the types of the incoming data to be loaded/appended but that there is no easy way to prevent this. 
              For example, consider a CSV file with a zipcode column. I want to load that CSV file with the zip code column set to text not numeric, because the zip codes in this file (being in New England) begin with 0. But the load proces treats the incoming zipcode data as numeric and thus drops the leading zeros even when the zip code column in the table I am importing into is defined as text, not numeric. Another example: a file with student grades that include K for kindergarten in addition to the numbered grades.  The loader thinks grade is numeric and causes all non-numeric grades to load as nulls.  Worse, there is no user feedback or error report to tell me that those kindergarten grades loaded as nulls.  
              From personal experience, this behavior caused me to waste several hours, after discovering this problem by having to redo a lot of data loading and preparation.  There is no way I can find in ArcGIS to override the load process's data type assumptions.  The workaround I used was to manually editing the incoming CSV data file and adding 20 dummy records at the beginning in order to "fool" the loader into making the correct data type assumptions.  But this does not lend itself to scripting or programmatically repeating this process.
              As there are NO field types in a CSV file, what is happening is that ArcGIS (like Excel) does a read-ahead of the first X number of records and then makes assumptions about the data type based only on that data.  
              I submit that ArcGIS should be respecting the data type of the target (destination) field when importing into an existing target table (similar to what Access and SQL Server can do).  It shouldn't matter if an input column like a zip code contains only numeric characters; if the target column is a text field then ArcGIS should import that column as a text field without alteration and not reinterpret it as a numeric value.  
              I can see where making such data type assumptions might be helpful if importing a CSV into a NEW table, but not when the target already has a defined data type.  But even in this case ArcGIS should allow me as the user to override its assumptions on what it thinks the data type should be PRIOR to executing the import.  
              -Tony Schaffer
               City of Manchester, NH Information Systems Department
               100 Merrimack Street,  Manchester, NH 03101
               Phone: (603) 624-6519 x2309
               Email: aschaffer@ManchesterNH.Gov