Field Type Mismatch when merging excel files

[MelvinShuster]

Hello:

I am attempting to use the merge tool to combine nine excel files that I have imported into ArcGIS 10.3. When I did this, I got the error message "Failed on input OID could not write 'N/A' to output field Air_Temp." I went to the GDB and checked out the data type listed for this field (I got the data from someone else) and noticed that it was "double". I then realized that N/A is sprinkled throughout the data in all nine files.

What is the best way to deal with N/A and the resulting type mismatch when trying to merge these excel files?

Thanks.

[DanPatterson_Retired]

are the field in excel explicitly formatted for the particular data type?​

[PROBERT68]

Are you sure the all of the excel files have the same field name, data types etc ? From what I understand that they have to be in the same data type, or field name etc...

See if this helps Lab 3 - Part 1 - Preparing Excel Spread Sheets for ArcMap. - YouTube

[MelvinShuster]

Thanks Dan and Robert. I check and the same field in different excel files has different data types. When I go to change some of the field types to make them consistent, I get the response: "Unable to alter the field's type. The table or feature class is not empty."

Any thoughts on this? Can I somehow change the field type for a given field in order to make it consistent with the corresponding fields in the other excel files?

Thanks,

Mel

[DanPatterson_Retired · Accepted solution]

​another reason not to use Excel for data management...but I digress

If a field has mixed data types, it can only be 'fixed' by removing the erroneous data or, by adding a new field and copying over the correct data to it.  You will have to repeat this for all files.  It is a wise idea to define your field data type (ie number, text etc) prior to adding any data to it to ensure the correct data is there....doesn't help now, but it will in the future.

[MelvinShuster]

Dan:

Thanks for your help. I looked over the original excel data and discovered that N/A was entered whenever no data was collect. When I imported the data into ArcMap, the software, which, I just learned, scans the first eight records in order to determine the data type, found a mix of data types. In these cases it defaults to text. So the result was a sprinkling of text data types among the majority of double data types.

So I went into the original excel data and deleted the N/A, leaving these cells blank, and imported the data again. This time the excel files merged fine.

Thanks again for helping me with this.

Mel

[JamesPierce]

I have been using FME by Safe Software to manage my tabular datasets.  It works wonders with Excel and allows me to integrate the spreadsheets into my workflows.  If I cannot stop people from using Excel, then I need to work with what they give me.  I think FME has a free trial available.

[JoeBorgione]

...another reason not to use Excel for data management...but I digress  

Yeah....  What Don said.... 

[MelvinShuster]

Thanks to everyone. I appreciate the help.

[SepheFox]

HI melvin, can you mark this question as answered so that people won't think you still need help? Thanks!