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;
}
