How To Upload CSV To SharePoint From Dataframe Using Python

Introduction

In this article, you will learn how to read Microsoft SQL table, convert the DataFrame to CSV and upload it directly to SharePoint. I have created a simple Microsoft SQL table for this demo named as Employees which contains the following columns and rows.

I have created a document library named Demo in SharePoint site and file gets uploaded to this library as shown below.

Topics Covered

This article demonstrates how to build the following:

• Create the project folder
• Install the required packages
• Implement the REST API
• Test the API

Pre-requisites

1. Windows machine and Visual Studio code are used for this article. Complete all the prerequisites mentioned in this article.
2. Install the Microsoft ODBC Driver for SQL Server on Windows.
3. Refer this article to generate the client id and secret to connect to SharePoint site

Tools

1. Visual Studio Code

Task 1: Create the project folder

In this task, you will see how to create the project folder.

Step 1

Open Windows Command Prompt and run the following commands to create the new folder for this project.

mkdir UploadCSVSharePoint
cd .\UploadCSVSharePoint

Task 2: Install the required packages

In this task, you will see how to install the required packages for this project.

Step 1

Open UploadCSVSharePoint folder in Visual Studio Code. Click Terminal -> New Terminal.

Step 2

Run the following command to install the packages required for this project.

pip install flask
pip install flask-restful
pip install pyodbc
pip install pandas
pip install Office365-REST-Python-Client

• flask is a simple framework for building complex web applications.
• flask-restful is an extension for Flask that adds support for quickly building REST APIs
• pyodbc is an open source Python module that makes accessing ODBC databases simple.
• pandas provides fast, flexible, and expressive data structures designed to make working with “relational” or “labeled” data both easy and intuitive.
• Office365-REST-Python-Client Office 365 & Microsoft Graph library for Python

Task 3: Implement the REST API

In this task, you will see how to implement the REST API to read the Microsoft SQL table, convert the DataFrame to CSV and upload it directly to SharePoint.

Step 1

In Visual Studio Code, create a new file app.py under UploadCSVSharePoint folder.

Step 2

Copy and paste the below code. Note: Update the connection string, client id and secret value.

from flask import Flask
from flask_restful import Resource, Api
import pyodbc
from datetime import date
from io import BytesIO
import pandas as pd
from office365.sharepoint.client_context import ClientContext
from office365.runtime.auth.client_credential import ClientCredential

# Input Parameters
getdatacmd = 'SELECT id AS EmployeeId, name AS Name, designation AS Designation, location AS Location FROM Employees'
connstring = 'DRIVER={ODBC Driver 18 for SQL Server};SERVER=*******\SQLEXPRESS;DATABASE=demo;ENCRYPT=no;Trusted_Connection=yes;'
today = date.today().strftime('%d.%m.%Y')
sharepoint_url = 'https://v2technologies683.sharepoint.com/sites/Demo'
clientid = '18e1fecc-0925-4372-ae38-********998a'
clientsecret = 'gTvLMjA1Mt32**********lX4ANWsfsN6nrYplIF2zg='
relative_url = f'Demo'
filename = r'Employees_SQL_'+today+'.csv'

# Create the flask app
app = Flask(__name__)
# Create an API object
api = Api(app)

# Class for UploadCSVToSharePoint
class UploadCSVToSharePoint(Resource):
	# GET Request
	def get(self):
        # Read data from SQL table
		cnxn = pyodbc.connect(connstring)
		sql_query = pd.read_sql_query(getdatacmd, cnxn)
		df = pd.DataFrame(sql_query)
		
        # Connect to SharePoint
		client_credentials = ClientCredential(clientid, clientsecret)		
		ctx = ClientContext(sharepoint_url).with_credentials(client_credentials)		
		target_folder = ctx.web.get_folder_by_server_relative_url(relative_url)

		# Create a buffer object
		buffer = BytesIO()  

		# Write the dataframe to the buffer            
		df.to_csv(buffer, index=False)
		buffer.seek(0)
		file_content = buffer.read()

        # Upload the file			
		target_file = target_folder.upload_file(filename, file_content).execute_query()		
		return 'File has been uploaded to url: {0}'.format(target_file.serverRelativeUrl)	      
        
# Add the defined resources along with their corresponding urls
api.add_resource(UploadCSVToSharePoint, '/')

# Driver function
if __name__ == '__main__':

	app.run(debug = True)

Task 3: Test the API

In this task, you will see how to test the API which creates the CSV file with data read from Microsoft SQL table.

Step 1

In Visual Studio Code, run the following command in the Terminal.

python .\app.py 

Step 2

Open the browser and access the below URL. CSV file gets uploaded to SharePoint site Demo library.

http://127.0.0.1:5000/

References

1. Flask-RESTful documentation
2. Python SQL Driver - pyodbc
3. pandas.DataFrame.to_csv

Summary

This article describes how to read Microsoft SQL table, convert the DataFrame to CSV and upload it directly to SharePoint.