Read multiple excel file


#1

hi,

i can’t figure how to import XlsxWriter in the Python inside Dynamo, so im using BumbleBee Package.
what im trying to achieve is to to read multiple file from a directory, easy for one or few files. but what about hundreds? is there a way to real ALL excel file from a single directory? Would BumbleBee crash or i might better use only Python coding for that much file?


#2

Python would probably be better but it depends on how much you’re doing. Give it a try with BumbleBee and see what happens.

Use Directory.Contents to get the files in your directory then filter out the Excel documents. Then you should be able to run them all through the Excel node.


#3

thanks!
so i manage to use content directory, but still use a simple python code to extract only xlsx files


#4

ok…it jammed bumblebee.


#5

Not surprised, but worth a shot.
I don’t know what your end goal is, but you could have your script run on a set number of files instead of all at once. But again, if you’re looking at hundreds of files you’re probably better off with Python. I’m curious as to what you’re trying to do though.


#6

Just to read data from multiple excel files, then use the values to override parameters on Revit elements.

Why? Because Excel is still better for some task.


#8

To my understanding, Bumblebee is already more time efficient for reading Excel and it is Python based. I’d imagine writing your own Python script would still take a fair amount of time with hundreds of files.

If your end goal is to read Excel for use with overriding Revit parameters, I would set up my Excel files in subdirectories or with prefixes indicating the Dynamo workflow they correspond to (maybe Revit category). I’m not sure if there’d be much of a benefit to load in hundreds (or even dozens) of Excel files and their data to update Revit parameters all at once, in comparison to updating in separate runs… modifying your Revit data in batches would also prevent error (i.e. it would be easier to miss any mistakes after running a graph that changes hundreds and hundreds of elements all at once)

By the way - there is no need for your Python after the Directory Contents node - use the searchString input with " *xlsx " and it will filter your directory contents :slight_smile:

(PS Apologies for the deleted post - I had replied without thoroughly looking at your first image.)


#9

THAnks for the tips!

You right about the update process.

Im thinking to concatenate mutltiple file in a single file. I just need some data, not all, so the update would be easier to Revit. How about using Panda+Python?

Maybe its overkill, but there is hundreds of data in excel file that needs (from time to time) to override some parameters in Revit.


#10

Sounds like significant overkill… As @awilliams mentioned, doing this in batches will help confirm that the data is moving correctly each time you hit run. Not saying you need to do it in batches of 1, but batches of 10 is better than batches of 100. Have you considered utilizing type catalogs? What types of data/families are you updating?

Also, no need to fight bumblebee or python - I’d just use the native excel node for this since you’re going to need to push the data out of the python node into dynamo to do the Revit edits anyway. Unless you were you planning on somehow reading the element types, parameter names, parameter storage types, and parameter values all from the same excel file, and then setting the family type values accordingly, for 100000s of elements across 100000s of family types, dozens of categories and the like all based on the 100s of excel files you’re reading… In which case I tip my hat to your bravery, and advise you to make your iteration work correctly from end to end for one set of families in one excel file before you move onto the list of every family in many excel files.


#11

it’s shared parameters for room. 5-6 shared parameters, and over 2000 rooms.
each is by instance.
there is no database, only excel files. that why im thinking to assemble just the data i need in a single excel, then use those data in Revit.


#12

That’s the way I’ve seen it done in the past. One column for room number, one for room name, one for each other parameter… and you’re off to the races. No need for bumble bee or other high end racing gear just yet. :wink:


#13

Ok guys i find a way.
just want to share it with you.

i did a loop through my 850 Excel file with Openpyxl, and retreive only the cells i need.
then create a new Excel file. note that the pyhton code is outside Dynamo, because it seems some module like OpenPyxl is not available in the Dynamo working space.

From the new Excel file, its easy to upload parameters through Dynamo.

Y


#14

morning,
Any chance you can share the code?


#15

I will do a tutorial.