CSV import Zip Code field problem

[DamienMscisz]

ArcGIS Pro 3.2.2

I am importing about 72,000 rows of data that include addresses.  This is from a CSV file that is created from a database and is in plain text with no data type associated.  When we import the file to ArcGIS Pro, a large amount of zip codes show as NULL, when they are actually something like 12345-6789.  Pro is setting the data type to a long integer and then dropping any values with a dash in it.  We cannot figure out how to get Pro to import them as a 10 digit text type.  When we try exporting it and changing the data type to text, it does not affect the missing values - so this seems to be happening upon import.  We can manually change it in Excel before import and that fixes it; however, we are trying to automate this procedure in the model builder and need a better way.

Thanks!

[DanPatterson]

are you using

Table To Geodatabase (Conversion)—ArcGIS Pro | Documentation

if not give it a try

[LauraGosnold]

I had this problem last week as well.  One fix was to append the tables.

However, my work around was doing the Table to GDB conversion.  Good luck!

[jcarlson]

Excel's "field" settings don't carry over. What you should do is:

1. Create the database table and define its fields how you need
2. Use the "Append" tool to load data from your source into the table

By setting the schema first, you won't get any mistaken assumptions on the program side.

[JessicaMcCann]

Similar to Dan and Josh,

You may already be using the Export Table (conversion)  and use the field mapping available.

I am including some documentations link for you to have a look on how ArcGIS Pro imports.

Work with Microsoft Excel files in ArcGIS Pro

A few considerations in the following section Format a table in Microsoft Excel for use in ArcGIS Pro.

Note: "Excel does not enforce field types for values during data entry the way standard databases do, so the field type specified in Excel is not used in determining the field type exposed in ArcGIS. Instead, the field type in ArcGIS is determined by the Microsoft driver."

I would second the two suggestions mentioned above.

Hope that help shed some light.

[ThomasHoman]

Another option is to copy a row in the csv that contains the hyphenated zip to the 2nd row right under the headers. If you just try and import the data, Pro only looks at the first row of data to make it's field type decisions.

Regards,

Tom

[BarryNorthey]

I have csv files dumped from my GPS and Pro sets all text fields to 8000 characters in width. This is a known non lethal bug. I am old school and use a schema.ini file to override this. When you open a csv file, Pro scans the field values in the first line of the csv file (below the column name header if it has one) to set the field type based on the value encountered. If the first value in the zipcode field is 123456789 then it would interpret the field type as Long (integer) whereas if the first value is 12345-6789 it would set the field type to Text as mentioned elsewhere. A schema.ini file can be used to override these setting.

An empty schema.ini file can be manually created and then edited in Notepad but can also be created *automatically and populated if one doesn’t exist. In any case the schema.ini and csv file(s) must reside in the same folder for this to work.  You can have many csv files in the folder but only one schema.ini file. The content of the schema.ini file is structured such that each csv file has its own section or none at all. In your case it might look like this wherein the first line is the csv filename in [brackets], Col2 is named zipcodes (the problematic field) and you are setting it to a Text field type of Width 20 characters:

*You can use the Export Table GP tool to export the Input csv to a new Output csv Table and its section will populate automatically in the existing schema.ini file in the target folder or a new schema.ini file will be created and populated.  This simplifies typing and editing.  

The use of a schema.ini file might be useful when trying to automate a workflow.

[Robert_LeClair]

To add to the previous responses, Zip+4 should be stored in a text field rather than a long integer.  This ArcMap document talks about it but ArcGIS Pro is similar in the workflow.  FAQ: Is It Possible to Generate ZIP+4 Data Using ArcGIS Business Analyst?