Reading Excel files via Python script

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).

 

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

 

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/

 

@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

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.

Thanks Dimitar!

I’ll have a play around with that Python script

Good to know (:. Opens many possible applications.

Thanks alot.

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

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

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.

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.

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

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!

 

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

Thanks to all for great tips:)