Table to Excel - Null values

[MErikReedAugusta]

Idea:

In the Table to Excel tool, add a parameter to specify the value that the tool will write to any cell that has a NULL value in the original table (e.g., a "sentinel value").

Optionally, there could also be multiple parameters for specifying different sentinel values for each possible data type, but this might likely be overkill.

The Problem:

As we all (likely) know, Excel has no real concept of a NULL value.  Currently, when you run Table to Excel, it effectively just doesn't write to those cells, which means depending on what you're doing with the final file, Excel might read that as a 0 or an empty string ("").

There's not really a clean and easy way to replace these "fake nulls" after export (especially if your data has a mixture of legitimate zeroes and NULLs, for example) and it seems like a tedious waste of time & effort to make a copy of this table, manually change all those NULLs to a sentinel value, and then finally run Table to Excel.

Given my limited experience with Python's excel libraries, this seems like something that should be able to handled by the conversion engine(s) on the back end at the time that they're writing the Excel file.

I'm tempted to write up a script myself, but given that it feels a bit like reinventing the wheel at that point, I thought I should raise the Idea so everyone could benefit.

[JonathanNeal]

@MErikReedAugusta Maybe this script can help you with the null string fields? Here is the doc link for other datatypes besides string if you want to do it for numerics or dates.
ListFields—ArcGIS Pro | Documentation

import arcpy
sentinel_for_string = "sentinel"
table_path = "c:/data/municipal.gdb/hospitals"
# https://pro.arcgis.com/en/pro-app/latest/arcpy/functions/listfields.htm
fields = arcpy.ListFields(table_path, field_type="String") 
tab = arcpy.management.CopyRows(table_path, "memory\\t_hospitals")[0]
field_names = [f.name for f in fields]
ftype = ["TEXT"] * len(field_names)
fname = [f"{i}_" for i in field_names]
calculation = [f"\"{sentinel_for_string}\""] * len(field_names)
where = [f"{i} IS NULL" for i in field_names]
exp = list(zip(fname, calculation, where))
arcpy.management.AddFields(tab, list(zip(fname, ftype)))
arcpy.management.CalculateFields(tab,
                                "PYTHON3",
                                exp)
arcpy.conversion.TableToExcel(tab, "c:/data/hospitals.xlsx")

[MErikReedAugusta]

Worth noting that the first thing that jumps out at me is that this currently relies on adding a field, which isn't always an option.

And as I pointed out in my original post, I could almost certainly write a bespoke script to take care of this (without adding a field, if needed), but I felt that since it's such a basic issue when converting between Tables & Excel, that everyone sooner or later will likely run into it, and there was value in a solution at the ArcGIS level.

[JonathanNeal]

@MErikReedAugusta Not necessary to add the fields, or make a memory table. 

I put those there so you can compare the results, just in case, so you don't overwrite the data.

Your idea is still valid, so keep the kudos coming and we'll bring it into a release.