Read & Load multiple Sharepoint/OneDrive Excel Files into Dataframe

Viewed 13

I have several files saved on my Desktop (Windows) in a folder. My desktop files are synchronized with OneDrive to my corporate account.

If I want to load an Excel file (.xlsx) into a dataframe with pandas I use the following:

import pandas as pd
import io
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File

username = "username"
password = "password"

file = "https//company.sharepoint.com/.../file.xlsx?web=1"

ctx_auth = AuthenticationContext(file)
if ctx_auth.acquire_token_for_user(username, password):
    ctx = ClientContext(path_kst_jan_22, ctx_auth)
    web = ctx.web
    ctx.load(web)
    ctx.execute_query()
    print("Authentication successful")
    
response = File.open_binary(ctx, file)

# Save Data to BytesIO stream
bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0)

# To read & load the file then
df = pd.read_excel(bytes_file_obj)

This works perfectly for one file. What I want to do though is to load multiple files "file1", "file2" ... and append/concat the files to get one single dataframe including all content from the files. Is this possible with this approach?

0 Answers
Related