import table ignores values

Question

I'm trying to import an excel table into a geodatabase. I made sure all field names are valid, and cells are formatted correctly. The imported geodatabase table is missing numeric data in some cells. On the 'input' table attached, the cells ignored are ones that have a 'D' in the cell immediately to the right of the value. See several highlighted examples and compare to the 'output' table from the geodatabase import. Why is it doing this? How can I fix it without manually adding the missing data?

input.xls

output.xls

MarkFawkes · Answer

I checked your input spreadsheet again at home (previously at work) with Excel 2013. I can see that the cells are  formatted as numbers (with 2 decimal places). However this should mean that the values in the cells that are causing you grief should show with two decimal places, which they are not.

* If I copy a collection of the cells - that has both 'good' and 'bad' values -  to to one of your blank columns (which would normally be formatted as general), I see that cells with 'good' values are right justified, the cells with 'bad' values are left justified - which would normally mean that the 'bad' values are text and the 'good' values are numbers.
* If I sort the worksheet on one of the columns with 'bad' data, Excel tells me I have some numbers formatted as text - if I select to sort numbers and numbers stored as text separately it sorts all of the 'bad' values together.
* Also if I change the formatting of one of these columns to 4 decimal places, then the 'good' values change and the 'bad' values do not. If I retype the 'bad' values in this new column, they revert to numbers with the 2 decimal places.
* If I copy the same set of values to a new column formatted as as numerical and copy values then I still get the 'bad' cells copying as text, which is even stranger.

It is obvious that Excel sees these 'bad' values as text and thus when importing to Arc will not import these values. I have no idea why this is happening, as they look like numerical values to me.

I have found a fix (to change the values). Try this method (from Microsoft Office support site). Unfortunately you can only process one column at a time. It worked for me with your input spreadsheet (all values show with two decimal places).

Convert numbers stored as text to numbers - Office Support

This would be OK as a one off method, if you have to repeat this import frequently then save as a CSV file and import that might be a better alternative.

One of those unexplained mysteries.

JamesHarrington · Answer

Fields are formatted as numbers. There are no leading apostrophes or spaces. Imported to geodatabase as Double.

MarkFawkes · Answer

When I opened the Input spreadsheet (using Excel 2010) the cells next to the D values (i.e. the ones having the problem) I get an error that "The number in the cell is formatted as text or preceded by an apostrophe". Perhaps you need to revisit the input spreadsheet, apply formatting as number to whole column or figure out why there is an apparent difference in formatting, and try to import again.