Get data from Excel with Python

Posted by BrunoJu on March 9, 2018

Aim

Hi guys, as an Chinese Operator in SAP SaaS we often need to upgrade the servers’ firmware from time to time. We get a Exel file from Germany Operation Team within the servers spreading all over different data centers around the world like the screenshot you can see as below: openpyxl_excel

STEP1

They are all Xen hypervisors. The virtual machines can be live migrated to another hypervisor, that’s the one advantage of Xen if you know this virtualization technology well. So we always need to find a spare hypervisor for each one as its destination server with extraordinarily same hardware configuration to migrate its virtual servers and then upgrade the firmware version for the old server. We cannot avoid to do searching for because we must check memory size, network range, CPU model, BIOS settings and so on.

STEP2

Then we need to open our work website. You can see the so-called “GMP” which is our company work website. openpyxl_gmp

By clicking “New Internal Ticket” we can use the name of source server and target server to create a ticket for us to deal with. openpyxl_create_ticket

We want to automate some procedure by writing a script which can get a pair of source server and target server we have written in Excel then open Firefox and create ticket automatically.

Operation

Emm… I decide to use Python to write the script of course. That’s my favorite program language. The first thing came up into my mind was some useful module:

  • openpyxl module: Get data from Excel
  • selenium module: Open Firefox automatically with authorization.

For creating ticket automatically, my colleague used Javascript to do it in the console of Firefox and realized it successfully.
Here is our intact script, you can see it in our GitHub by clicking here.

PS

If you are interested in the module openPyxl, you can see here
And if you want to automate some daily work like this. We are delighted for your business.
Here is my contact information: wenqing.zhu@qq.com