public static DataTable LoadFile2DataTable(string FileName) { var sConnectionString = ""; var mdtOut = new DataTable(); DataTable mdtIn; 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 parser = new clsGenericParserAdapter()) { parser.SetDataSource(FileName); parser.ColumnDelimiter = ",".ToCharArray(); parser.FirstRowHasHeader = true; parser.MaxBufferSize = 4096; parser.TextQualifier = '\"'; mdtIn = parser.GetDataTable(); parser.Close(); mdtOut = ReplaceMissingStr(mdtIn); } break; case ".txt": using (var parser = new clsGenericParserAdapter()) { parser.SetDataSource(FileName); parser.ColumnDelimiter = "\t".ToCharArray(); parser.FirstRowHasHeader = true; parser.MaxBufferSize = 4096; parser.TextQualifier = '\"'; mdtIn = parser.GetDataTable(); parser.Close(); mdtOut = ReplaceMissingStr(mdtIn); } 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;"; objConn = new OleDbConnection(sConnectionString); objConn.Open(); dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return null; } var excelSheets = new string[dt.Rows.Count]; var i = 0; // Add the sheet name to the string array. foreach (DataRow row in dt.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } var sheetCmd = "SELECT * FROM [" + excelSheets[0] +"]"; //read the first table var objCmdSelect = new OleDbCommand(sheetCmd, objConn); var objAdapter1 = new OleDbDataAdapter { SelectCommand = objCmdSelect }; objAdapter1.Fill(mdtOut); //mdtOut = clsDataTable.ClearNulls(mdtIn) ; } 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 type"); //fileOK = false; mdtOut = null; break; } return mdtOut; }