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!!!
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.
If I added the wait node correctly…I got the same error. See attached…it shows the whole script
Can you show the node preview bubbles so we can see what your data actually looks like?
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.
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.
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.
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
Explanation
If we look at the Excel.ExportAsFile 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.
1 Like
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.
1 Like
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. 
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.
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:
3 Likes
I wonder if you use other nodes like @jacob.small mentioned and Data.ExportToExcel, do you get the same behaviour?
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.
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!!
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.
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.
You can promote a 1D list to 2D in either direction using list create or transpose:
2 Likes
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:
extension = fileName.EndsWith(“.xlsx”) ? “” : “.xlsx”;
fullPath = directory + “\\” + fileName + extension;
