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."); } } }