Python Tutorial – Using Python To Interact With Microsoft Excel With Alta3 Research

Hi there I’m Zack feasor an instructor with uLTA 3 research we thought it would be neat to give away a little bit of our Python training course if you like what you see here we offer a 4 to 5 day training session that can probably cover any skillset you might be lacking with Python typically we target organizations so.
You’re part of the fortune 500 you’re right.

In our sweet spot if you’re smaller organization we’re happy to work with you.

Too if you’re a single person and so just like a one-off we offer public classes so I want you to regardless.

If you’re looking for training who are whoever you are go over to Ulta 3com and go ahead and look at the different Python training classes we.

Offer we do more than Python as well telecom and all sorts of fun stuff ansible automation but if you’re watching this you’re probably into Python right so we’ll have beginner classes intermediate classes will focus on Network automation server-side automation whatever you need to.

Do in this video we’re gonna focus on getting Python to talk to excel and sometimes we need Python to read from Excel sometimes we need it to output to excel regardless of what it might be this is.

A very typical this is a there’s a common request for students to give me be there be.

Them network engineers or even server side admins will say Zack I get a lot of my data.

Sent to me say in an email there’s some attachment on there I have a giant spreadsheet that I have to read a bunch of data in or I have to go and search for a bunch of information and produce spreadsheets to send off for some purpose well let’s take a glance let’s start to scratch the surface how we can automate way that sort of responsibility I already have the code written that we’re gonna work with here.

I’m just gonna move into it so if you’re wondering what the heck is leafpad leafpad is just like notepad except it’s available in Linux environments so I’m working in a bun – 1604 right now this script with a few tweaks of work in Windows as well or in your Apple environment right off the bat I see a shebang line here shebang line is teaches a.

Linux environment what sort of well teaches Linux what environment you want your script to run in so Linux doesn’t depend on file extensions like dot py to understand oh I run this in Python so we just.

Say write up the beginning of our script hey Linux throw this over to the Python interpreter we really would prefer to run this in a Python environment not bash or something like that if you’re Windows this won’t do anything unless you do some tweaks install some additional software it’s still best practice to always include a shebang.
Next I want to actually these are.

Three commented lines oh these don’t actually run these are just reminders that this codes not gonna run number one without this sudo apt install Python three pip pip is a tool just like oh we could say the Google Play Store client is a tool on your.

Android phone like we’d say the iTunes Store client is a tool on your iPhone these allow us to go and grab software specifically.

Made for Android device or specifically made for our Apple device what the PIP tool does this allow us to install software specifically to Python there’s a bunch of Python code already written by other people publicly available for free for us to use.

We just have to go get it you have to hunt over to the Python package index just type it into Google Python package index and you’ll find.

A searchable repository where you can find all sorts of free code that’s available to us out in the world some of that code will be PI excel so you install Pi Excel and then you.

Find out oh you install Pi Excel XLS if you want to make XLS compatible spreadsheets you can also create xlsx there’s all sorts of different extensions you can work with but that’s what I want.

To work with in this demonstration because I did this out on the command line I’ve already done that stuff I’ve typed that in on the command line I can then import PI excel those are prerequisites I had packages stuff I had to pull into my system before my.

Script would run import PI excel is saying hey I need to now use that software written by somebody else there’s lines of code written by somebody else pull those in and allow me to work with those in here that’s import PI Excel requests data from user is a comment it doesn’t get run this thing’s called a function and ensure.

A function is repetitious code I don’t want to keep writing I just want a reference like a single line and boom up.

To that block and run that block so white spacing is important in Python.

You can recognize this block because it’s all indented and what is this block gonna do well initially input IP is the result of whatever the user types in to this question what’s the IP address input driver is the result the string value of whatever a user inputs to this question what is the.

Driver associated with this device so the program actually pauses and ask that question we’re creating a dictionary here that we’re saving as the letter D and we’re saying we no its dictionary because we’re using.

Mustache brackets and we’re saying this dictionary contains the key IP and the value input IP which was harvested from the user it contains the key driver and.

The value input driver which was grabbed from the user so if you’re going key values what dictionaries um contain key value pairs where key values the key is static it doesn’t change its what we make the request on and the value is the dynamic.

Piece and it’s super speedy and efficient so you know IP word that’s gonna be a.

Column header just like driver will be a column header and then whatever the user inputs ultimately that’s gonna be under that column and you’ll see that coming up here in just a moment we’re gonna return d then so this dictionary.

That we created we’re gonna return that if somebody calls on this function so if I jump down to my runtime this is all comments that I’m gonna explain anyways.

So don’t focus too heavily on that here’s my code that actually begins running so this is a function this doesn’t really run unless it gets called after my import PI it’s L.

At runtime the first thing that’s gonna go down is I create initialize an empty list square brackets creates a list so my list dict gets created and I print to the screen hello this program will make a start on XLS file that’s cool.

That’s what I want while true I’ve created an infinite loop so what you’re saying here as long as true is true do the following well true is always true so I’ve.

Created an infinite loop this is usually a bad thing but it’s sort of a little trick we can use to just maybe I want a loop to go on for a long time until some conditional pops up.
I have a conditional in here you’ll see in.

A moment but this is the block of.

Code that’s gonna we think executes indefinitely here Milas dicked that’s this empty list we want to modify its attributes so we’re using a little method here the append method and we’re saying what we want to add inside of.

That list is the result of our function get IP data go up to wherever that function is defined get IP data and run the.

Code associated with it so this code runs right there which returns a dictionary which is then going to be appended or added to our currently empty.

Now but it was empty before we then record a string called keep going which is we say hey user do you want to keep going press Q to quit and then we say okay well look if keep going we apply the lower method to say maybe they did a capital Q regardless if it’s if it tests as a little Q break break escapes us from the infinite while loop so it’s not so infinite after all all the user has to do is.

Import a Q to that question input the.

Q to the question and we’re out if not we’re going back to the top the while loop and it’s repeating that would be desire behavior they didn’t give Q so do.

It again in which case we again call the function add more data to our list which will in turn ultimately create a bigger spreadsheet once we do finish up with our loop we say ok file names gonna be what’s the name of the excel file we then go ahead and add XLS and to the end of that there’s I know.

More efficient ways to do stuff like this it’s the way I chose to do it so my excel file is equal to filename which was our string we got up here and then we concatenate or just add into it add to the end of it the string dot.

XLS from the PI Excel library we then say.

Hey we want to save the record Milas dictionary so that’s the list we created and the file we’re gonna save it as is my XLS file so that was the completed string we created here now let me explain to you how the data is gonna show up in our Excel spreadsheet any of our keys so the key IP which is what we use each time that’s a column header just like the key.

Driver is a column header what the user inputs is going to appear underneath of that column so if the user makes two entries dot ten and dot twenty.

Both of those will be under the column IP just like if they put arista eos and Arista a OS and twice as the driver those two drivers will appear under the column header in this case driver this is a very basic example we could create.

Very complicated spreadsheets using pi excel just how I chose to sort of scratch the surface of this particular package and answering this.

Question how does Python talk to excel let’s see when we’re done with that we print to the screen the file should be in the file directory we’ll look and it should be there I’m then going to because.

This is I’m currently trapped in Linux world here we wanted to see this open.

In Excel so I’m gonna mail it to myself for SSH in here and grab the file and I’m gonna open it back in a Windows environment but let’s run it first and then make sure the file actually gets created then all use the the.

Editing magic to suddenly show it to you in Windows I’m gonna close or we’re all.

Saved up no change I’m gonna close their script and I’m gonna go.

Ahead and Python three Excel Oh 1 py let’s let it rip so hey this.

Program will make an excel file that’s what we predicted it would say what’s the IP address we’ll give it something simple one ninety two dot one sixty eight dot zero dot one and that’s a Cisco device and we want to keep going so I’m gonna hit enter and it says again what’s the IP address 10.0 and juniper OS driver will say enter again ten dot 0 dot 2.

In a wrist BOS one more will do ten dot dot and.

That can be another cisco okay I’ll hit the cue to quit and now we’re exiting what’s the name of the excel file how about we’ll do PI 2 X PI 2.

Excel ok the file PI 2 Excel dot XLS should be in your local directory let’s find out LS alright so not only is our script there but we also have PI two Excel XLS I’m gonna go ahead and get this out the windows and then we’ll take a peek at it out in the windows world so here it is I went ahead and took.

Excel the XLS spreadsheet and.

I got it out of our Linux environment here I am in Windows now and exactly what we expected to happen happen so we have an IP header we have a driver header and we have our IP addresses down the side and we have our drivers down the other.

Side so imagine if now we took this data not through human input but we had our script go and read this data from maybe somewhere.

In our network maybe from some other sources who knows a database or something like that imagine we wanted to make a much more complex spreadsheet we can do all of that stuff and if you want to learn more either research.

Yourself nights and weekends right or hop on over to Alta three.com and get new class with me anyways I hope you enjoyed this video if you did go ahead and smash the like button go ahead and subscribe and I hope to see you in a future training class with all the three research.