cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stchln
Resolver I
Resolver I

library ssh in R

Hello all,

I have a simplified R script below that works fine in Power BI Desktop but no more in Power BI Service, the reason is that the library ssh I am using is not supported in Power BI Service ("Error in library("ssh") : there is no package called 'ssh'")

For some reason, despite ssh library  CRAN - Package ssh (r-project.org) is an official package it is not part of the list of supported ones Learn which R packages are supported - Power BI | Microsoft Docs

 

Any idea for another solution? I am thinking about RCurl library but I don't see why we need to provide both public and private keys in scp_upload and I don't see how to build a temporary pkey file (containing private key) and to pass it to scp_upload...

I think I need help to port the below lines from ssh to RCurl (if RCurl is a good B-plan candidate)

 

 

 

# pkey: Cloud private key .pem format
pkey<-tempfile()
write("-----BEGIN RSA PRIVATE KEY-----
MIIEowIBAAKCAQEA5Vl3nEh3NZRE1p2ek3Xb81GtOrwgqwCtFsS9oUeT5pjmkYmh
../..
-----END RSA PRIVATE KEY-----",pkey)

# csv generated file, dummy case
csv1<-tempfile(pattern="nodes",fileext=".csv")
write.csv(1:10,csv1)

# upload file using ssh private key
library("ssh")
session<-ssh_connect("root@10.157.211.173",keyfile=pkey)
scp_upload(session,csv1,to="/var/lib/neo4j/import")

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
stchln
Resolver I
Resolver I

I think I found a way, based on One Drive + Power Automate to upload on Sharepoint or Task Scheduler to upload a server using curl or pscp

 

Example : source is writetonedrive.csv file 2 columns F and Rem

"Fid","Rem"
"FA",14
"FB",19

1/ in Power BI report, open source located in your OneDrive. Take care of path format that should not contain the /:x/r/ added by default for browser compatibility matrix

If your path has the /:x/r/ you don't have OAuth2 as credential in Power BI Service

 

let
Source = Csv.Document(Web.Contents("https://nokia-my.sharepoint.com/personal/stephane.../writetoonedrive.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fid", type text}, {"Rem", Int64.Type}})
in
#"Changed Type"

 

2/ in R script, here simple code is just substracting 1 to Rem field

# basic code
dataset$Rem<-dataset$Rem-1

# saving in same writetoonedrive.csv (update)
write.table(dataset,file='C:/Users/chalon1/OneDrive.../writetoonedrive.csv', sep=",", append=F, row.names=F)

library(gridExtra)
gridExtra::grid.table(dataset)

 

3/ in Power BI Service, you can edit the credentials using OAuth2 and you can schedule a refresh.

View solution in original post

2 REPLIES 2
stchln
Resolver I
Resolver I

I think I found a way, based on One Drive + Power Automate to upload on Sharepoint or Task Scheduler to upload a server using curl or pscp

 

Example : source is writetonedrive.csv file 2 columns F and Rem

"Fid","Rem"
"FA",14
"FB",19

1/ in Power BI report, open source located in your OneDrive. Take care of path format that should not contain the /:x/r/ added by default for browser compatibility matrix

If your path has the /:x/r/ you don't have OAuth2 as credential in Power BI Service

 

let
Source = Csv.Document(Web.Contents("https://nokia-my.sharepoint.com/personal/stephane.../writetoonedrive.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fid", type text}, {"Rem", Int64.Type}})
in
#"Changed Type"

 

2/ in R script, here simple code is just substracting 1 to Rem field

# basic code
dataset$Rem<-dataset$Rem-1

# saving in same writetoonedrive.csv (update)
write.table(dataset,file='C:/Users/chalon1/OneDrive.../writetoonedrive.csv', sep=",", append=F, row.names=F)

library(gridExtra)
gridExtra::grid.table(dataset)

 

3/ in Power BI Service, you can edit the credentials using OAuth2 and you can schedule a refresh.

View solution in original post

lbendlin
Super User
Super User

Power BI is a sink, not a source. Use different tools to push your data into other systems.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!