public static DataTable LoadFile2DataTableFastCSVReader(string FileName) { var sConnectionString = ""; var mdtOut = new DataTable(); var fileName = Path.GetFileName(FileName); var fExt = Path.GetExtension(fileName); if (string.IsNullOrEmpty(fExt)) { Console.WriteLine("Unknown file type"); //fileOK = false; return(null); } switch (fExt.ToLower()) { case ".csv": // CSV files using (var csv = new CsvReader(new StreamReader(new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)), true)) { csv.ParseError += csv_ParseError; csv.MissingFieldAction = MissingFieldAction.ReplaceByEmpty; mdtOut.Load(csv); } break; case ".txt": using (var csv = new CsvReader(new StreamReader(new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)), true, '\t')) { csv.ParseError += csv_ParseError; csv.MissingFieldAction = MissingFieldAction.ReplaceByEmpty; mdtOut.Load(csv); } break; case ".xls": //Excel files sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileName + ";" + "Extended Properties=Excel 8.0;"; goto case "Excel"; case ".xlsx": // New Excel files sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FileName + ";" + "Extended Properties=Excel 12.0;"; goto case "Excel"; case "Excel": OleDbConnection objConn = null; DataTable dt = null; try { //string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + // FileName + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""; objConn = new OleDbConnection(sConnectionString); objConn.Open(); dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return(null); } string mstrSheet; if (dt.Rows.Count == 1) { mstrSheet = (dt.Rows[0])["TABLE_NAME"].ToString(); } else { var marrExcelSheets = new List <string>(); var i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { mstrSheet = row["TABLE_NAME"].ToString(); marrExcelSheets.Add(mstrSheet); i++; } var mfrmSheets = new frmSelectExcelSheet { PopulateListBox = marrExcelSheets }; if (mfrmSheets.ShowDialog() == DialogResult.OK) { i = mfrmSheets.SelectedSheet; mstrSheet = marrExcelSheets[i]; } else { mdtOut = null; break; } } var sheetCmd = "SELECT * FROM [" + mstrSheet + "]"; var objCmdSelect = new OleDbCommand(sheetCmd, objConn); var objAdapter1 = new OleDbDataAdapter { SelectCommand = objCmdSelect }; objAdapter1.Fill(mdtOut); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } break; default: Console.WriteLine("Unknown File"); //fileOK = false; mdtOut = null; break; } return(mdtOut); }
private DataTable LoadExcelFile(string filePath, string connectionString) { OleDbConnection objConn = null; DataTable dt = null; var dtOut = new DataTable(); try { objConn = new OleDbConnection(connectionString); objConn.Open(); dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return(null); } string mstrSheet; if (dt.Rows.Count == 1) { mstrSheet = (dt.Rows[0])["TABLE_NAME"].ToString(); } else { var arrExcelSheets = new List <string>(); var i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { mstrSheet = row["TABLE_NAME"].ToString(); arrExcelSheets.Add(mstrSheet); i++; } var frmSheets = new frmSelectExcelSheet { PopulateListBox = arrExcelSheets }; if (frmSheets.ShowDialog() == DialogResult.OK) { i = frmSheets.SelectedSheet; mstrSheet = arrExcelSheets[i]; } else { return(null); } } var sheetCmd = "SELECT * FROM [" + mstrSheet + "]"; var objCmdSelect = new OleDbCommand(sheetCmd, objConn); var objAdapter1 = new OleDbDataAdapter { SelectCommand = objCmdSelect }; objAdapter1.Fill(dtOut); } catch (Exception ex) { ReportError($"Error opening {filePath}: {ex.Message}"); } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } dt?.Dispose(); } return(dtOut); }
public static DataTable LoadFile2DataTableFastCSVReader(string FileName) { var sConnectionString = ""; var mdtOut = new DataTable(); var fileName = Path.GetFileName(FileName); var fExt = Path.GetExtension(fileName); if (string.IsNullOrEmpty(fExt)) { Console.WriteLine("Unknown file type"); //fileOK = false; return null; } switch (fExt.ToLower()) { case ".csv": // CSV files using (var csv = new CsvReader(new StreamReader(new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)), true)) { csv.ParseError += csv_ParseError; csv.MissingFieldAction = MissingFieldAction.ReplaceByEmpty; mdtOut.Load(csv); } break; case ".txt": using (var csv = new CsvReader(new StreamReader(new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)), true, '\t')) { csv.ParseError += csv_ParseError; csv.MissingFieldAction = MissingFieldAction.ReplaceByEmpty; mdtOut.Load(csv); } break; case ".xls": //Excel files sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FileName + ";" + "Extended Properties=Excel 8.0;"; goto case "Excel"; case ".xlsx": // New Excel files sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FileName + ";" + "Extended Properties=Excel 12.0;"; goto case "Excel"; case "Excel": OleDbConnection objConn = null; DataTable dt = null; try { //string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + // FileName + ";" + @"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""; objConn = new OleDbConnection(sConnectionString); objConn.Open(); dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } string mstrSheet; if (dt.Rows.Count == 1) mstrSheet = (dt.Rows[0])["TABLE_NAME"].ToString(); else { var marrExcelSheets = new List<string>(); var i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { mstrSheet = row["TABLE_NAME"].ToString(); marrExcelSheets.Add(mstrSheet); i++; } var mfrmSheets = new frmSelectExcelSheet { PopulateListBox = marrExcelSheets }; if (mfrmSheets.ShowDialog() == DialogResult.OK) { i = mfrmSheets.SelectedSheet; mstrSheet = marrExcelSheets[i]; } else { mdtOut = null; break; } } var sheetCmd = "SELECT * FROM [" + mstrSheet + "]"; var objCmdSelect = new OleDbCommand(sheetCmd, objConn); var objAdapter1 = new OleDbDataAdapter { SelectCommand = objCmdSelect }; objAdapter1.Fill(mdtOut); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // Clean up. if (objConn != null) { objConn.Close(); objConn.Dispose(); } if (dt != null) { dt.Dispose(); } } break; default: Console.WriteLine("Unknown File"); //fileOK = false; mdtOut = null; break; } return mdtOut; }