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