public DataTable ReadCsv(string filename, string delimiter = "", string table = "") { DataTable dt = new DataTable(); long totalLines = 0; try { string Feedback = string.Empty; string[] strArray; DataRow row; FileStream fileStream = new FileStream(filename, FileMode.Open, FileAccess.Read); using (StreamReader streamReader = new StreamReader(fileStream)) { string charsRead = streamReader.ReadLine(); if (delimiter.ToLower().Contains("\\t")) { delimiter = "\t"; } strArray = charsRead.Split(delimiter.ToCharArray()); for (int i = 0; i <= strArray.Length - 1; i++) { dt.Columns.Add(strArray[i].ToString(), typeof(string)); } CreateTableShema createTableShema = new CreateTableShema(); bool result = createTableShema.GetTableMetadata(table + "_Original", dt); if (result == false) { dt.Clear(); string connString = ConfigurationManager.ConnectionStrings["DataBaseConnectionString"].ConnectionString; DataTable TempDt = new DataTable(); //make our connection and dispose at the end SqlDataAdapter adt = new SqlDataAdapter("select * from " + table + "", connString); adt.Fill(TempDt); long linesProcessed = 0; for (int i = 0; i <= TempDt.Rows.Count - 1; i++) { if (i < 100000) { row = dt.NewRow(); string chararray = TempDt.Rows[i][1].ToString(); row.ItemArray = chararray.Split(delimiter.ToCharArray()); dt.Rows.Add(row); linesProcessed += 1; totalLines += 1; } else { SQLBulkCopy sql = new SQLBulkCopy(); string rees = sql.ProcessBulkCopy(dt, table + "_Original"); dt.Clear(); linesProcessed = 0; } } } } } catch (Exception err) { } return(dt); }
public DataTable csv(string filename, string delimiter = "", string table = "") { DataTable dt = new DataTable(); long totalLines = 0; try { string Feedback = string.Empty; string[] strArray; DataRow row; FileStream fileStream = new FileStream(filename, FileMode.Open, FileAccess.Read); using (StreamReader streamReader = new StreamReader(fileStream)) { string charsRead = streamReader.ReadLine(); if (delimiter.ToLower().Contains("\\t")) { delimiter = "\t"; } strArray = charsRead.Split(delimiter.ToCharArray()); for (int i = 0; i <= strArray.Length - 1; i++) { dt.Columns.Add(strArray[i].ToString(), typeof(string)); } CreateTableShema createTableShema = new CreateTableShema(); bool result = createTableShema.GetTableMetadata(table, dt); if (result == false) { long linesProcessed = 0; while (!String.IsNullOrEmpty(charsRead)) { if (linesProcessed < 100000) { charsRead = streamReader.ReadLine(); row = dt.NewRow(); UTF8Encoding utf8 = new UTF8Encoding(); var preamble = utf8.GetPreamble(); row.ItemArray = charsRead.Split(delimiter.ToCharArray()); dt.Rows.Add(row); linesProcessed += 1; totalLines += 1; } else { SQLBulkCopy sql = new SQLBulkCopy(); string rees = sql.ProcessBulkCopy(dt, table); dt.Clear(); linesProcessed = 0; } } //while loop endpoint } //result loop endpoint } } catch (Exception err) { } return(dt); }