idea Table to Excel - Null values in ArcGIS Pro Ideas

Table to Excel - Null values

MErikReedAugusta — Wed, 29 Nov 2023 17:42:03 GMT

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.

Re: Table to Excel - Null values

JonathanNeal — Wed, 29 Nov 2023 21:16:56 GMT

@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")

Re: Table to Excel - Null values

MErikReedAugusta — Wed, 29 Nov 2023 21:30:38 GMT

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.

Re: Table to Excel - Null values

JonathanNeal — Wed, 29 Nov 2023 21:45:39 GMT

@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.