Forum Discussion
R script "Token Comma expected"
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!
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
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
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
11 Replies
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
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 haltedIs 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
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!
SPaine86 ,
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
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
