How to convert an r datafile to csv without using R?-CodePudding

I am facing a weird task. I need to convert a datafile, which is in R to a csv file by using an automated vba script or a batch job. Installing R studio or a plug in for excel is not allowed due to compliance issues. Is this possible to do this conversion using a vba script and if yes, how? Thanks a lot!

CodePudding user response：

As commented, R is an open source, programming language that is independent of any IDE like RStudio. Its installation includes terminal shells and command line tools such as Rscript.exe. VBA can run external command line using Shell or Windows Script Host. R can even receive arguments from the command caller!

R (receiving arguments)

args <- commandArgs(trailingOnly=TRUE)   # RECEIVE ALL COMMAND LINE ARGS

var1 <- args[1]
var2 <- args[2]

cat(paste(
    "Hi! This line is from R and received", 
    var1, "and", var2, "from command line \n"
))

Cmd/PowerShell/Bash/etc. (sending arguments)

# if R bin folder is in PATH environment variable
Rscript "C:\path\to\my_script.R" "Stack" "Overflow"     

# if R bin folder is not in PATH environment variable
C:\path\to\R\bin\Rscript "C:\path\to\my_script.R" "Stack" "Overflow"

VBA (replicating command line above)

Sub Run_RScript()
    Dim oShell As Object
    Dim var1 As String, var2 As String
    Dim Rcmd As String, output As String
    
    var1 = "" & "Stack" & ""       ' STRINGS REQUIRE DOUBLE QUOTES
    var2 = "" & "Overflow" & ""    ' STRINGS REQUIRE DOUBLE QUOTES

    Set oShell = CreateObject("WScript.Shell")
    Rcmd = "Rscript C:\path\to\myScript.R " & var1 & " " & var2
    output = oShell.Exec(Rcmd).StdOut.ReadAll
    
    Debug.Print output             ' PRINT TO IMMEDIATE WINDOW (CTRL G)
    
    Set oShell = Nothing
End Sub

Immediate Window (Ctrl G)

Hi! This line is from R and received variables ( Stack and Overflow ) from command line