# Export Data to Excel error and strange output

**URL:** <https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171>\
**Category:** Revit\
**Created:** [April 17, 2024, 4:50pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171 "2024-04-17T16:50:21Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![lashaw13](https://avatars.discourse-cdn.com/v4/letter/l/cc9497/32.png) [@lashaw13](https://forum.dynamobim.com/u/lashaw13)\
**Post date:** [April 17, 2024, 4:50pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/1 "2024-04-17T16:50:21Z")

</div>

Hello Forum Folks! Hope you’re all having a good week so far!

So I’m trying to export my door numbers (in numerical order) into an excel spreadsheet and something strange is happening. I was hoping someone could shed some light on this. The script has a dialog box for the user to insert the directory path where the file is to be saved. In the first image the door numbers are sorted and input into the Export Node. I get an error that the path is null. However, in the second image the data is feed directly from the Filter by Bool mask node and the path works…export is successful. But then the excel file is not in numerical order and each digit of the door numbers are in their own columns…which makes sorting them afterwords impossible without a lot of cell manipulation.

Please share some thoughts! THANKS!!!

 ![Export Door Numbers to Excel_null-](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/1/1/11abd04b7795152cc6fe8dc9b0dddcd08cac21c4.png)

 ![Export Door Numbers to Excel_work](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/8/f/8f4db3947170e193cafedde86daf105f28114fc6.png)

 ![excel](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/2/2/222988ead482d93296c262f2f886677b197de605.jpeg)

---

<div class="post-metadata">

**Author:** ![Nick\_Boyts](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/nick_boyts/32/76313_2.png) [@Nick\_Boyts](https://forum.dynamobim.com/u/Nick_Boyts)\
**Post date:** [April 17, 2024, 5:33pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/2 "2024-04-17T17:33:30Z")

</div>

Hard to say without seeing the node previews, but it sounds like your export node is executing before the UI has accepted the input value. Try using a `Wait`/`Passthrough` node to force the data to pass _after_ the directory path exists.

---

<div class="post-metadata">

**Author:** ![lashaw13](https://avatars.discourse-cdn.com/v4/letter/l/cc9497/32.png) [@lashaw13](https://forum.dynamobim.com/u/lashaw13)\
**Post date:** [April 17, 2024, 7:16pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/3 "2024-04-17T19:16:15Z")

</div>

If I added the wait node correctly…I got the same error. See attached…it shows the whole script

 ![Export Door Numbers to Excel_passthrough](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/7/5/7590acf41009414f57b8493290673fcd5637e1eb.png)

---

<div class="post-metadata">

**Author:** ![Nick\_Boyts](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/nick_boyts/32/76313_2.png) [@Nick\_Boyts](https://forum.dynamobim.com/u/Nick_Boyts)\
**Post date:** [April 17, 2024, 8:17pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/4 "2024-04-17T20:17:17Z")

</div>

Can you show the node preview bubbles so we can see what your data actually looks like?

---

<div class="post-metadata">

**Author:** ![lashaw13](https://avatars.discourse-cdn.com/v4/letter/l/cc9497/32.png) [@lashaw13](https://forum.dynamobim.com/u/lashaw13)\
**Post date:** [April 17, 2024, 8:37pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/5 "2024-04-17T20:37:46Z")

</div>

![Export Door Numbers to Excel_with previews](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/b/1/b146ef3ae0e4b38d00436d274eca59052bc2f6b1.png)

---

<div class="post-metadata">

**Author:** ![Nick\_Boyts](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/nick_boyts/32/76313_2.png) [@Nick\_Boyts](https://forum.dynamobim.com/u/Nick_Boyts)\
**Post date:** [April 17, 2024, 9:03pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/6 "2024-04-17T21:03:18Z")

</div>

Try putting a `List.FirstItem` node between your UI output and the wait node. Because the UI input gets returned as a list, it may be accepting an empty list before the list contains the input value.

If that doesn’t work, post a version of the script with some fake data so we can test it.

---

<div class="post-metadata">

**Author:** ![lashaw13](https://avatars.discourse-cdn.com/v4/letter/l/cc9497/32.png) [@lashaw13](https://forum.dynamobim.com/u/lashaw13)\
**Post date:** [April 17, 2024, 9:49pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/7 "2024-04-17T21:49:43Z")

</div>

Ok…so that didn’t work. I made a fake list of data, took out the pass through, and it worked…although the data in excel still has each digit of the numbers in it’s own column but they are in numerical order.

 ![TEST - Export Door Numbers to Excel](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/3/2/32702e5a62a4915deca08a796ccaa09b06099991.png)  
 ![excel_2](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/f/c/fc89aa467db54c29bee22e1fc7fd1226a0c2caf6.jpeg)

---

<div class="post-metadata">

**Author:** ![jacob.small](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/jacob.small/32/161030_2.png) [@jacob.small](https://forum.dynamobim.com/u/jacob.small)\
**Post date:** [April 18, 2024, 12:46am UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/8 "2024-04-18T00:46:42Z")

</div>

Strings are iterable in Python, so it’s sending each character as it’s own value for a cell rather than sending the total string length as the value for a cell.

Run your strings through a List.Chop node to get the data structured as a list of lists of strings and see if that fixes it.

---

<div class="post-metadata">

**Author:** ![Mike.Buttery](https://avatars.discourse-cdn.com/v4/letter/m/b19c9b/32.png) [@Mike.Buttery](https://forum.dynamobim.com/u/Mike.Buttery)\
**Post date:** [April 18, 2024, 12:57am UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/9 "2024-04-18T00:57:32Z")

</div>

> [@lashaw13](#):
>
> excel still has each digit of the numbers in it’s own column

TLDR;  
This is due to the code assuming that the input is a list of lists, as a string is technically iterable it sees it as a list of characters and inputs it as such

You could try other Excel export nodes or _List.Map_ with _List Create_  
EDIT - Updated after @jacob.small 's comment

 ![image](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/0/1/0156bc884bd105a0cac77b9a1e4e2bfeaba419ea.png)

Explanation  
If we look at the [Excel.ExportAsFile](https://github.com/aussieBIMguru/Crumple/blob/master/Crumple/dyf/ExcelExportAsFile.dyf) custom node python - this line of code `nbr_colum = len(self.lstData[0])` in the `ExcelUtils.exportXls` method is setting up the number of columns in the array for exporting to Excel

There are a few things to note here - firstly there are many Python objects that have length, like strings and dictionaries - any object that has a ` __len__ ` method.

There is no type checking in the code - so there are assumptions being made on the inputs.

The code assumes that all of the inputs are of equal length or at least the item length at index 0 is a proxy for all data in the array. If the item at index 0 is not the longest then data will potentially be cut off as the code creates an excel Range based on the value, yet iterates over all the available data.  
This could be avoided by using `nbr_colum = max(map(len, self.lstData))` instead.

---

<div class="post-metadata">

**Author:** ![GavinNicholls](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/gavinnicholls/32/190442_2.png) [@GavinNicholls](https://forum.dynamobim.com/u/GavinNicholls)\
**Post date:** [April 18, 2024, 2:51am UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/10 "2024-04-18T02:51:09Z")

</div>

Got it in one, that’s the Crumple excel node which only takes list of lists given it’s the only suitable data structure for Excel (at least without merged cells, multiple sheets/docs).

Whilst in some nodes I build in data structure fixers, it would impact the flexibility of the node in future. At some point users must learn what a good/bad data structure is, even if just through seeing a bad output as the means of learning.

---

<div class="post-metadata">

**Author:** ![jacob.small](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/jacob.small/32/161030_2.png) [@jacob.small](https://forum.dynamobim.com/u/jacob.small)\
**Post date:** [April 18, 2024, 4:10am UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/11 "2024-04-18T04:10:29Z")

</div>

Why not set the input node to `dataRows: var[]..[]` then? Doesn’t force editing the Python but it will ensure the warning to help users learn. 🙂

---

<div class="post-metadata">

**Author:** ![GavinNicholls](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/gavinnicholls/32/190442_2.png) [@GavinNicholls](https://forum.dynamobim.com/u/GavinNicholls)\
**Post date:** [April 18, 2024, 5:02am UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/12 "2024-04-18T05:02:48Z")

</div>

My input prompt is set to that, but people can still send in lists of strings into that I believe, and it would try to iterate through them by virtue of the fact strings are iterables. I generally use that for input type unless there is a clear data type the node expects. I’m fairly sure my tooltip on the input, if not the node description indicates the list of lists assumption.

In the node body itself I just take whatever the user provides, so a list of objects vs lists would be accepted by `var[]..[]`. In some nodes I check if an object is a list to force iterables (then return them to 1 object if they began as that), but list of lists, even lengths going into zip loops and other deeper structures I tend not to try to fix for the user, with my family document nodes being the current exception to the rule (replicate uneven list lengths based on familydoc count input to force longest, as well as enforce list of list structures from the input if only a single item or list is provided).

I’m familiar with recursion as a way to handle unpredictable list structure, and even have a node in Crumple to demonstrate this for education purposes, but it doesn’t make for legible/understandable code and would be a headache for me to maintain.

---

<div class="post-metadata">

**Author:** ![jacob.small](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/jacob.small/32/161030_2.png) [@jacob.small](https://forum.dynamobim.com/u/jacob.small)\
**Post date:** [April 18, 2024, 7:31am UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/13 "2024-04-18T07:31:11Z")

</div>

AH! I see the issue now. Totally get the problem here - @solamour @emrullah.yildiz we may want to discuss ways to ‘force’ a list structure for an input as this could be a blocker for other custom node endeavors.

@lashaw13 you may want to try the out of the box OpenXML nodes for now - they ensure the list structure by a C# mechanism so you get the results you’re after:

 ![image](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/d/8/d8724d3430b2b92f761a975a5c5120774935ec0e.png)

---

<div class="post-metadata">

**Author:** ![emrullah.yildiz](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/emrullah.yildiz/32/177572_2.png) [@emrullah.yildiz](https://forum.dynamobim.com/u/emrullah.yildiz)\
**Post date:** [April 18, 2024, 10:26am UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/14 "2024-04-18T10:26:36Z")

</div>

I wonder if you use other nodes like @jacob.small mentioned and **Data.ExportToExcel** , do you get the same behaviour?

---

<div class="post-metadata">

**Author:** ![emrullah.yildiz](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/emrullah.yildiz/32/177572_2.png) [@emrullah.yildiz](https://forum.dynamobim.com/u/emrullah.yildiz)\
**Post date:** [April 18, 2024, 10:31am UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/15 "2024-04-18T10:31:28Z")

</div>

Yeah, this is interesting. I have never encountered with an issue of iteration over String data while exporting a data to excel. Worth to investigate.

---

<div class="post-metadata">

**Author:** ![lashaw13](https://avatars.discourse-cdn.com/v4/letter/l/cc9497/32.png) [@lashaw13](https://forum.dynamobim.com/u/lashaw13)\
**Post date:** [April 18, 2024, 11:49am UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/16 "2024-04-18T11:49:55Z")

</div>

I did use this node as a workaround…but I wanted to be able to input a directory path and have the excel node create and save the file to the path…not pick an actual excel file. The way our file structure is setup we would have to create a dummy excel file in the project files. We will only use this for larger projects not every project.  
Thanks for the comment!!

---

<div class="post-metadata">

**Author:** ![GavinNicholls](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/gavinnicholls/32/190442_2.png) [@GavinNicholls](https://forum.dynamobim.com/u/GavinNicholls)\
**Post date:** [April 18, 2024, 12:01pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/17 "2024-04-18T12:01:33Z")

</div>

My guess is the nodes have some safeguards in them versus my custom node being used here (which makes a new excel file as @lashaw13 mentioned). My node does a 2 level loop to write out the data, so if it gets a list of strings it iterates over each string as it treats it the same as a list/row of data it expects otherwise.

Unintended behavior, but Pythonically possible.

---

<div class="post-metadata">

**Author:** ![lashaw13](https://avatars.discourse-cdn.com/v4/letter/l/cc9497/32.png) [@lashaw13](https://forum.dynamobim.com/u/lashaw13)\
**Post date:** [April 18, 2024, 12:05pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/18 "2024-04-18T12:05:09Z")

</div>

I really appreciate all of your comments!!! But my script still isn’t working…I added the List Chop into the Test file with fake data and it did produce the excel info as I wanted! so bravo @jacob.small  
However, I went back to my script with the actual data and the it still gave me the path error even with the wait node.

 ![Export Door Numbers to Excel_with chop](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/0/b/0b278f61a513a959b097f14f2dff59940264c129.png)

---

<div class="post-metadata">

**Author:** ![GavinNicholls](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/gavinnicholls/32/190442_2.png) [@GavinNicholls](https://forum.dynamobim.com/u/GavinNicholls)\
**Post date:** [April 18, 2024, 12:11pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/19 "2024-04-18T12:11:58Z")

</div>

You can promote a 1D list to 2D in either direction using list create or transpose:

 ![image](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/6/5/65f7b3e3ebbb0dee0b595ff6f674311e99696543.png)

---

<div class="post-metadata">

**Author:** ![jacob.small](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/jacob.small/32/161030_2.png) [@jacob.small](https://forum.dynamobim.com/u/jacob.small)\
**Post date:** [April 18, 2024, 12:42pm UTC](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171/20 "2024-04-18T12:42:30Z")

</div>

> [@lashaw13](#):
>
> I wanted to be able to input a directory path and have the excel node create and save the file to the path

Make a string input for he name, and a directory input for the file. Then use a code block to merge them together. Something like this should work:

```auto
extension = fileName.EndsWith(“.xlsx”) ? “” : “.xlsx”; 
fullPath = directory + “\\” + fileName + extension;

```

[Next page](https://forum.dynamobim.com/t/export-data-to-excel-error-and-strange-output/100171.md?page=2)
