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