public static void Import(
IReadOnlyList <string> srcColNames,
IEnumerable <object[]> dataRows,
Ast.ImportTable importTable,
bool temporaryTable,
bool truncateExistingTable,
bool stopAtFirstBlankRow,
IfConversionFails ifConversionFails,
BlankValuesOption blankValuesMethod,
Notebook notebook,
ScriptRunner runner,
ScriptEnv env
)
{
var dstTableName = runner.EvaluateIdentifierOrExpr(importTable.TableName, env);
var mappings = GetImportColumnMappings(importTable.ImportColumns, runner, env, srcColNames);
if (!mappings.Any())
{
throw new Exception("No columns chosen for import.");
}
CreateOrTruncateTable(mappings, dstTableName, temporaryTable, truncateExistingTable, notebook);
VerifyColumnsExist(mappings.Select(x => x.DstColumnName), dstTableName, notebook);
InsertDataRows(dataRows, srcColNames, mappings, dstTableName, ifConversionFails, stopAtFirstBlankRow,
blankValuesMethod, notebook);
}
public static void Import(string[] srcColNames, IEnumerable <object[]> dataRows, Ast.ImportTable importTable,
bool temporaryTable, bool truncateExistingTable, IfConversionFails ifConversionFails, INotebook notebook,
ScriptRunner runner, ScriptEnv env)
{
Ast.ImportColumn[] dstColNodes;
string[] dstColNames;
var dstTableName = runner.EvaluateIdentifierOrExpr(importTable.TableName, env);
GetDestinationColumns(importTable.ImportColumns, runner, env, srcColNames,
out dstColNodes, out dstColNames);
CreateOrTruncateTable(srcColNames, dstColNodes, dstColNames, dstTableName, temporaryTable,
truncateExistingTable, notebook);
VerifyColumnsExist(dstColNames, dstTableName, notebook);
InsertDataRows(dataRows, dstColNames, dstColNodes, dstTableName, ifConversionFails, notebook);
}
private ImportCsvStmtRunner(INotebook notebook, ScriptEnv env, ScriptRunner runner, Ast.ImportCsvStmt stmt)
{
_notebook = notebook;
_env = env;
_runner = runner;
_stmt = stmt;
foreach (var option in _stmt.OptionsList.GetOptionKeys())
{
switch (option)
{
case "SKIP_LINES":
_skipLines = _stmt.OptionsList.GetOptionLong(option, _runner, _env, 0, minValue: 0);
break;
case "TAKE_LINES":
_takeLines = _stmt.OptionsList.GetOptionLong(option, _runner, _env, -1, minValue: -1);
break;
case "HEADER_ROW":
_headerRow = _stmt.OptionsList.GetOptionBool(option, _runner, _env, true);
break;
case "TRUNCATE_EXISTING_TABLE":
_truncateExistingTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false);
break;
case "TEMPORARY_TABLE":
_temporaryTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false);
break;
case "FILE_ENCODING":
_fileEncoding = _stmt.OptionsList.GetOptionEncoding(option, _runner, _env);
break;
case "IF_CONVERSION_FAILS":
_ifConversionFails = (IfConversionFails)_stmt.OptionsList.GetOptionLong(
option, _runner, _env, 1, minValue: 1, maxValue: 3);
break;
default:
throw new Exception($"\"{option}\" is not a recognized option name.");
}
}
}
private ImportXlsStmtRunner(INotebook notebook, ScriptEnv env, ScriptRunner runner, Ast.ImportXlsStmt stmt)
{
_notebook = notebook;
_env = env;
_runner = runner;
_stmt = stmt;
_filePath = _runner.EvaluateExpr <string>(_stmt.FilenameExpr, _env);
if (!File.Exists(_filePath))
{
throw new Exception($"The specified XLS/XLSX file was not found: \"{_filePath}\"");
}
if (_stmt.WhichSheetExpr != null)
{
_whichSheet = _runner.EvaluateExpr(_stmt.WhichSheetExpr, _env);
}
int?index;
foreach (var option in _stmt.OptionsList.GetOptionKeys())
{
switch (option)
{
case "FIRST_ROW":
_firstRowIndex = _stmt.OptionsList.GetOptionInt(option, _runner, _env, 1, minValue: 1) - 1;
break;
case "LAST_ROW":
var lastRowNum = _stmt.OptionsList.GetOptionInt(option, _runner, _env, 0, minValue: 0);
if (lastRowNum == 0)
{
_lastRowIndex = null;
}
else
{
_lastRowIndex = lastRowNum - 1;
}
break;
case "FIRST_COLUMN":
index = XlsUtil.ColumnRefToIndex(_stmt.OptionsList.GetOption <object>(option, _runner, _env, null));
if (index.HasValue)
{
_firstColumnIndex = index.Value;
}
else
{
throw new Exception($"The {option} option must be a valid column number or string.");
}
break;
case "LAST_COLUMN":
var lastColumnValue = _stmt.OptionsList.GetOption <object>(option, _runner, _env, null);
if (lastColumnValue.Equals(0))
{
_lastColumnIndex = null;
break;
}
index = XlsUtil.ColumnRefToIndex(lastColumnValue);
if (index.HasValue)
{
_lastColumnIndex = index.Value;
}
else
{
throw new Exception($"The {option} option must be a valid column number or string.");
}
break;
case "HEADER_ROW":
_headerRow = _stmt.OptionsList.GetOptionBool(option, _runner, _env, true);
break;
case "TRUNCATE_EXISTING_TABLE":
_truncateExistingTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false);
break;
case "TEMPORARY_TABLE":
_temporaryTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false);
break;
case "IF_CONVERSION_FAILS":
_ifConversionFails = (IfConversionFails)_stmt.OptionsList.GetOptionLong(
option, _runner, _env, 1, minValue: 1, maxValue: 3);
break;
default:
throw new Exception($"\"{option}\" is not a recognized option name.");
}
}
}
public static void InsertDataRows(IEnumerable <object[]> rows, string[] dstColNames,
Ast.ImportColumn[] dstColNodes, string dstTableName, IfConversionFails ifConversionFails, INotebook notebook)
{
var dstColCount = dstColNames.Where(x => x != null).Count();
var insertSql = GetInsertSql(dstTableName, dstColCount, numRows: 1);
var insertArgs = new List <object>();
foreach (var row in rows)
{
insertArgs.Clear();
bool skipRow = false;
for (int j = 0; j < Math.Min(row.Length, dstColNames.Length); j++)
{
if (dstColNames[j] == null)
{
continue; // column not chosen to be imported
}
var originalValue = row[j];
var typeConversion = dstColNodes[j].TypeConversion ?? Ast.TypeConversion.Text;
object converted;
bool error = !TryParseValue(originalValue, typeConversion, out converted);
if (!error)
{
insertArgs.Add(converted);
}
else if (ifConversionFails == IfConversionFails.ImportAsText)
{
insertArgs.Add(originalValue.ToString());
}
else if (ifConversionFails == IfConversionFails.SkipRow)
{
skipRow = true;
}
else if (ifConversionFails == IfConversionFails.Abort)
{
throw new Exception($"Failed to parse input value as type \"{typeConversion}\". Value: \"{originalValue}\".");
}
else
{
throw new Exception($"Internal error: unknown value for IF_CONVERSION_FAILS: \"{ifConversionFails}\".");
}
}
if (!skipRow)
{
while (insertArgs.Count < dstColCount)
{
insertArgs.Add(null);
}
notebook.Execute(insertSql, insertArgs);
}
}
}
private ImportCsvStmtRunner(Notebook notebook, ScriptEnv env, ScriptRunner runner, Ast.ImportCsvStmt stmt)
{
_notebook = notebook;
_env = env;
_runner = runner;
_stmt = stmt;
foreach (var option in _stmt.OptionsList.GetOptionKeys())
{
switch (option)
{
case "SKIP_LINES":
_skipLines = _stmt.OptionsList.GetOptionLong(option, _runner, _env, 0, minValue: 0);
break;
case "TAKE_LINES":
_takeLines = _stmt.OptionsList.GetOptionLong(option, _runner, _env, -1, minValue: -1);
if (_takeLines == -1)
{
_takeLines = null;
}
break;
case "HEADER_ROW":
_headerRow = _stmt.OptionsList.GetOptionBool(option, _runner, _env, true);
break;
case "SEPARATOR": {
var separator = _stmt.OptionsList.GetOption(option, _runner, _env, ",");
if (separator.Length != 1)
{
throw new Exception("IMPORT CSV: The separator must be a single character.");
}
_separator = separator[0];
break;
}
case "TRUNCATE_EXISTING_TABLE":
_truncateExistingTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false);
break;
case "TEMPORARY_TABLE":
_temporaryTable = _stmt.OptionsList.GetOptionBool(option, _runner, _env, false);
break;
case "FILE_ENCODING":
_fileEncoding = _stmt.OptionsList.GetOptionEncoding(option, _runner, _env);
break;
case "IF_CONVERSION_FAILS":
_ifConversionFails = (IfConversionFails)_stmt.OptionsList.GetOptionLong(
option, _runner, _env, 1, minValue: 1, maxValue: 3);
break;
case "BLANK_VALUES":
_blankValuesMethod = (BlankValuesOption)_stmt.OptionsList.GetOptionLong(
option, _runner, _env, 2, minValue: 1, maxValue: 3);
break;
default:
throw new Exception($"\"{option}\" is not a recognized option name.");
}
}
}
