Spreadsheet data import: Convert 'String' to 'Double' and also '#DIV/0!' to 'Null' (instead of '0's)

[GoldenJiang]

I am having a problem importing excel spreadsheet data into ArcGIS and join with my existing attribute table.

I have tried saving the data as text files or csv. and imported again, but the program still recognise the column data format as 'String', maybe because there are too many text boxes '#DIV/0!'.

The goal is to convert 'String' to 'Double' and importantly: the blank '#DIV/0!' to 'Null' (instead of '0's).

I have tried creating a new column as 'Double' format and then using the Field Calculator to run '[Field]/1', but it turns out 'failure during processing'.

Please let me know if there is any solution.

[DanPatterson_Retired · Accepted solution]

fix the spreadsheet first, you have multiple instances of division by zero.  Assign those entries a null value which is numeric, for instance -9999.  Then save as csv so that the whole column is numeric.  Use a definition query to display only those values not equal to -9999

[ChrisSmith7]

If you save as a flat file (csv/txt), you can use a schema.ini file to specify data types, which ArcGIS will honor. This is something that is created automatically when you open a flat file in ArcGIS; here's more info for you to consult:

Schema.ini File (Text File Driver)

You can edit the schema.ini driver file with a text edit (right click, edit with notepad if it doesn't open with a text editor on double-click). Make sure the .ini file is in the same directory as your flat file.

Regarding you zero division error, you can fix this in Excel before saving as a flat file:

Correct a #DIV/0! error - Excel

It's basically an inline if statement, something like:

=IF(A2=0,"",A1/A2)

This would probably even fix the issue with using Excel as the source in ArcGIS, so it would be recognized as a double.

Also, worst case scenario, you could you could copy your column with division errors, then right click an Excel column, then "paste special", then choose "value only" - this should allow you to find/replace the division error values and replace with null.

[NeilAyres]

I agree with Dan & Chris. Try to fix the spreadsheet first. But this does highlight the potential issues of using spreadsheets as a form of data input .

You could also create a personal geodatabase (access) and use its excel import tools to bring in the data. There you can specify each columns data type. Anything not matching that will be converted to Null.

[BruceHarold]

Hi

If you have the Data Interchangeability extension then there are a lot of tools there for handling Excel as a format and nulls in data.  Like the other contributors say, Excel doesn't support a schema so getting it into a georelational environment can be challenging.

Regards

[DanPatterson_Retired]

Bruce, if it costs, then that is the probable reason.  Given the widespread use of Excel ... and the fact that there is nothing developers can do about it... it is perhaps wise to offer a robust, free set of tools to at least ensure that the excel inputs have an explict format, no blank rows, proper headings and check beyond the first xx-ish rows for compliance.  This is akin to allowing coordinate systems to be erroneously defined and a few other things.  Excel is definitely NOT a data management system, but if people insist/persist on using it, then perhaps the onus should be on preventing its misuse... a harm and frustration measure, if you like

[NeilAyres]

I agree with you Dan. Excel is widely used and misused. In the last 10 years as a private consultant, I have lost count of the number of times I have been given someone's "database", only to be confronted with a pile of spreadsheets!

Perhaps I am getting a little old and tired now....

But the best way to handle these is to import into access with all the columns as correctly defined types. Then if necessary, go back and fix the spreadsheet input.