class SharePoint(): # Generating Auth Access auth = AuthenticationContext(sharepoint_base_url) auth.acquire_token_for_user(sharepoint_user, sharepoint_password) ctx = ClientContext(sharepoint_base_url, auth) web = ctx.web ctx.load(web) ctx.execute_query() print('Connected to SharePoint: ', web.properties['Title']) # Collecting files names from the folder def folder_details(ctx, folder_in_sharepoint): folder = ctx.web.get_folder_by_server_relative_url( folder_in_sharepoint) folder_names = [] sub_folders = folder.files ctx.load(sub_folders) ctx.execute_query() for each_folder in sub_folders: folder_names.append(each_folder.properties["Name"]) return folder_names # Passing auth ctx and folder path file_list = folder_details(ctx, folder_in_sharepoint) # Reading File from SharePoint Folder and saving it in local. print("Downloading 3P & Subledger file from sharepoint") for each_file in file_list: sharepoint_file = folder_in_sharepoint + each_file file_response = File.open_binary(ctx, sharepoint_file) print(file_response) with open(each_file, 'wb') as output_file: output_file.write(file_response.content) # Downloading the UTC Key file from the sharepoint print("Downloading UTC Key.xlsx file from sharepoint") sharepoint_file = utc_key_file_in_sharepoint + "UTC Key.xlsx" file_response = File.open_binary(ctx, sharepoint_file) print(file_response) with open("UTC Key.xlsx", 'wb') as output_file: output_file.write(file_response.content) # Processing the files and generating the output file. print("Processing the files and generating the output file") import automation # Uploading the output file in the output folder of sharepoint. with open(output_xlsx, 'rb') as content_file: print(output_xlsx) file_content = content_file.read() target_folder = ctx.web.get_folder_by_server_relative_url( '/CP/Shared%20Documents/Automation/Subledger%20Automation/Output/') print( "Uploading the output file in the output folder of the sharepoint." ) target_folder.upload_file(output_xlsx, file_content) ctx.execute_query()
def load_file(filename): # Open the file in temporary memory response = File.open_binary(ctx, str(folder_url + filename)) bytes_file_obj = io.BytesIO() bytes_file_obj.write(response.content) # set file object to start bytes_file_obj.seek(0) global df print(f'\n...Loading {filename}...') if filename.endswith('.xlsx'): dict = pd.read_excel(bytes_file_obj, sheet_name='Sheet1' ) # read the excel file in python as a dictionary df = pd.DataFrame.from_dict( dict) # convert dictionary to a dataframe using pandas return df elif filename.endswith('.csv'): df = pd.read_csv( bytes_file_obj) # read the .csv file in python as a dataframe return df else: file = input("\n*** File not recognised, please try again: ") Load_file(file)
def download_file(self, filename): """ :param filename: name of the file to download :return: download file in current folder """ response = File.open_binary(self.context, f'/{self.folder}/{filename}') with open(f'./data/{filename}', 'wb') as local_file: local_file.write(response.content)
def load_by_relativeUrl(self, relativeUrl: str, **kwargs): ext = os.path.splitext(relativeUrl)[-1] if ext == ".zip": temp = tempfile.NamedTemporaryFile(suffix=ext) _file = (self.ctx.web.get_file_by_server_relative_url( relativeUrl).download(temp).execute_query()) df = pd.read_csv(temp.name, compression="zip", **kwargs) temp.close() else: response = File.open_binary(self.ctx, relativeUrl) df = pd.read_csv(StringIO(response.text), **kwargs) return df
def download(tenant_url, folder, file): file_path = "%s%s" % (folder, file) ctx_auth = AuthenticationContext(tenant_url) ctx_auth.acquire_token_for_user(username, password) # ctx_auth.with_client_certificate(tenant, client_id, thumbprint, cert_path) # ctx_auth.acquire_token_for_app(client_id, client_secret) ctx = ClientContext(tenant_url, ctx_auth) target = "%s%s%s" % (os.path.dirname( os.path.abspath(__file__)), "/import/", file) response = File.open_binary(ctx, file_path) with open(target, "wb") as local_file: local_file.write(response.content)
def download(self, request, *args, **kwargs): sh_file = self.get_object() relative_url = sh_file.properties['ServerRelativeUrl'] response = File.open_binary(self.client.context, relative_url) django_response = HttpResponse( content=response.content, status=response.status_code, content_type=response.headers['Content-Type'], ) django_response[ 'Content-Disposition'] = 'attachment; filename=%s' % sh_file.properties[ 'Name'] return django_response
def readFile( ctx: ClientContext ) -> pd.DataFrame: ''' Reads sharepoint file ''' response = File.open_binary(ctx, env.sp_mppt) #save data to BytesIO stream bytes_file_obj = io.BytesIO() bytes_file_obj.write(response.content) bytes_file_obj.seek(0) #set file object to start try: #read file into pandas dataframe df = pd.read_excel(bytes_file_obj) return(True, df) except ValueError: return(False, '')
def openShiftPlan(creds): ctx_auth = AuthenticationContext(siteURL) if ctx_auth.acquire_token_for_user(creds[0], creds[1]): ctx = ClientContext(siteURL, ctx_auth) web = ctx.web ctx.load(web) ctx.execute_query() print("Web title: {0}".format(web.properties['Title'])) else: print(ctx_auth.get_last_error()) response = File.open_binary(ctx, relativeURL) #print(ctx.service_root_url()) #save data to BytesIO stream bytes_file_obj = io.BytesIO() bytes_file_obj.write(response.content) bytes_file_obj.seek(0) #set file object to start #read file into pandas dataframe return pd.read_excel(bytes_file_obj, sheet_name='Daily - Infra')
def get_data_from_excel(self, relative_url, worksheet_name, header_row, start_row): # adapted from: https://stackoverflow.com/a/69292234/14125255 # (accessed 2021-10-15) ctx = ClientContext(self.conn.site_url).with_credentials( UserCredential(self.conn.user, self.conn.password)) response = File.open_binary(ctx, relative_url) bytes_file_obj = io.BytesIO() bytes_file_obj.write(response.content) bytes_file_obj.seek(0) ws = load_workbook(bytes_file_obj, data_only=True)[worksheet_name] headers = { ws.cell(row=header_row, column=col).value: col for col in range(1, ws.max_column + 1) if ws.cell(row=header_row, column=col) } data = [{ k: ws.cell(row=row, column=v).value for k, v in headers.items() } for row in range(start_row, ws.max_row + 1)] return data
from office365.runtime.auth.authentication_context import AuthenticationContext from office365.sharepoint.client_context import ClientContext from office365.sharepoint.files.file import File import io import pandas as pd url = 'https://eafit.sharepoint.com/sites/Proyectoinformedecoyunturaeconomica' username = '******' password = '' relative_url = r'/sites/Proyectoinformedecoyunturaeconomica/Documentos compartidos/PowerBI/Data/Base de datos.xlsx' ctx_auth = AuthenticationContext(url) ctx_auth.acquire_token_for_user(username, password) ctx = ClientContext(url, ctx_auth) web = ctx.web ctx.load(web) ctx.execute_query() response = File.open_binary(ctx, relative_url) #save data to BytesIO stream bytes_file_obj = io.BytesIO() bytes_file_obj.write(response.content) bytes_file_obj.seek(0) #set file object to start df_embig = pd.read_excel(bytes_file_obj, sheet_name='25.EMBIG') df_ise = pd.read_excel(bytes_file_obj, sheet_name='29.Indice de calidad y cu') df_ise["date"] = pd.period_range('2016-01-01', '2020-12-01', freq='M')
ctx = ClientContext(settings['url']).with_credentials( ClientCredential(settings['client_credentials']['client_id'], settings['client_credentials']['client_secret'])) # list version files <name>.txt under Shared%20Documents/versions Folder = ctx.web.get_folder_by_server_relative_url('Shared%20Documents/versions') files = Folder.files ctx.load(files) ctx.execute_query() version_files = [_file.properties["ServerRelativeUrl"] for _file in files] # Read in the <name>.txt version files into strings versions=[] for version_file in version_files: response = File.open_binary(ctx, version_file) versions.append( pd.read_csv(StringIO(response.text), names=['partner', 'subproduct', 'version_name', 'last_update'])) # Concatenate versions df = pd.concat(versions) expected = pd.read_csv( 'https://raw.githubusercontent.com/MODA-NYC/db-recovery-data-partnership/master/recipes/_data/expected_update_cycle.csv', index_col=False) df = pd.merge(df, expected, on=['partner', 'subproduct'], how='left')\ .sort_values(by=['partner', 'subproduct']) temp = tempfile.NamedTemporaryFile(suffix='.csv') df.to_csv(temp.name, index=False) # Load in the file to file_content