read csv from SharePoint with Pandas (Python authentication code)

user_7683796 0
Viewed 12986

I have a csv in the documents section of a SharePoint site. I would like to import it in Pandas. Of course is I use just the normal code below I get HTTP error 403 Forbidden.

import pandas
df = pandas.read_csv('link from sharepoint')

How do I get SharePoint authentication to work using Python so Pandas can read the csv file.

I have already searched and tried code in several internet posts, but either the code is too generic that I do not know what it means e.g. 

username = 'YourDomain\\account'

or

user = r'SERVER\user'

or it just didn't.

Is there a simple way to get authentication to work and import the file in Pandas?

pandas sharepoint python
4 Answers

Relatively easy solution with sharepy:

import io
import sharepy
import pandas as pd

URL = 'https://yoursharepointsite.sharepoint.com'
FILE_URL = '/sites/...path to your file.../yourfilename.csv'
SHAREPOINT_USER = 'yourusername'
SHAREPOINT_PASSWORD = 'yourpassword'

s = sharepy.connect(URL, username=SHAREPOINT_USER, password=SHAREPOINT_PASSWORD).
r = s.get(URL+FILE_URL)
f = io.BytesIO(r.content)
df = pd.read_csv(f)
user_122019100

To my knowledge you can't, but what you can do is use an API to connect python to SharePoint.

What I do is use SharePlum -> download via pip (pip install shareplum)

import sys, pandas, datetime
from requests_ntlm import HttpNtlmAuth
from shareplum import Site

auth = HttpNtlmAuth('DOMAIN' + '\\' + str(username), str(password)) 
site = Site('sharepointsiteurl', auth=auth, verify_ssl=True) # set to False if behind firewall

site_list = site.List('Name of List')

df = pandas.DataFrame.from_dict(site_list)

# then do what you need to in Pandas

Edit: Since you are trying to download a CSV file. use the python-sharepoint api

same thing as above but instead of the 

site_list = site.List('Name of List')
# do instead
site.download('nameoffile.csv', 'D:/location/to/save.csv')
user_35052060

As long as your SP username and password are identical to your Windows login credentials, which is very common, the following should work (mostly taken from here):

import requests
from requests_negotiate_sspi import HttpNegotiateAuth
import pandas as pd

url = 'https://yoursharepointsite.sharepoint.com'
fileUrl = '/sites/...path to your file.../yourfilename.csv'

r = requests.get(url + fileUrl, auth=HttpNegotiateAuth())
df = pd.read_csv(r.text)

I tested it with read_excel(r.content), since I had an XLSX file instead of a CSV. It seems that XLSX files are treated as binaries. But with CSV being parsed as text, it should work as posted. Feel free to report back.

user_40277290

Below is a complete code for file/folder management in ShaprePoint which allows you to read, write, and delete files. In the example, I'm reading excel file into pandas dataframe.

import os
import pandas as pd
from shareplum import Site, Office365
from shareplum.site import Version

config = {'user': {email},
          'password': {password},
          'base_url': 'https://{domain}.sharepoint.com',
          'site': 'https://{domain}.sharepoint.com/sites/{sitename}/',
          'doc_library': 'Shared Documents/{folder}'
          }


def authenticate():
    auth = Office365(config['base_url'], username=config['user'], password=config['password']).GetCookies()
    site = Site(config['site'], version=Version.v365, authcookie=auth)
    return site


def connect_folder(sharepoint_folder):
    site = authenticate()
    sharepoint_dir = '/'.join([config['doc_library'], sharepoint_folder])
    folder = site.Folder(sharepoint_dir)
    return folder


def upload_file(local_file, sharepoint_folder):
    file_name = os.path.basename(local_file)
    folder = connect_folder(sharepoint_folder)
    with open(local_file, mode='rb') as file_obj:
        file_content = file_obj.read()

    folder.upload_file(file_content, file_name)


def read_file(file_name, sharepoint_folder):
    folder = connect_folder(sharepoint_folder)
    df = pd.read_excel(folder.get_file(file_name))
    return df


def delete_file(file_name, sharepoint_folder):
    folder = connect_folder(sharepoint_folder)
    folder.delete_file(file_name)


sharepoint_folder = 'Test'
local_file = '/file/path/location/excel_file_test.xlsx'
file_name = os.path.basename(local_file)
upload_file(local_file, sharepoint_folder)
df = read_file(file_name, sharepoint_folder)
print(df)
user_124119750
Related