R script "Token Comma expected"

[SPaine86]

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!

[PhilipTreacy · Accepted 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

[PhilipTreacy · Accepted solution]

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

[PhilipTreacy · Accepted 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

[SPaine86]

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?

[PhilipTreacy · Accepted solution]

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

[SPaine86]

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!

[Pragati11 · Accepted solution]

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:

https://community.powerbi.com/t5/Community-Blog/Schedule-Automated-Data-Exports-from-Power-BI-using-...

Thanks,

Pragati

[SPaine86]

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

[manikumar34]

@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

[SPaine86]

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

[Pragati11]

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

[Pragati11]

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

[SPaine86]

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!