public async Task <IActionResult> ViewDataTable(int datasetID)
{
Dataset dataset = await repository.GetDatasetByID(datasetID);
ViewBag.TableName = dataset.DatasetNameVersion;
// convert string to stream
byte[] byteArray = Encoding.UTF8.GetBytes(dataset.TheData);
using (MemoryStream stream = new MemoryStream(byteArray))
{
DataTable dataTable = CSVConverter.CSVDataToDataTable(stream); //convert the internally loaded data (which is in CSV format) to a datatable
dataTable.TableName = dataset.DatasetID.ToString();
Sheet sheet = new Sheet(dataTable);
return(View(sheet));
}
}
public void ConvertToDataTable_DECulture__ReturnsCorrectDataTable()
{
//Arrange
System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
string theData = GetTestCSVData();
//change the separator
if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
theData = theData.Replace(',', ';'); //list separator for De on windows
theData = theData.Replace('.', ','); //points replaced with commas
}
else
{
theData = theData.Replace(',', ';'); //list separator for De on windows
theData = theData.Replace('.', ','); //points replaced with commas
theData = theData.Replace(';', '.'); //list separator for De on linux
}
byte[] byteArray = Encoding.UTF8.GetBytes(theData);
using (MemoryStream stream = new MemoryStream(byteArray))
{
//Act
DataTable dataTable = CSVConverter.CSVDataToDataTable(stream, new System.Globalization.CultureInfo("de-DE"));
//Assert
Assert.Equal("SilveRSelected", dataTable.Columns[0].ColumnName);
Assert.Equal("Resp1", dataTable.Columns[1].ColumnName);
Assert.Equal("Trea t1", dataTable.Columns[2].ColumnName);
Assert.True((bool)dataTable.Rows[0][0]);
Assert.Equal("0,998758912", dataTable.Rows[0][1]);
Assert.Equal(String.Empty, dataTable.Rows[10][4]);
Assert.Equal("C", dataTable.Rows[10][5]);
}
}
public void ConvertToDataTable_GBCulture_ReturnsCorrectDataTable()
{
//Arrange
System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
string theData = GetTestCSVData();
byte[] byteArray = Encoding.UTF8.GetBytes(theData);
using (MemoryStream stream = new MemoryStream(byteArray))
{
//Act
DataTable dataTable = CSVConverter.CSVDataToDataTable(stream, new System.Globalization.CultureInfo("en-GB"));
//Assert
Assert.Equal("SilveRSelected", dataTable.Columns[0].ColumnName);
Assert.Equal("Resp1", dataTable.Columns[1].ColumnName);
Assert.Equal("Trea t1", dataTable.Columns[2].ColumnName);
Assert.True((bool)dataTable.Rows[0][0]);
Assert.Equal("0.998758912", dataTable.Rows[0][1]);
Assert.Equal(String.Empty, dataTable.Rows[10][4]);
Assert.Equal("C", dataTable.Rows[10][5]);
}
}
private async Task <IActionResult> LoadFile(FileInfo selectedFile)
{
if (selectedFile.Extension.ToUpper() == ".CSV") //not necessary as already filtering...
{
//use the CSVReader to read in the data
string message;
DataTable dataTable = CSVConverter.CSVDataToDataTable(selectedFile.OpenRead(), Program.UserCulture); //loading from disk so allow the CSV parser to just use the current culture
if (dataTable == null) //then failed to be read
{
message = "The CSV data needs to be in the correct format.";
}
else //got datatable but need to check
{
message = dataTable.CheckDataTable();
}
if (message == null)
{
//save in database
await SaveDatasetToDatabase(selectedFile.Name, dataTable);
try //to delete but if fails doesn't matter
{
System.IO.File.Delete(selectedFile.FullName);
}
catch (IOException)
{
//ignore error
}
TempData["InfoMessage"] = "File imported successfully!";
}
else
{
TempData["ErrorMessage"] = message;
}
return(RedirectToAction("Index")); //return to file loader/loaded files display screen
}
else if (selectedFile.Extension.ToUpper() == ".XLS" || selectedFile.Extension.ToUpper() == ".XLSX") //excel
{
//System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
//read in xls file
using (FileStream stream = selectedFile.OpenRead())
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(stream))
{
DataSet excelDataSet = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//if there is more than one worksheet then ask the user which sheet they want, otherwise automatically load it
if (excelDataSet.Tables.Count == 0)
{
TempData["ErrorMessage"] = "Error reading file. No sheets with valid data could be found in the Excel workbook.";
return(RedirectToAction("Index"));
}
else if (excelDataSet.Tables.Count == 1) //one excel sheet
{
DataTable dataTable = excelDataSet.Tables[0];
string message = dataTable.CheckDataTable();
if (message == null)
{
//save in database
await SaveDatasetToDatabase(selectedFile.Name, dataTable);
try //to delete...
{
System.IO.File.Delete(selectedFile.FullName);
}
catch (IOException)
{
//...if fails doesn't matter
}
TempData["InfoMessage"] = "File imported successfully!";
}
else
{
TempData["ErrorMessage"] = message;
}
return(RedirectToAction("Index")); //return to file loader/loaded files display screen
}
else //more than 1 excel sheet
{
List <string> tableNames = new List <string>();
foreach (DataTable t in excelDataSet.Tables)
{
tableNames.Add(t.TableName);
}
TempData["TableNames"] = tableNames;
return(RedirectToAction("SheetSelector", new { filename = selectedFile.FullName })); //go to sheet selection screen
}
}
}
}
else
{
throw new ArgumentException("File format not recognised.");
}
}
public async Task Execute(string analysisGuid)
{
using (IServiceScope scope = services.CreateScope())
{
ISilveRRepository silveRRepository = scope.ServiceProvider.GetRequiredService <ISilveRRepository>();
AppSettings appSettings = scope.ServiceProvider.GetRequiredService <IOptions <AppSettings> >().Value;
//declared here as used in exception handler
string workingDir = Path.GetTempPath();
string theArguments = null;
string rscriptPath = null;
try
{
Stopwatch sw = Stopwatch.StartNew();
//get analysis
Analysis analysis = await silveRRepository.GetAnalysisComplete(analysisGuid);
//load the analysis entity into the model so that arguments can be extracted
AnalysisModelBase analysisModel = AnalysisFactory.CreateAnalysisModel(analysis);
analysisModel.LoadArguments(analysis.Arguments);
//save the useroptions to the working dir
UserOption userOptions = await silveRRepository.GetUserOptions();
File.WriteAllLines(Path.Combine(workingDir, analysisGuid + ".useroptions"), userOptions.GetOptionLines());
//combine script files into analysisGuid.R
string scriptFileName = Path.Combine(workingDir, analysisGuid + ".R");
List <string> scriptLines = new List <string>();
scriptLines.AddRange(File.ReadAllLines(Path.Combine(Startup.ContentRootPath, "Scripts", "Common_Functions.R")));
scriptLines.AddRange(File.ReadAllLines(Path.Combine(Startup.ContentRootPath, "Scripts", analysis.Script.ScriptFileName + ".R")));
if (analysisModel.CustomRCode != null)
{
scriptLines.Add(analysisModel.CustomRCode);
}
File.WriteAllLines(scriptFileName, scriptLines);
//csvfilename is built from the analysis guid and is also used in R to name the output at this time
string csvFileName = Path.Combine(workingDir, analysisGuid + ".csv");
if (analysisModel is AnalysisDataModelBase analysisDataModelBase) //then has data component
{
string[] csvData = analysisDataModelBase.ExportData();
File.WriteAllLines(csvFileName, csvData);
}
//setup the r process (way of calling rscript.exe is slightly different for each OS)
if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
if (!String.IsNullOrEmpty(appSettings.CustomRScriptLocation))
{
rscriptPath = appSettings.CustomRScriptLocation;
}
else
{
rscriptPath = Path.Combine(Startup.ContentRootPath, "R", "bin", "Rscript.exe");
}
}
else if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux))
{
if (!String.IsNullOrEmpty(appSettings.CustomRScriptLocation))
{
rscriptPath = appSettings.CustomRScriptLocation;
}
else
{
rscriptPath = "Rscript";
}
}
else if (RuntimeInformation.IsOSPlatform(OSPlatform.OSX))
{
if (!String.IsNullOrEmpty(appSettings.CustomRScriptLocation))
{
rscriptPath = appSettings.CustomRScriptLocation;
}
else
{
rscriptPath = "/usr/local/bin/Rscript";
}
}
ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = rscriptPath;
psi.WorkingDirectory = workingDir;
theArguments = analysisModel.GetCommandLineArguments();
psi.Arguments = FormatPreArgument(scriptFileName) + " --vanilla --args " + FormatPreArgument(csvFileName) + " " + theArguments;
//Configure some options for the R process
psi.UseShellExecute = false;
psi.CreateNoWindow = true;
psi.RedirectStandardOutput = true;
psi.RedirectStandardError = true;
//start rscript.exe
Process R = Process.Start(psi);
//create a stringbuilder to hold the output, and get the output line by line until R process finishes
StringBuilder output = new StringBuilder();
bool completedOK = R.WaitForExit(10 * 60 * 1000); //10 minutes!
if (completedOK)
{
//need to make sure that we have got all the output so do a readtoend here
output.AppendLine(R.StandardOutput.ReadToEnd());
output.AppendLine();
//output the errors from R
string errorsFromR = R.StandardError.ReadToEnd().Trim();
R.Close();
R.Dispose();
if (!String.IsNullOrEmpty(errorsFromR))
{
output.AppendLine();
output.Append(errorsFromR);
output.AppendLine();
}
}
else //timed out, try and kill it (but usually doesnt work)
{
output.AppendLine("WARNING! The R process timed out before the script could complete");
output.AppendLine();
//get the id so can really check if it has died
int processID = R.Id;
//try and kill it
R.Kill();
R.WaitForExit(5000); //wait 5 seconds to exit, but this usually doesnt work
R.Dispose();
if (Process.GetProcesses().Any(x => x.Id == processID)) //then R failed to exit
{
throw new TimeoutException("R timed out and failed to exit gracefully, aborting analysis without reading results or log. You may need to manually kill the Rscript process. Partial results and log may be in the temp folder.");
}
}
TimeSpan timeElapsed = sw.Elapsed;
output.AppendLine();
output.AppendLine("Analysis by the R Processor took " + Math.Round(timeElapsed.TotalSeconds, 2) + " seconds.");
analysis.RProcessOutput = output.ToString().Trim();
//assemble the entire path and file to the html output
string htmlFile = Path.Combine(workingDir, analysisGuid + ".html");
if (File.Exists(htmlFile)) //won't exist if there is an error!
{
DirectoryInfo dir = new DirectoryInfo(workingDir);
FileInfo[] outputFiles = dir.GetFiles(analysis.AnalysisGuid + "*");
//first go through all the results files...
List <string> resultsFiles = new List <string>();
foreach (FileInfo file in outputFiles.Where(x => x.Extension == ".html" || x.Extension == ".png")) //go through all results output
{
resultsFiles.Add(file.FullName);
}
//generate the inline html
string inlineHtml = InlineHtmlCreator.CreateInlineHtml(resultsFiles);
analysis.HtmlOutput = inlineHtml;
//do a save at this point so that results can be shown (processing is checking for output at this point)
await silveRRepository.UpdateAnalysis(analysis);
//now go through any csv output to be imported into datasets
foreach (FileInfo file in outputFiles.Where(x => x.Extension == ".csv" && !x.FullName.EndsWith(analysis.AnalysisGuid + ".csv"))) //go through any dataset output (make sure dont import original csv file!)
{
DataTable dataTable = CSVConverter.CSVDataToDataTable(file.OpenRead()); //because R will write out using , separator
string datasetName = file.Name.Replace(analysis.AnalysisGuid, String.Empty);
await SaveDatasetToDatabase(silveRRepository, datasetName, dataTable);
}
}
else //something not right then, throw exception
{
throw new InvalidOperationException("No html output found!" + Environment.NewLine + "RProcessOutput:" + Environment.NewLine + analysis.RProcessOutput);
}
#if !DEBUG
string[] filesToClean = Directory.GetFiles(workingDir, analysis.AnalysisGuid + "*");
foreach (string file in filesToClean)
{
File.Delete(file);
}
#endif
}
catch (System.ComponentModel.Win32Exception ex) when(ex.NativeErrorCode == 2)
{
Analysis analysis = await silveRRepository.GetAnalysis(analysisGuid);
string message = "Rscript cannot be found, so no analysis was run." + Environment.NewLine;
if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
message = message + "Your R installation (specifically Rscript.exe) is missing - please reinstall this application.";
}
else if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux))
{
message = message + "No R install could be found on this system. It is recommended that you copy and run the following shell script to install the correct version of R. Then, click the 'Install R packages' button under settings to install the correct R packages." + Environment.NewLine;
message = message + "apt-get install r-base -y";
}
else if (RuntimeInformation.IsOSPlatform(OSPlatform.OSX))
{
message = message + "No R install could be found on this system. It is recommended that you download and install the following R package (https://cran.r-project.org/bin/macosx/R-3.5.2.pkg) into your OSX system. Then, click the 'Install R packages' button under settings to install the correct R packages.";
}
analysis.RProcessOutput = message;
await silveRRepository.UpdateAnalysis(analysis);
}
catch (Exception ex)
{
Analysis analysis = await silveRRepository.GetAnalysis(analysisGuid);
string message = "ContentRoot=" + Startup.ContentRootPath + Environment.NewLine + Environment.NewLine;
message = message + "TempFolder=" + workingDir + Environment.NewLine + Environment.NewLine;
message = message + "Arguments=" + theArguments + Environment.NewLine + Environment.NewLine;
message = message + "Rscript=" + rscriptPath + Environment.NewLine + Environment.NewLine;
message = message + ex.ToString();
analysis.RProcessOutput = message;
await silveRRepository.UpdateAnalysis(analysis);
}
}
}
