Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
Hoping for a second pair of eyes...
I'm trying to use a R Script to dump a Query into a csv file stored in SharePoint. I've followed this wonderful tutorial to set it all up using R, RStudio and Strawberry Perl. I'm getting a syntax error telling me a Token Comma is expected - but it looks OK to me. Can one of you be my second pair of eyes?
The formula reads:
= R.Execute("# 'dataset' holds the input data for this script#(lf)require(gdata)#(lf)write.table(trim(dataset), file="https://site.sharepoint.com/site/AnalysisTeam/Automated Reporting/VA data.csv", sep = “,”, row.names = FALSE)#(lf)plot(dataset);",[dataset=#"Removed Columns"])
(I have added italics where Power BI says the token comma is missing)
I have no previous experience with R
Thank you in anticipation!
Solved! Go to Solution.
Hi @SPaine86
The URL needs two sets of double quotes to escape it within the string it is inside i.e. ""url""
Try this
R.Execute("# 'dataset' holds the input data for this script#(lf)require(gdata)#(lf)write.table(trim(dataset), file=""https://site.sharepoint.com/site/AnalysisTeam/Automated Reporting/VA data.csv"", sep = ",", row.names=FALSE)#(lf)plot(dataset);",[dataset=#"Removed Columns"])
regards
Phil
Proud to be a Super User!
Hi @SPaine86
The R doco doesn't mention writing to a web location so the solution looks to be to write to a local file and then copy the file up to the web.
As you are using Sharepoint, can you just write locally and let the file sync to Sharepoint?
Alternatively check these articles that address the issue.
Solved: Exporting file to sharepoint using R script editor - Microsoft Power BI Community
Saving files to SharePoint folder from R - Stack Overflow
Saving a file to Sharepoint with R - Stack Overflow
Regards
Phil
Proud to be a Super User!
Hi @SPaine86 ,
Apologies for jumping in here.
You can easily set-up scheduled exports from Power BI, everytime the data refreshes using a personal gateway which is very easy to set-up. Refer following blog if this helps:
Thanks,
Pragati
Hi @SPaine86
The URL needs two sets of double quotes to escape it within the string it is inside i.e. ""url""
Try this
R.Execute("# 'dataset' holds the input data for this script#(lf)require(gdata)#(lf)write.table(trim(dataset), file=""https://site.sharepoint.com/site/AnalysisTeam/Automated Reporting/VA data.csv"", sep = ",", row.names=FALSE)#(lf)plot(dataset);",[dataset=#"Removed Columns"])
regards
Phil
Proud to be a Super User!
Thank you, this has solved that error!
If I subsitute the URL for a location on my C:\ drive (e.g. My Documents) the script now runs as expected (I have to use a double \\ in the path, e.g. C:\\user\\user\\documents\\file.csv.
But using a URL doesn't work. I get the following error:
can only open URLs for readingError in file(file, ifelse(append, "a", "w")) :
cannot open the connection
Calls: write.table -> file
Execution halted
Is there a different syntax required for exporting to a web location maybe?
Hi @SPaine86
The R doco doesn't mention writing to a web location so the solution looks to be to write to a local file and then copy the file up to the web.
As you are using Sharepoint, can you just write locally and let the file sync to Sharepoint?
Alternatively check these articles that address the issue.
Solved: Exporting file to sharepoint using R script editor - Microsoft Power BI Community
Saving files to SharePoint folder from R - Stack Overflow
Saving a file to Sharepoint with R - Stack Overflow
Regards
Phil
Proud to be a Super User!
Our plan is for Power BI Online to generate this file every time the document refreshes - so I'm not sure if it will be able to save locally and then upload again back to SharePoint (we don't have a gateway set up). I think I'll need to investigate this further - but thank you for all your help so far!
Hi @SPaine86 ,
Apologies for jumping in here.
You can easily set-up scheduled exports from Power BI, everytime the data refreshes using a personal gateway which is very easy to set-up. Refer following blog if this helps:
Thanks,
Pragati
Hi Pragati,
I think this is exactly what I need - thank you. Your instructions for setting up a gateway are very clear, and I think they will help me with the "next job" on my list (sourcing XML files from a network location) - thank you 🙂
Thank you Pragati, Philip and Manikumar for your time and help - I really do appreciate it.
Steve
Looks like link is not correct. I can see there is space on your link. If I am not wrong there won't be any spaces on a link.
Regards,
Manikumar
Proud to be a Super User!
Hi Manikumar,
Thank you for your reply. I have tried different links (including some without spaces and some which point to a local drive rather than SharePoint) and I am still getting a syntax error - so I don't think that is it unfortunately. Power BI is expecting a comma immediately after the " in file..
Thanks
Steve
Hi @SPaine86 ,
Try on thing. In the following file path there is folder name with SPACES:
https://site.sharepoint.com/site/AnalysisTeam/Automated Reporting/VA data.csv"
Can you try renaming your folder to something like "Automated_Reporting". This should be easy to achieve. Also your csv file name has got space in it as well. Try modifying it's name to "VA_data.csv"
I think this will resolve the issue.
Thanks,
Pragati
Hi @SPaine86 ,
I think the problem is in the following line. Not sure why there is a SEMI COLON after plot(dataset):
plot(dataset);",[dataset=#"Removed Columns"])
Thanks,
Pragati
Thanks for your reply Pragati.
The error seems to be with my URL/path. From after file=" the formula turns one colour. This is also where Power BI indicates that the comma should be. I have tried different paths including pointing to my own desktop, and these all have the same 'token comma' error.
It may also be that the semi-colon also shouldn't be there, but the formula fails before it reaches that part - so I guess I'll find out once the path error is fixed!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |