If you are using ODBC connection to retrieve the data, use the OdbcConnection.GetSchema("Columns") option to retrieve the columns list.
Build your own filter condition to exclude the unwanted records.
Code Sample:
private static DataTable ReadExcelData(string filePath)
{
string excelConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\\T\\Test.xls;";
excelConnectionString = excelConnectionString.Replace("{fileName}", filePath);
OdbcConnection odbcConnection = new OdbcConnection();
OdbcCommand command = new OdbcCommand();
OdbcDataAdapter adapter = null;
DataSet resultSet = new DataSet();
DataTable returnTable = new DataTable();
DataTable dtExcelsheetName = new DataTable();
try
{
odbcConnection.ConnectionString = excelConnectionString;
odbcConnection.Open();
#region For Getting the Column Names List and Building the Filter Condition
dtExcelsheetName = odbcConnection.GetSchema("Columns");
StringBuilder filterCondition = new StringBuilder();
foreach (DataRow column in dtExcelsheetName.Rows)
{
filterCondition.Append(column["Column_Name"] + " <> ''");
filterCondition.Append(" or ");
}
filterCondition = filterCondition.Remove(filterCondition.Length - 3, 3);
#endregion
command.CommandText = "Select * from [" + dtExcelsheetName.Rows[0]["TABLE_NAME"].ToString() + "] where (" + filterCondition.ToString() + ");";
command.CommandType = CommandType.Text;
command.Connection = odbcConnection;
adapter = new OdbcDataAdapter(command);
adapter.FillSchema(resultSet, SchemaType.Source);
adapter.Fill(resultSet);
if (resultSet != null)
{
if (resultSet.Tables.Count > 0)
{
returnTable = resultSet.Tables[0].Copy();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (resultSet != null)
{
resultSet.Dispose();
resultSet = null;
}
if (adapter != null)
{
adapter.Dispose();
adapter = null;
}
if (command != null)
{
command.Dispose();
command = null;
}
if (odbcConnection.State == ConnectionState.Open)
odbcConnection.Close();
odbcConnection.Dispose();
odbcConnection = null;
}
return (returnTable);
}