1

Currently I am building the automated process to clean and transform excel data from sharepoint using R. I have trouble reading excel files from sharepoint in R. I read a couple of posts (Accessing Excel file from Sharepoint with R, for instance), and tried a couple of suggestions, but none worked for me. The all error message are "Path" does not exist. Could someone give me some light for that?

Harshal Parekh
  • 5,918
  • 4
  • 21
  • 43
Qianru Song
  • 331
  • 1
  • 4
  • 16
  • 1
    Hi Qianru, can you please summarize what you tried from that post and what didn't work, pasting your specific error messages for each thing you tried? This will help members on the site narrow down what the problem might be. – Nova Feb 06 '20 at 19:58
  • @QianruSong Your question is unfortunately like many others on SO. You have asked a question which might be easy to debug if we knew what code you had used, but you have failed to respond in a timely manner to a constructive suggestion to [edit] your question to improve it. There's a close mechanism for such questions. Close votes can be reversed if you heed the offered advice. – IRTFM Feb 06 '20 at 22:32

2 Answers2

0

I ran GET() and the link works: 

r <- GET(url, authenticate("window_username","window_password",type="any"))

I run into the same issue using the following code to get the info from an excel on this sharepoint site with the same error as the one in the original question: 

data <- read_excel(url)

Any feedback would be greatly appreciated.

Winnie Holden
  • 1
0

To make access to SharePoint files easy you should sync the sites from the web app to File Explorer. Addresses for these cloud resources that have been synced are commonly of the form: C:\Users\username\My Org\My Teams Group - General\Project\My Excel.xlsx This can create a problem when the code is run multiple users. Whilst https addresses for cloud locations may work in File Explorer they do not work directly within R packages. If relative addresses don't work you can make the code user agnostic by setting the username as a variable or returning the homepath with Sys.getenv() function.

library(openxlsx)
username <- Sys.getenv("USERNAME")
sharepoint_address <- "/My Org/My Teams Group – General/Project/My Excel.xlsx"
df <- read.xlsx(xlsxFile = paste0("C:/Users/",username,sharepoint_address), sheet = "Raw Data”)
# More elegantly 
df <- read.xlsx(xlsxFile = paste0(Sys.getenv("HOMEPATH"),sharepoint_address), sheet = "Raw Data”)
ajam21
  • 23
  • 4