public override ImportRowSource CreateRowSource(IProgressObserver progress) { if (_options == null) { throw new Exception("Null or incorrect options type received!"); } ImportRowSource rowsource = null; using (var parser = new GenericParserAdapter(_options.Filename)) { parser.ColumnDelimiter = _options.Delimiter[0]; parser.FirstRowHasHeader = _options.FirstRowContainsNames; parser.TextQualifier = '\"'; parser.FirstRowSetsExpectedColumnCount = true; var service = new ImportStagingService(); var columnNames = new List <String>(); int rowCount = 0; service.BeginTransaction(); var values = new List <string>(); while (parser.Read()) { if (rowCount == 0) { for (int i = 0; i < parser.ColumnCount; ++i) { if (_options.FirstRowContainsNames) { columnNames.Add(parser.GetColumnName(i)); } else { columnNames.Add("Column" + i); } } service.CreateImportTable(columnNames); } values.Clear(); for (int i = 0; i < parser.ColumnCount; ++i) { values.Add(parser[i]); } service.InsertImportRow(values); rowCount++; } service.CommitTransaction(); rowsource = new ImportRowSource(service, rowCount); } return(rowsource); }
private bool Validate() { if (string.IsNullOrWhiteSpace(txtFilename.Text)) { ErrorMessage.Show("You must specify an input file!"); return(false); } if (!File.Exists(txtFilename.Text)) { ErrorMessage.Show("File does not exist!"); return(false); } // Extract Column Headers... ColumnNames = new List <String>(); GenericParser parser = null; try { using (parser = new GenericParserAdapter(Filename)) { parser.ColumnDelimiter = Delimiter.ToCharArray()[0]; parser.FirstRowHasHeader = IsFirstRowContainNames; parser.MaxRows = 2; parser.TextQualifier = _textQualifier; if (parser.Read()) { for (int i = 0; i < parser.ColumnCount; ++i) { if (IsFirstRowContainNames) { ColumnNames.Add(parser.GetColumnName(i)); } else { ColumnNames.Add("Column" + i); } } } else { ErrorMessage.Show("Failed to extract column names from data source!"); return(false); } } } finally { if (parser != null) { System.GC.SuppressFinalize(parser); } } return(true); }
private void btnBrowseForDataFile_Click(object sender, EventArgs e) { diagOpenFile.ShowDialog(); if (!String.IsNullOrEmpty(diagOpenFile.FileName)) { txtDataFile.Text = diagOpenFile.FileName; using (GenericParserAdapter parser = new GenericParserAdapter(diagOpenFile.FileName)) { parser.FirstRowHasHeader = true; parser.Read(); sourceData = parser.GetDataTable(); } foreach (DataColumn column in sourceData.Columns) { cboColumnNames.Items.Add(column.ColumnName); } } }
protected void IterateOverCSVZipEntry(ZipEntry entry, Func <GenericParserAdapter, bool> func) { using (var reader = new StreamReader(entry.OpenReader())) { using (var parser = new GenericParserAdapter(reader)) { parser.ColumnDelimiter = ','; parser.FirstRowHasHeader = true; parser.TextQualifier = '\"'; parser.FirstRowSetsExpectedColumnCount = true; while (parser.Read()) { if (func != null) { if (!func(parser)) { return; } } } } } }
public async Task BulkLoadFile(string databaseName, string tableName, string csvFilePath) { // Take care of the SQL Connection SqlConnectionStringBuilder sqlStringBuilder = new SqlConnectionStringBuilder(_sqlConnectionString) { InitialCatalog = databaseName }; var sqlCnn = new SqlConnection(sqlStringBuilder.ConnectionString); sqlCnn.Open(); // Create a data table based on the structure of the table we want to bulk insert into // Use 1=2 as a quick way to keep the table empty var thisAdapter = new SqlDataAdapter(); var tableSelectSqlCmd = "Select * from {0} where 1=2"; thisAdapter.SelectCommand = new SqlCommand(String.Format(tableSelectSqlCmd, tableName), sqlCnn); // Use a DataTable as a layer in between reading the CSV file and Bulk Inserting // Bulk insert does not support quoted CSV etc // Using a CSV parser + a DataTable to provide a streaming interface to SqlBulkCopy is a way around this var thisDt = new DataTable(); thisAdapter.Fill(thisDt); // keep a copy of the data table structure var templateDt = thisDt.Clone(); var batchTrack = 0; try { using (var csvParser = new GenericParserAdapter(csvFilePath)) { csvParser.FirstRowHasHeader = false; csvParser.MaxBufferSize = CSV_PARSER_BUFFER_SIZE; // iterate through the CSV file while (csvParser.Read()) { // write out a batch from the data table if (batchTrack == DATATABLE_BATCH_SIZE) { await BulkInsertDataTable(sqlCnn, thisDt, tableName); thisDt.Dispose(); thisDt = null; // reset the data table to the template thisDt = templateDt.Clone(); batchTrack = 0; } // Parse each column of the row based on tables meta data // Assumes the table structure of the Target DB and incoming CSV files match // Ensure Null values are converted to DBNull var thisDr = thisDt.NewRow(); for (var i = 2; i < csvParser.ColumnCount; i++) { var targetCol = i - 2; thisDr[targetCol] = ProcessColumn(csvParser[i], thisDt.Columns[targetCol].DataType); } thisDt.Rows.Add(thisDr); batchTrack++; } // Write out the final batch await BulkInsertDataTable(sqlCnn, thisDt, tableName); // Cleanup thisAdapter.Dispose(); thisDt.Dispose(); thisDt = null; } sqlCnn.Close(); sqlCnn.Dispose(); } catch (Exception e) { Console.WriteLine(e); } }
public async Task BulkLoadAndUpsertFile(string databaseName, string tableName, string csvFilePath) { // Take care of the SQL Connection SqlConnectionStringBuilder sqlStringBuilder = new SqlConnectionStringBuilder(_sqlConnectionString) { InitialCatalog = databaseName }; var sqlCnn = new SqlConnection(sqlStringBuilder.ConnectionString); sqlCnn.Open(); CreateCtrlTbl(sqlCnn, databaseName); var columnList = CreateTempTable(sqlCnn, databaseName, tableName); // Create a data table based on the structure of the tmp table we want to bulk insert into // Use 1=2 as a quick way to keep the table empty var thisAdapter = new SqlDataAdapter(); var thisSql = "Select * from {0} where 1=2"; thisAdapter.SelectCommand = new SqlCommand(String.Format(thisSql, "sqlTmp"), sqlCnn); // Use a DataTable as a layer in between reading the CSV file and Bulk Inserting // Bulk insert does not support quoted CSV etc // Using a CSV parser + a DataTable to provide a streaming interface to SqlBulkCopy is a way around this var thisDt = new DataTable(); thisAdapter.Fill(thisDt); // keep a copy of the data table structure var templateDt = thisDt.Clone(); var batchTrack = 0; var tmpIndexCnt = 0; var cntDelLines = 0; var cntInsLines = 0; var cntUpdLines = 0; try { using (var parser = new GenericParserAdapter(csvFilePath)) { parser.FirstRowHasHeader = false; parser.MaxBufferSize = CSV_PARSER_BUFFER_SIZE; // iterate through the CSV file while (parser.Read()) { // write out a batch from the data table if (batchTrack == DATATABLE_BATCH_SIZE) { //Console.WriteLine("Task ID:::" + taskId + "(" + tableName + ")" + "Writing out a batch of " + batchTrack); await BulkInsertDataTable(sqlCnn, thisDt, "sqlTmp"); thisDt.Dispose(); thisDt = null; // reset the data table to the template thisDt = templateDt.Clone(); batchTrack = 0; } // Parse each column of the row based on tables meta data // Assumes the table structure of the Target DB and incoming CSV files match // Ensure Null values are converted to DBNull var thisDr = thisDt.NewRow(); // populate additional key to track records uniquely in the sqlTmp table thisDr[0] = tmpIndexCnt; for (var i = 0; i < parser.ColumnCount; i++) { thisDr[i + 1] = ProcessColumn(parser[i], thisDt.Columns[i + 1].DataType); } thisDt.Rows.Add(thisDr); batchTrack++; tmpIndexCnt++; // keep track of record counts by operation if (thisDr[1].Equals("I")) { cntInsLines++; } else if (thisDr[1].Equals("U")) { cntUpdLines++; } else if (thisDr[1].Equals("D")) { cntDelLines++; } else { //Should never get here?! } } // Write out the final batch await BulkInsertDataTable(sqlCnn, thisDt, "sqlTmp"); // Cleanup thisAdapter.Dispose(); thisDt.Dispose(); thisDt = null; } // Do a form of UPSERT based on the incoming file having // I = insert // U = update // D = delete // first delete records matching any operation in target table var delCmd = "DELETE TARGET FROM dbo.{0} as TARGET INNER JOIN dbo.sqlTmp SOURCE on TARGET.{1} = SOURCE.{2} and SOURCE.[oper] in ('I','U',\'D\')"; await using (var cmd = new SqlCommand(String.Format(delCmd, tableName, columnList[0], columnList[0]), sqlCnn)) { cmd.CommandTimeout = 120; cmd.ExecuteNonQuery(); } // now insert U and I rows only var insertCmd = "INSERT INTO dbo.{0} ({1}) SELECT {2} FROM sqlTmp SOURCE where SOURCE.oper in ('I', 'U') and SOURCE.[index] = (select Max(SOURCE2.[index]) from sqlTmp SOURCE2 where SOURCE2.id = SOURCE.id)"; await using (var cmd = new SqlCommand( String.Format(insertCmd, tableName, string.Join(",", columnList), string.Join(",", columnList)), sqlCnn)) { cmd.CommandTimeout = 120; cmd.ExecuteNonQuery(); } // finally insert a control line var ctrlInsertCmd = "INSERT INTO dbo.repLog ([file],[table],[delCnt],[insCnt],[updCnt]) VALUES ('{0}','{1}','{2}','{3}','{4}')"; await using (var cmd = new SqlCommand(String.Format(ctrlInsertCmd, Path.GetFileName(csvFilePath), tableName, cntDelLines, cntInsLines, cntUpdLines), sqlCnn)) { cmd.CommandTimeout = 120; cmd.ExecuteNonQuery(); } sqlCnn.Close(); sqlCnn.Dispose(); } catch (Exception e) { Console.WriteLine(e); } //var endDttm = DateTime.Now; //var span = endDttm.Subtract(startDttm); //Console.WriteLine(endDttm + ":: Finished Task ID:::" + taskId + "(" + tableName + ")::: " + "Duration (minutes): " + span.TotalMinutes); }
/// <summary> /// Read Data from selected csv file /// </summary> public void Parse() { if (_data != null) { return; } try { _data = new DataTable(); var csv = FromK2File(_settings.File); using (var textReader = new StringReader(csv)) using (var parser = new GenericParserAdapter(textReader)) { parser.ColumnDelimiter = _settings.ColumnDelimiter; parser.TextQualifier = _settings.TextQualifier; parser.FirstRowHasHeader = true; parser.StripControlChars = true; var isFirstRow = true; while (parser.Read()) { if (isFirstRow) { for (var i = 0; i < parser.ColumnCount; i++) { var columnName = parser.GetColumnName(i).FormatColumnName(_settings.HeaderRowSpaces); _data.Columns.Add(columnName); } isFirstRow = false; } var newRow = _data.NewRow(); for (var i = 0; i < parser.ColumnCount; i++) { newRow[i] = parser[i]; } _data.Rows.Add(newRow); } } if (_data.Columns.Count == 0) { Status = ImportStatus.NoColumnsFound; Message = "No columns found."; } else if (_data.Rows.Count == 0) { Status = ImportStatus.NoRowsFound; Message = "No rows found."; } else { Status = ImportStatus.Complete; Message = $"{_data.Columns.Count} columns found: " + $"'{string.Join("', '", _data.Columns.Cast<DataColumn>().Select(c => c.ColumnName))}'. " + $"{_data.Rows.Count} rows parsed for import. "; } } catch (Exception ex) { Status = ImportStatus.Error; Message = ex.Message; } }
private void DoImport() { if (String.IsNullOrEmpty(txtFilename.Text)) { ErrorMessage.Show("You must select a file before proceeding!"); return; } int rowCount = 0; var service = new SupportService(User); using (var parser = new GenericParserAdapter(txtFilename.Text)) { parser.ColumnDelimiter = ','; parser.FirstRowHasHeader = chkFirstRowHeaders.IsChecked.GetValueOrDefault(false); parser.TextQualifier = '\"'; parser.FirstRowSetsExpectedColumnCount = true; var columnNames = new List <String>(); var values = new List <string>(); while (parser.Read()) { if (rowCount == 0) { for (int i = 0; i < parser.ColumnCount; ++i) { if (parser.FirstRowHasHeader) { columnNames.Add(parser.GetColumnName(i)); } else { columnNames.Add("Column" + i); } } } values.Clear(); for (int i = 0; i < parser.ColumnCount; ++i) { values.Add(parser[i]); } String strFullPath = null; if (values.Count == 0) { strFullPath = values[0]; } else { strFullPath = values.Join("\\"); } if (!String.IsNullOrWhiteSpace(strFullPath)) { service.GetDistributionIdFromPath(strFullPath); lblProgress.InvokeIfRequired(() => { lblProgress.Content = strFullPath; lblProgress.UpdateLayout(); }); rowCount++; } } } lblProgress.InvokeIfRequired(() => { lblProgress.Content = String.Format("{0} rows processed.", rowCount); }); btnCancel.Content = "_Close"; }