Reading Excel files via Python script


#1

Hi All,

In my Dynamo script I am importing quite a few Excel files with an array of different sheets. With the amount of Excel.Read nodes, I wanted to know if there is a way to combine it all into a Python Script? Where the Python node can retrieve multiple sheet data and output into the various streams.

Keen if anyone knows of tutorials that I can use to familiarize myself with Python script more (in context of Dynamo).

 


#2

Danny, actually i would suggest to read the excel files using custom nodes and then forward them to a python node for processing.

But maybe you better describe what you have in mind

 


#3

I don’t think you can read excel files through the built-in python. You might need to import an external solution. Maybe something like this? https://openpyxl.readthedocs.org/en/latest/

 


#4

@Peter, Just thought there might be an alternative to the Read Excel nodes. Since I have 4+ Excel files being read in a Dynamo file, so it means 4 Read Excel nodes. I was wondering if there was a Python script method that will let me call multiple Excel files. I will be trying to do a lot of the processing in Python script, since the project file I’m working on is quite large, I want to reduce the amount of processing as much as possible.

@Dimitar, thanks for the link, I will give it a test


#5

Hi Danny,

It seems I mislead you. Dynamo uses IronPython and not Python and thus it is not compatible with openpyxl. However IronPython can handle excel files natively:

http://www.ironpython.info/index.php?title=Interacting_with_Excel

2014-12-09_123530

 

 

 

 

 

 

 

Hope that puts you on the right track.


#6

Thanks Dimitar!

I’ll have a play around with that Python script


#7

Good to know (:. Opens many possible applications.

Thanks alot.


#8

I have same problem, but that solution isn’t working for me. I have an error: “ImportError: No module called Office”. Can someone help?


#9

Rade, keep in mind that you have to sset your python paths to where your libraries reside!


#10

I’m beginner, don’t know how,I’ve been searching for hours, and found some xlrd libs but don’t know where to put them to work. Dynamo has some interop.dll, but I don’t know how to call that from Iron Python running from Dynamo.


#11

Good call Dimitar, but remember to realease the COM object when you are done with reading it or you will have some trouble later. :slight_smile: Remember that you are instantiating a new Excel Application when running this code so it will have to be closed/released. Try CTR+ALT+DEL and see how many Excel apps you have open. Reading isn’t too difficult but writing gets a little touchy about this.


#12

Good catch, Konrad. I didn’t notice that when testing because I only ran it two - three times. Rookie mistake :slight_smile:

If anybody’s trying to use the above code, be sure to add “ex.Workbooks.Close()” after you’re done extracting the info from excel, otherwise a few hidden instances of excel will be eating up your ram.

2015-04-16_10-25-06


#13

Hahahah, yes, and no. ex.Workbooks.Close() will only close the visible/hidden instance of Excel app. There is still a COM object running in a background for every worksheet, workbook and excel app. The proper way to make sure that you really closed and released all COM objects is to do the following:

xlApp.ActiveWorkbook.Close(False)
Marshal.ReleaseComObject(xlWorksheet)
Marshal.ReleaseComObject(xlWorkbook)
Marshal.ReleaseComObject(xlApp)

You see how I am dumping not only the Excel Application but also a COM object for a Worksheet and a Workbook. This makes sure that there isn’t anything left open in the Task Manager.

Good luck!

 


#14

Once again thanks for the great tip, Konrad. I’ll try to do my homework better next time :slight_smile:


#15

Thanks to all for great tips:)