public int Run(ImportFileOptions importTask)
{
Log.Debug($"TaskExecutor: run for File '{importTask.file}'");
// 1. Read csv
// 2. Store csv
var errNum = CsvReader.Read(importTask, SqlWriter);
Log.Trace("-------------------------------------------------------------------");
return(errNum);
}
public static IList <ImportFileOptions> ReadTasks(Logging l, string fileContent)
{
JObject importTasksRoot = JObject.Parse(fileContent);
var importFiles = new List <ImportFileOptions>();
var importTasks = importTasksRoot.SelectToken("importFiles");
foreach (var importFile in importTasks)
{
var importFileOptions = new ImportFileOptions();
importFileOptions.connectionString = GetTokenAsString(l, importFile, "connectionString", "");
importFileOptions.file = GetTokenAsString(l, importFile, "file", "");
importFileOptions.sheet = GetTokenAsString(l, importFile, "sheet", "");
importFileOptions.row = GetTokenAsInt(l, importFile, "row", -1);
importFileOptions.col = GetTokenAsInt(l, importFile, "col", -1);
importFileOptions.skipline = GetTokenAsInt(l, importFile, "skipline", -1);
importFileOptions.table = GetTokenAsString(l, importFile, "table", string.Format($"{DateTime.Now.Year}{DateTime.Now.Month}{DateTime.Now.Day}{DateTime.Now.Hour}{DateTime.Now.Minute}{DateTime.Now.Second}"));
importFileOptions.prefix = GetTokenAsString(l, importFile, "prefix", "");
importFileOptions.key = GetTokenAsString(l, importFile, "key", "");
importFileOptions.macFix = GetTokenAsString(l, importFile, "macFix", "");
importFileOptions.csv = GetTokenAsBoolean(l, importFile, "csv", true);
importFileOptions.columnMapping = GetTokenAsDictionary(l, importFile, "columnMapping");
importFileOptions.batchSize = Math.Abs(GetTokenAsInt(l, importFile, "batchSize", 1000));
importFileOptions.delimiter = GetTokenAsString(l, importFile, "delimiter", "");
importFileOptions.quoting = GetTokenAsString(l, importFile, "quoting", "");
importFileOptions.truncate = GetTokenAsBoolean(l, importFile, "truncate", false);
importFileOptions.saveMode = GetTokenAsBoolean(l, importFile, "saveMode", false);
importFileOptions.forceCreateTable = GetTokenAsBoolean(l, importFile, "forceCreateTable", false);
importFileOptions.comment = GetTokenAsString(l, importFile, "comment", "");
importFileOptions.retryPolicyNumRetries = Math.Abs(GetTokenAsInt(l, importFile, "retryPolicyNumRetries", 3));
importFileOptions.retryPolicyDelayRetries = Math.Abs(GetTokenAsInt(l, importFile, "retryPolicyDelayRetries", 1000)); // Miliseconds
importFileOptions.uniqueOnly = GetTokenAsBoolean(l, importFile, "uniqueOnly", false);
importFileOptions.uniqueFileOnly = GetTokenAsBoolean(l, importFile, "uniqueFileOnly", true);
importFileOptions.additionalSQL = GetTokenAsString(l, importFile, "additionalSQL", "");
importFiles.Add(importFileOptions);
l.Trace("--------------------------------------------------------");
}
return(importFiles);
}
public void Init(ImportFileOptions importTask, List <string> headers)
{
ImportTask = importTask;
Headers = headers;
Log.Debug($"SqlServerWriter: init for '{ImportTask.file}'");
sqlCmdBuilder = new SqlCmdBuilder(importTask);
HeaderFields = sqlCmdBuilder.GetHeaderFields(Headers);
sqlServerService = new SqlServerService(Log, getSqlConnectionString());
// Drop table
sqlServerService.simpleExecQuery(GetDropTableStatement());
// Create table
sqlServerService.simpleExecQuery(GetCreateTableStatement());
// Truncate Table
sqlServerService.simpleExecQuery(GetTruncateTableStatement());
}
public int Read(ImportFileOptions importTask, ISqlWriter sqlWriter)
{
Log.Debug($"ReadCsv for '{importTask.file}'");
Stopwatch _timer = new Stopwatch();
_timer.Start();
List <List <string> > batchLineFields = new List <List <string> >();
var fileInfo = new System.IO.FileInfo(importTask.file);
if (!System.IO.File.Exists(importTask.file))
{
Log.Error($"ReadCSV : '{importTask.file}' not exists!");
return(-1);
}
var firstLine = ReadFirstLine(fileInfo);
Char delimiter = string.IsNullOrWhiteSpace(importTask.delimiter) ? GuessDelimeter(firstLine) : importTask.delimiter.First <char>();
//Char quotingCharacter = '\0'; // no quoting-character;
Char quotingCharacter = string.IsNullOrWhiteSpace(importTask.quoting) ? GuessquotingCharacter(firstLine) : importTask.quoting.First <char>();
var rowCounter = 0;
try
{
using (var reader = new System.IO.StreamReader(fileInfo.FullName, Encoding.Default))
{
Char escapeCharacter = quotingCharacter;
using (var csv = new CsvReader(reader, false, delimiter, quotingCharacter, escapeCharacter, '\0', ValueTrimmingOptions.All))
{
csv.DefaultParseErrorAction = ParseErrorAction.ThrowException;
csv.SkipEmptyLines = true;
var headers = ReadHeaders(csv);
Log.Debug("Read:Headers : " + string.Join(",", headers));
sqlWriter.Init(importTask, headers);
if (!sqlWriter.EnsureFileIsUnique(fileInfo.Length))
{
Log.Debug($"Read: Import failed. The File '{importTask.file}' with size:'{fileInfo.Length}' has been allready imported.");
sqlWriter.UpdateStatusTable(-1, new TimeSpan(), fileInfo.Length);
return(-2);
}
do
{
// Read a pies of CSV
batchLineFields = ReadNextBatch(csv, importTask.batchSize);
if (batchLineFields.Count == 0)
{
break;
}
Log.Debug($"Read next batch : {batchLineFields.Count} entries start at {rowCounter} to {batchLineFields.Count + rowCounter}");
//Create & Write SQL
sqlWriter.Write(batchLineFields);
rowCounter += batchLineFields.Count;
} while (batchLineFields.Count > 0);
}
}
}
catch (Exception ex)
{
Log.Error($"ReadCsv catch exception : {ex.Message}");
return(-1);
}
_timer.Stop();
var timeSpan = TimeSpan.FromMilliseconds(_timer.ElapsedMilliseconds);
Log.Debug($"Import from '{importTask.file}' lenght:'{fileInfo.Length}' takes {_timer.ElapsedMilliseconds / 1000.0} seconds or {timeSpan.ToString("c")} of time. {rowCounter} rows inserted.");
sqlWriter.UpdateStatusTable(rowCounter, timeSpan, fileInfo.Length);
sqlWriter.ExecuteAdditionalSql();
return(rowCounter);
}
public SqlCmdBuilder(ImportFileOptions importTask)
{
ImportTask = importTask;
uniqueRows = new Dictionary <int, bool>();
}
public void Init(ImportFileOptions importTask, List <string> headers)
{
ImportTask = importTask;
Headers = headers;
Log.Debug($"SqlServerWriter: init for '{ImportTask.file}'");
}
