Downloading and Modifying Files from SharePoint

Introduction

• Presenter: Lou
• Topic: Downloading a file from SharePoint, modifying it using Pandas, and re-uploading it back.
• Focus: The process is done in memory without saving locally, suitable for AWS Lambda or Azure Functions.

Overview of the Process

1. Download the file from SharePoint.
2. Modify the file using Pandas.
3. Upload the modified file back to SharePoint.

Setting Up

• Get started: Hit the like button and follow on Twitch for hardware-related content.
• Code Preparation: Already have code ready; no need to start from scratch.
  • Access code from GitHub repository: imlu coding -> python sharepoint office 365 api.
  • Video tutorial available: "Python download SharePoint files part one" for configuration guidance.

Code Modifications

• No need to save locally: The goal is to keep files in memory.
• Code Adaptations:
  • Remove local saving references from arguments.
  • Use BytesIO to manage data in memory.

Downloading the Excel File

• Function to get files: Utilize existing download function to retrieve the file object.
• Excel File Handling with Pandas:
  • Install Pandas if not already done.
  • Import: import pandas as pd

Modifying the File

• Creating Upload Function:

  • upload_file: Takes arguments for file name, folder name, and context (file content).
  • Use existing SharePoint upload function.

• Modification Logic:

  • Define modify_file function to handle file modifications.
  • Read the file content into a Pandas DataFrame.
  • Example modification: Adding a new column.

Writing Back to SharePoint

• Creating In-Memory Excel Object:

  • Use ExcelWriter from Pandas to create an in-memory Excel file.
  • Specify the sheet name to maintain consistency with the original file.

• Save and Upload the Modified File:

  • Use the upload function to send the modified file back to SharePoint with a unique name.

Testing the Process

• Run the code: Execute the script to download, modify, and upload the file.
• Confirmation: Check SharePoint for the new file named "Testing Modify Excel File".
• Compare Original and Modified Files:
  • Verify that the new file contains the modifications (new column added).

Conclusion

• Future Requests: Lou encourages viewers to send questions or requests for topics.
• Support: Thanks viewers for their support and engagement.