Excel to Table - Null vs. Empty Values

[PhilipQueen]

Hello,

I have an ongoing project that involves taking a table of Northing and Easting values, along with attributes, from a survey team and converting them to GIS points. To do this efficiently, I've formatted an Excel doc to match the fields of my Feature Classes, and when I get a new excel I run it through Excel to Table, Display X,Y for the Table, and then copy and paste from the resulting event layer into the corresponding Feature Class. All of the filled out attributes get transferred over without requiring manual entry, which is quite nice.

My issue comes with attributes that are not filled in. For some fields these come in as empty, and for others they come in as <Null>. There are two fields where it is important that they be <Null> and not empty, but these consistently come in as empty. I can't find any logic as to what is different, either in the Excel or the Feature Class, between the fields which come in in two different ways. I would like to be able to set up the Excel to ensure these two fields are converted to <Null>, without either me or the survey team having to input these <Null> values manually.

Does anyone have leads on how I can do this? Thanks!

[DanPatterson_Retired]

Perhaps this might help

/blogs/dan_patterson/2018/12/13/excel-arrays-tables-in-arcgis-pro 

using Excel to Table is recommended if you want to stay in-house

Excel To Table—Conversion toolbox | ArcGIS Desktop 

Also <null> is a visual artifact... the closest in Python is None, which is used extensively in cursors and the field calculator.

It is best... but not always possible for some, …. to ensure that all cells are entered before they end up in ArcMap or ArcGIS Pro tables.  Excel is popular but it doesn't have the controls necessary.

One option that I used in the past was to fill each spreadsheet column with a 'null' value

-999 for integers

'None' for text

-999e-99 for floats

what you use doesn't matter as long as it isn't a possible value... that is the key to None/Null

So, the 'user' always has a full spreadsheet... they can change the 'null' value to a valid observation, but at the end of the day, the spreadsheet columns are full with no blank spaces

[PhilipQueen]

Thanks for the response Dan,

The information in your blog post that when using Excel to Table, blank cells in numeric fields are translated "properly" (I assume this means as blank) is the kind of information I'm looking for - although neither the fields in the Excel or in the FC are numeric. What I'm interested in is why some fields are transferred <Null> and others empty.

As to the rest of the response, it does matter that the responses are <Null> (and not some other value essentially meaning null) since the client has a backend process running on the data that allow <Null> values but not empty ones. It doesn't matter that the fields lack data, just what form the lacking data takes. I can always add this in by hand, but its much more efficient for me to set up the process to do it automatically - this saves me time, but more importantly is a QA step to deliver data properly.

[TedKowal]

The problem lies within excel.  Excel DOES NOT have or recognize a "Null" value.  The only exception I found so far is that SQL Server will reliably treat the result from the NA() function as a null value (But this is very specific to the database and not very intuitive).

The workaround I use is place a known value as a replacement for NULL.  For example if I wish to export a string column to a database that contains NULLs  I create a VB function or Macro in excel that does something like this placing a text string "NULL" in all the blank cells (this can be enhanced to cover NA values as well: 

Sheet1.UsedRange.SpecialCells(xlCellTypeBlanks)="NULL" 

Then on the database side, I create import scripts that will convert the text String to a DBNull value.  For numbers, instead of using a Null text string I assign a number that will never occur in my data  ... "-10013"  Then on the database side script replacing that number with nulls.

Not pretty, a pain in the behind,  but workable when you fully script it.

[TedKowal]

Answering your second question.... Why some values a blank others are interpreted as NULL -- it is depended upon how excel is determining data types on the cut/paste or export.

My research on MSDN reveals the following information:

• The Excel source component determines the input data types by itself, based on the first 8 rows of the Excel file.
• “Missing values. The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.”  <--- This works if you are programming the transfer but the behaviour your experiencing carries through on a cut/paste operation as well.