Python Download List From SharePoint Using Office365 Rest Package Part 3

what up guys what up what up i am lou and today guys we're going to be adding to our multi-part series video of interacting with sharepoint with python so in this video we're going to be accessing a sharepoint list and the goal here is we want to be able to download that list and as we download a list from sharepoint uh we're gonna save those results right and we're gonna do uh apply two methods to when it comes to saving we're either gonna save it as a csv file or save it as an excel file you know one or the other right so ideally you could do more stuff whether you could save it to database but we're gonna stick for now csv excel right so that's the whole video guys we're going to be accessing sharepoint we're going to be downloading a sharepoint list saving it locally csv and excel but before we get started guys if you give me a like give me a follow man i really appreciate it let me get a lot of love and support from y'all man and i'm thankful for and appreciate it hit that like button and give me a follow alright guys let's get started so again this is part of a project that we're adding on to an existing project so those who are who are new this is your first time watching the video this is probably gonna be part three of the series there you go finding my github account you'll find the source code and where we're adding to it right so you could tell so far we have a download files function and the upload file function so what we're going to end up doing we're going to end up adding another function right in this case we're going to add a function that we're going to call this function get list right one list not list but one list only right so that's ultimately what we want so we're going to end up passing it you know kind of small let me make it bigger just realize it was kind of a bit small all right cool that should be big enough i think yeah um so it is going to take a a argument which in this case would be the list name right so whatever your list name is that's what it's going to end up taking um so one thing i do want to show y'all is i created this very basic simple list in sharepoint nothing special but it's a list that i created and um it's called employee onboarding and then again you got a few columns right you have um the the name which is again meeting meet team meet manager description they complete some dates things of that nature right so again just very basic nothing special but again this is the list and this is the list name if you look at the url just kind of if you know to kind of get more familiar with it you'll see where it says sites the name of the site what your mind called the level meant right the deliverment and then also list and then of course you have the name of the list right employee percent 20 is you know represents space for the url then of course onboarding right so that's kind of what that ultimately represents so dude my screen turning on and off i just i thought that's what it seems like that's odd either way all right guys i actually had some issues i had to reboot my machine so hopefully we don't have these issues again where my screen shares kind of you know getting the the elgato error message so hopefully that's not the case anymore but either way let's go ahead and continue on but this is the list kind of want to share that um so our you know of course our function is going to take a list name right um you know what i need to open up one more thing all right cool so in here we're going to have a connection and again it's going to end up calling our ass to authenticate right so once we do that we're going to go ahead and build our target um get our target list in this case and this would be connect dot web dot list dot get by title right so if this takes an argument the argument here is going to be list name you know which is the argument that we're passing in right so every list name uh next we're gonna pretty much get a um a response back of the list of items the items um would be our list object pretty much what we're getting back so in order to get that back it would be target list items get and then it would be execute query right and then this is where we will do a return return items so really this is it dude when it comes to the to the office 365 api portion of it right our class our sharepoint class we're literally just adding these five lines of code and this would give us back our sharepoint list right so it's pretty straightforward now i'm going to create another a new file in this new file i'm going to call it download list right so in this download list file this is more like a sample code think of it that way where um we're going to be connecting the sharepoint like calling our office 365 um class the class in that file which is sharepoint and then we're going to end up calling the list and do all of that but i'm going to be putting some code in here on um and for for the script to take some arguments and then of course give you the ability to save it in csv or excel right one or the other so that's kind of what we're doing here so let's go ahead and get started and this piece will do from uh let's see office 365 api import sharepoint right so that's the first thing uh next we're going to import systems and then we're going to import csv because again we're going to be exporting to csv and the next thing we're going to import in um it's going to be from um my bad pass lib import peer pass because we're going to be building our directory right our root directory pass with our file name so that's the reason why we bring that in then the final package is going to be import um open py excel um i mean from open py excel import uh workbook so this package we do not have installed yet right now i i do have it installed on my side but um in in the um if you're cloning this repo it would not be initially part of that requirements.txt file so you will have to go ahead and and edit so in this case remember dude i'm going to be talking about this more and more virtual environments if you're not familiar with it get familiar with retro environments strongly recommend to always use virtual environments even if you're just testing out these virtual environments it'll keep your environments clean and prevent issues related to installing packages that could affect other packages so either way but if you don't have it installed go ahead and do pip install open py excel again i already have it installed i don't need to do this but if you don't have it that's what you would do right so just fyi on that piece so now let's go ahead and bring in our arguments right so um let's see argument number one would be the name of the sherp sharepoint list right so this would be called sharepoint list name and this would be system argument uh number one remember when in python whenever you go to the terminal you type in python and then it it's space and then it's normally the file name right now normally it should be the file name that you're trying to run that file name is argument zero index zero because the way python works though there who are new you don't start from one then go up it's not one two three and the starting point is zero and then one two and three all right just for those of you who are new and learning just wanted to put that out there so our second argument is going to be uh let's see it's going to be what i'm going to call export type um it can be excel or csv right so just keep in keep that in mind it could be and this is the format excel slash or csv so this would be export type uh same thing system argument number two alright so now third argument is going to be um local directory pass right so this i'm going to call directory pass um so this directory pass let me kind of notate a local directory pass to save list list data set so since we're downloading on the the list from sharepoint we need to save it somewhere in this case you could be you know save it somewhere locally or some kind of network folder or some sort right but that's kind of what this is um so this would be our argument number three the final argument and this would be um the name of the file to give to name of the file that will get saved on uh local directory right so pretty much this is the file name so i'm gonna call this file name and this would be our argument number four right so now we have all of our arguments um let's go ahead and um let's create our first function the first function that we're going to create here is going to be we're going to call this save to csv right and this is going to take in three arguments it will be list name uh directory pass and file name all right so it takes in three arguments what we're going to do here we're going to do a directory file pass so this variable um it's going to be our full root directory pass which where we're going to save the file to and then also the file name right so it's going to be both so this is where peer pass will come in so we do peer path directory pass and then it would be file name oops file name all right and then this is where we're going to end up using the csv package so what we're going to be doing here is under with open we are going to specify the wiz directory file pass comma right not right b wb just w it's strictly just raw text um let's see new line equals forward slash and we wanted to apply a new line uh special character at the end and then also our encoding would be utf-8 right and this would be f for file that's what we call it right um okay so one of the things that we're going to end up doing here is see how we have the list name object that we're bringing in we're going to end up extracting the header information from there and then save it to our csv file so in order to do that let's call this um header and it will be list name index zero right i want the first index then it would be um properties keys so it is in the form of a dictionary object so what we're doing here is we're getting the first row and then from there we're getting the properties then we're specifying it give me back the keys and think of it as when you're working when you're working with the dictionary those who know who are not familiar with it it's a key value right you have a key name then um [Music] some kind of value associated with this case we're getting back a list of keys only so this is actually returning back um a in a format of a list and it's only specifying the name of each key in the list right so this header is really a list um it's a list object right so that we're getting back so um because of that now we're gonna just call this w and we're gonna call this csv uh dictionary writer um f because this is a file object over here right there we're trying to write to and then i'm gonna say header and then ultimately it's going to end up writing that to our um our file and that gives us the header we're writing the header to our file and then to finish it off i would do right header okay the next thing that i'm going to do is i'm going to call this item um in list name uh did i call this list name you know what this it's not this should be um my bad it's not a list name this is list items because this is the actual object of the list of all the items so let me go ahead and change the name of this list items list items cool right and then also now what i'm going to do here is as we get back a list of every item i'm going to be writing that out and this i would do it right row item dot um properties okay and then based on the that um which is in the form of a dictionary we're gonna be writing that out to our csv file okay and that's it dude this function here by us passing these arguments is gonna take this is the object the list items object this is a pass on where we want to save it to and then this is the file name that we want to give it to save to and then it would do the rest from here so now that we have that we're going to now create another function we're going to call this save to [Music] excel and it will be the same thing list items pass and file name all right now this one will have a few more steps because we're dealing with excel right so just kind of keep that in mind we are dealing with excel here so a few more steps what we end up doing here is we're going to have a directory well you know what let me copy it from the top we are going to have this this will be the same all right so now that we have that piece the next piece is we're gonna call this um this object vb and it's gonna stand for work uh workbook okay so again just kind of going back we're importing in the workbook uh class and that's what we're calling here so based on the class we're pretty much just getting getting a copy of that we're creating a new a new instance for this um object which we're calling wb for workbook the next one is going to be worksheet because in excel you have a workbook you have worksheets right since we're going to be creating a new workbook and a new worksheet by default what we're going to do here is we're going to say workbook active what this means is pretty much saying okay go ahead and give me the the active worksheet in the workbook that we're creating right this is our our workbook instance go ahead and give me the first worksheet in that workbook and by doing by doing in order to do that it'll be wb.active and that would be our worksheet ws worksheet right um so the next thing let's call this um list of header names from sharepoint list so this is where we kind of what we did up here get our header our our list items right property this is our header information so we're kind of doing something similar like so let's go ahead and copy this because this will actually be the same we do need this right now we have a header list um the next thing is going to be uh right headers on first row all right and in order to do that we will go ahead and do for so before i do that um i guess let me do it before index name in enumerate um header okay so if you're not familiar with enumerate it works the same way i just iterate over a list the difference is it gives you a index value and again the starting index is always zero so think of it as a sequencer it allows you to get a sequencer as you iterate through your list that's pretty much what that does so in this case i'm going to be calling my ws my worksheet cell because now i'm going to start specifying a cell that i want to write data into right so the first one is going to be row and this would be a row one because my row one is going to be my header uh the next one is gonna be column and column this is where it's available it's gonna be it's gonna be moving from one column to the next so the way i would do this is this is where enumerate comes in because i do have this index value think of it as a sequence number which starts at zero and it goes up this is where i will call that and i will do plus one because i really wanted to start at one or zero because again excel does not does not have zero um index placements in excel it starts at one but in python it starts at zero so in order to make them be equal have to add a one and it will increment up right it'll be column one two and three and so on when it comes to writing and then of course the next one is gonna be the value what am i gonna write in the cell and in this case it would be the name that's where the name comes in right here so this would write our header to our excel file that's exactly what that's doing right um so now the next thing is going to be um write details starting um or write line items starting on a second row so we don't want to write in the first row because the first row the header we want to write this on the second row so what we're going to do here i'm going to call this row two so instead of hard coding one like the way i did over here i'm going to actually i have this object called row and it's going to be starting at 2 now i'm going to have that increment up as we kind of iterate to the process right that's how we kind of get it to go up so now i'm going to do my iteration and i'm going to call this uh dictionary object in a list items and then from here because what i'm doing is i have um a list of the objects inside like dictionary objects let's think of it as a list and inside that list instead of having values i actually have dictionaries inside and after so now i got to extract data from those dictionaries to be able to write to excel so that's kind of what i'm doing here so say this will be the same concept it will be index item in enumerate dictionary object dot um properties dot i uh items okay and this will give us our our items um and then same thing right worksheet cell row equals row right instead of row equals one in this case broke the row which is this guy over here and then it would be column equals index plus one because again we're starting a different rope and we still wanted to go across and and uh set all of the values which in this case would be item um then it would be index one right so index one and the reason why we're doing x and x one this is really a tuple uh when we get item believe it's a tuple and it has the first index 0 is the key name then the second index like in index 1 is the actual value that's why we're doing it this way and then i'm gonna add over here row plus equals one this will make it increment up and then at the very end of the process we'll do work book save uh directory file pass and this is the ultimately this we want to save it under this path and this file name to save this object that we have so that's kind of what we're doing here um okay so the next thing that we're going to need to do here is we will need to we're going to create another uh function we're going to call this set file x uh file extension so because one of the arguments is file name we're not going to pro we're not going to specify here the kind of extension that it needs we're just saying for the file name and based on the export type we're going to use this function to determine which file extension it will need and ultimately this will build that new file name for us so that's kind of what we're doing here slightly different i mean maybe more than what it needs to be but you know just to make it a bit easier so the logic would be export type equals um excel and if that's true then we will call this uh file name with extension equals dot join again join takes a list argument and inside the list you could put many values to join so in our case it will be file name comma dot excel x alright the next one will be if export type equals csv then it will be the same thing but the difference here is going to be csv i mean yes it's feed for the extension then of course it becomes else file name um [Music] with extension just equals file name so if they if they don't specify excel or csv it would just spit back whatever file name they gave right and that would be the um what gets assigned to this new file name with extension all right and then finally we're going to create one more last function and this function would be to be able to download list and this is going to take the list list name export type uh directory pass and file name pretty much taken all the arguments right it's what this is doing and inside here this is what we would call this um let's call this sp list and this would be sharepoint um [Music] get list and it would be the list name and that would return back our sharepoint list and then this is where we would have logic same thing will be if export type equals excel right if equal excel we're going to call the save to excel function that we created and this is going to take the sp list the directory pass file name and that's it and then we're gonna have if um export type equals csv it would be the other function that we created save to csv so it'll be the same thing as p list directory pass file name uh and then we're gonna do an else and this we're just gonna print you know export type is not a valid type which means whatever they typed in is not valid and this is pretty much it guys now in order to to run this these functions and run everything we are going to put in the toward the bottom of our file we're going to create a if statement uh name equals so by doing this this would allow to run this code that we're about to add so the first thing we're going to be doing we're going to in order to get our new file name with extension we're going to create a new variable a new object called file name um it would be set file name with extension and in this case we're gonna call file name it's gonna be one of our arguments and export type right and then after that we're gonna end up calling our download list function and again this we're gonna be sharepoint list name uh variable export type directory pass and then the new file name not the uppercase one but the lowercase one which is this guy and really this is it guys this is pretty much it the only thing that we need to do now is go ahead and run it the only thing that uh we need to do now we need to test it out right let's test it out to um make sure everything works so what i'm gonna do is i have a folder my download i think what did i call it what the hell let me find it real quick uh with the files here with files boom so we're gonna do a test and we're gonna save it and here in this folder right so let me go ahead and copy this and ultimately file should get saved in there if everything works successfully um what i'm going to need to pass in as my arguments is let me check something out i want to make sure my uh you know what it is i have all my credentials and everything in my env file so it should be good so i'm going to pass in my sharepoint name so let me go ahead and open that up real quick so this is my sharepoint list name right so my case called employee onboarding and this is what i'm bringing in all right so we have that export type we're going to do we'll try both excel csv directory pass is where i want to save to which in my case i'm going to save it here to this folder and then of course file name whatever file name i want to give it so let's go ahead and [Music] test this out um so let me go ahead and um what is the uh youtube [Music] python sharepoint there goes this is it let me go ahead and activate my environment all right cool so now we're going to go ahead and run our whatever download list py file right so it will be python download list py again our first argument is going to be sharepoint list name in our case employee onboarding right is that what it's called uh employee on a boarding yep some put that in double quotes our second argument is going to be excel export type so let's go ahead and do csv first right we'll do csv uh third will be directory pass again this is where i want the files to get saved to and then fourth is gonna be file name so i'm gonna call this for now um employee onboarding um export right that's all so again those are my arguments um list name export type where it's going to get saved to you right my directory pass and the file name so if i hit enter let's cross finger see if it works all right no errors let's go to our folder boom we got a file here csv let's open this bad boy up boom there you go so this ended up taking so also keep in mind when you look at it these are the main columns that you see there are other columns that exist for the list that you don't you may not see for example uh create a date modify date right um what else this grid id right so there you do got these values that just may not necessarily you may not see visually but there will be part of your extract and of course you have all your main files your main column that you see like work this this column is called work but in the table in the list is actually called title and it's you know these are your values your ids for each record um attachments true or false like this guy has an attachment but this one does not right true and false uh description their description over here so again you do have everything and plus a little extra some other information but again it did what we wanted to do right it exported out to csv now let's go ahead and try it again but let's the difference is we're gonna give call it the same name but the difference is we're gonna we want the export type to be excel instead of csv so let's go ahead and run it all right no errors that that's good so now we have an excel file go ahead and open this bad boy up and boom same thing right it looks completely the same it just one's excel one csv that's the only difference so again this kind of gives you the core of again we're adding on to our office 365 api uh um file my main goal with this file is i want to have a lot of the core uh functions uh that we would do like use to interact with sharepoint which is upload file download file um which would be also upload list like create list you know download list um you know get you know file url like shareable link like all of these stuff that you know probably comes to mind where we could like pro have some kind of script to automate the process that's where i'm going to be adding on to this because that's that's the main goal and purpose was the video that we're doing and of course this download list file is more like a sample of i probably it may be a little bit more than a traditional sample but i try to build it where i know some of my viewers can actually use this file like they if they really want to they could literally take this file as is and start using it just by providing the arguments like the way i did right past the argument i'm not hardcoding nothing and these are all just arguments that we're passing in they could literally just take that and throw it in their system and have it run and it's good to go so i'm trying to add some i'm trying to give you a sample how to use it but i'm trying to add some um you know try to build the code where it's reusable very easy out of the box as well so but again it worked guys that did the job it downloaded file from our list and did exactly what i wanted to do again guys man i appreciate it for watching appreciate y'all for um following me showing support hopefully this video helps helps y'all i will be adding on how i mentioned this is a multi-part series video where i'm going to be adding more and more um functionality to uh the office 365 api file you know so just keep that in mind but hopefully this helps you out and the the end goal result would be one file to bring in to ultimately allow you to do x y and z download upload create so you know the list goes on and on with sharepoint stuff so again guys appreciate it give me a like give me a follow appreciate all the support and hopefully this helps you out peace

Channel: I am Lu

Views: 4,496

Keywords:

Id: Uyhg6ccqUvM

Length: 37min 59sec (2279 seconds)

Published: Tue Aug 09 2022