private string GetInsertFmtForNormalCols(Flatfile flatfile, bool insertLineNum) { var sql = insertLineNum ? ", LineNum" : ""; sql = string.Join(",", flatfile.Schemafile.Fields.Where(x => x.ColumnType == ColumnType.Normal).Select(x => x.Name).ToList()) + sql; return("INSERT INTO " + flatfile.Tablename + " (" + sql + ") VALUES ({0});"); }
private string GetInsertStmtForNormalCols(Flatfile flatfile, bool insertLineNum) { var sql = insertLineNum ? ", LineNum" : ""; var lncnt = insertLineNum ? 1 : 0; var qmark = ""; var colList = flatfile.Schemafile.Fields.Where(x => x.ColumnType == ColumnType.Normal).Select(x => x.Name).ToList(); sql = string.Join(",", colList) + sql; for (int i = 1; i <= colList.Count + lncnt; i++) { qmark += "?" + i.ToString() + ","; } return("INSERT INTO " + flatfile.Tablename + " (" + sql + ") VALUES (" + qmark.Substring(0, qmark.Length - 1) + ")"); }
public int ImportDelimitedFile(Flatfile flatfile, out int linesRead, ConfigFile configFile, bool insertLineNum = false) { int i = 1; int sqliteStatus = 0; try { string insertStmt = GetInsertStmtForNormalCols(flatfile, insertLineNum); raw.sqlite3_exec(RawDbConnection, "BEGIN"); //NPS_TODO : Get the column count of the table // rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0) where zSQL is a select * from col // if no rc, then create table // then // nCol = sqlite3_column_count(pStmt); sqlite3_stmt stmt; bool skipRows = flatfile.Schemafile.SkipRows != 0; sqliteStatus = raw.sqlite3_prepare_v2(RawDbConnection, insertStmt, out stmt); using (StreamReader reader = File.OpenText(flatfile.Filename)) { var parser = new CsvParser(reader); parser.Configuration.Delimiter = flatfile.Delimiter; while (true) { //Console.WriteLine("1- "+DateTime.Now.Ticks); var line = parser.Read(); if (line == null) { break; } //Console.WriteLine("2- " + DateTime.Now.Ticks); if (skipRows && i - 1 < flatfile.Schemafile.SkipRows) { i++; continue; } //Console.WriteLine("3- " + DateTime.Now.Ticks); int columnCount = flatfile.Schemafile.Fields.Count - 1; if ((i == 1 || (skipRows && (i - 1) == flatfile.Schemafile.SkipRows)) && flatfile.HasHeaderRow) { flatfile.Headers = line; if (flatfile.HasValidHeaderCount()) { var invalidHeaders = flatfile.ValidateHeaderNames(); if (!String.IsNullOrEmpty(invalidHeaders)) { sqliteStatus = raw.sqlite3_exec(RawDbConnection, "INSERT INTO GeneralErrors VALUES('Malformed Header','None', + '" + flatfile.Tablename.ToUpper() + "', '" + configFile.Validate.SchemaErrorSettings.MalformedHeaderErrorLevel + "', 'Malformed Header Row - " + invalidHeaders + "');"); } } i++; continue; } //Console.WriteLine("5- " + DateTime.Now.Ticks); var linenum = insertLineNum ? "," + parser.Row : ""; List <string> newLine = new List <string>(); if ((line.Count() > columnCount) && configFile.Extract.IgnoreTooManyColumns) { for (var num = 0; num < flatfile.Schemafile.Fields.Count - 1; num++) { newLine.Add(line[num]); } line = newLine.ToArray(); } //Console.WriteLine("6- " + DateTime.Now.Ticks); if ((line.Where(x => x == null).Count() > 0) && configFile.Extract.MissingColumns.MissingColumnHandling.ToLower() == "fill") { newLine = line.Where(col => col != null).ToList(); var fillVal = configFile.Extract.MissingColumns.MissingColumnFillValue; while (newLine.Count < columnCount) { newLine.Add(fillVal); } line = newLine.ToArray(); } //Console.WriteLine("7- " + DateTime.Now.Ticks); raw.sqlite3_reset(stmt); string sql = ""; //string sql = String.Format(insertStmt, String.Join(@",", SanitizeInputs(line)) + linenum) + Environment.NewLine; for (int k = 1; k <= line.Length; k++) { sqliteStatus = raw.sqlite3_bind_text(stmt, k, line[k - 1]); } if (linenum != "") { sqliteStatus = raw.sqlite3_bind_text(stmt, line.Length + 1, i.ToString()); } //Console.WriteLine("8- " + DateTime.Now.Ticks); //sqliteStatus = raw.sqlite3_exec(RawDbConnection, sql); raw.sqlite3_step(stmt); raw.sqlite3_clear_bindings(stmt); //Console.WriteLine("9- " + DateTime.Now.Ticks); // This next line really implies that MissingColumnHandling = error if ((sqliteStatus != raw.SQLITE_OK && sqliteStatus != raw.SQLITE_DONE)) { string error = raw.sqlite3_errmsg(RawDbConnection); sqliteStatus = raw.sqlite3_exec(RawDbConnection, "INSERT INTO GeneralErrors VALUES('Insert Error', 'None','" + flatfile.Tablename.ToUpper() + "', 'Error','Failed to import lines in " + flatfile.Filename + " due to error: " + error + Environment.NewLine + "Line: " + i + ": " + sql.Replace("'", "''") + "')"); if (configFile.Db.StopOnError) { raw.sqlite3_finalize(stmt); raw.sqlite3_exec(RawDbConnection, "COMMIT"); break; } } i++; } } raw.sqlite3_finalize(stmt); raw.sqlite3_exec(RawDbConnection, "COMMIT"); linesRead = i; return((int)ErrorCode.Ok); } catch (Exception e) { linesRead = i; return(sqliteStatus); } }