topic Field Type Mismatch when merging excel files in Data Management Questions

Field Type Mismatch when merging excel files

MelvinShuster — Sat, 28 Mar 2015 20:54:47 GMT

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.

Re: Field Type Mismatch when merging excel files

DanPatterson_Retired — Sun, 29 Mar 2015 01:40:03 GMT

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

Re: Field Type Mismatch when merging excel files

DEAF_PROBERT_68 — Sun, 29 Mar 2015 03:36:30 GMT

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

Re: Field Type Mismatch when merging excel files

MelvinShuster — Sun, 29 Mar 2015 21:00:37 GMT

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

Re: Field Type Mismatch when merging excel files

DanPatterson_Retired — Sun, 29 Mar 2015 21:10:18 GMT

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.

Re: Field Type Mismatch when merging excel files

MelvinShuster — Mon, 30 Mar 2015 16:28:30 GMT

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

Re: Field Type Mismatch when merging excel files

JamesPierce — Tue, 31 Mar 2015 18:38:00 GMT

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.

Re: Field Type Mismatch when merging excel files

JoeBorgione — Tue, 31 Mar 2015 18:41:56 GMT

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

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

Re: Field Type Mismatch when merging excel files

MelvinShuster — Sat, 04 Apr 2015 15:07:22 GMT

Thanks to everyone. I appreciate the help.

Re: Field Type Mismatch when merging excel files

SepheFox — Sat, 04 Apr 2015 23:47:38 GMT

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

Re: Field Type Mismatch when merging excel files

MelvinShuster — Sun, 05 Apr 2015 00:18:37 GMT

Thanks for the reminder. Please let me know if I need to do anything else.