Excel Producing False Null Values

Hey Guys totally new here! I have 2 seemingly identical excel files. 1 reads and creates lists perfectly in Dynamo. The other reads the same set of information but returns a seemingly arbitrary number of null values after reading the values intended. This is cause issues because I can’t feed null values into my next node. Any ideas what in excel would be causing these nulls to generate?

Hey, some screenshots would probably be useful here…

I think these are just empty cells in excel, before or after the cells with data. You can use one of the remove/replace null nodes from Lunchbox or Clockwork:

1 Like

@aclark - What versions of excel and Dynamo are you using? FYI @Neal_Burnham

Reminder… i’m brand new and using scripts I can find and understand the output to sort of reverse engineer and learn. I did not create this script.

The first screen shot attached I’ve patched in a watch node and you can see the number of returned values is 1700 and change with loads of nulls.

The next is the exact same script with a working spreadsheet. Same column layout and naming, same values, everything the same as far as I can tell. Also you can see the list repeats, as it should, where the failing excel has nulls and then repeats. also, value count is WAY down.

I’m in Revit 2015 and Dynamo 1.2

something about just removing the nulls kills some of the excel column reading further down the script

As a workaround you could replace the null values with a dummy value and filter the lists by that dummy value.

What version of Dynamo are you using?

Thought about that… I just don’t know what is producing the null. Is there a “find and replace” node that can help with this?

Could you also show us the list of values directly extracted from Excel (before the List.OfRepeatedItem node)?

You can use manage.replacenulls from Lunchbox for finding and replacing values
Something like this:

1 Like


And I suppose you used an Excel.ReadFromFile node before List.Transpose?

oh yeah… it’s a whole process, again I can produce a clean result with a specific excel file. but I don’t know what it is that’s keeping that file clean. the “(TESTER)” file is the one producing the nulls

ATCIC Program (TESTER).xlsx (15.6 KB)
Space Planning_ATCIC.xlsx (16.4 KB)

@aclark Try this file For Clark.dyn (7.4 KB)


@aclark - Can you tell me what excel version you are using?

so this is awesome, and interesting… when i run my functioning excel file i’m getting 550 values returned and your cleaned versions are returning 495… This is awesome, let me patch this in and see if it cleans up the issues.

excel 2016

@aclark Report back here and if it is solved make sure you mark the post as solved. Thanks :slight_smile:

1 Like

You guys!!! you did it… I patched in the list.clean node that @Kulkul provided and it works!

1 Like