C# reading excel file where the header is not the first row in OLEDB

[Neraks]

Hi ,
I have this code but only import data when the column name start in first row. My column names starts in column 2 to column row 6 How I resolved this? I know i need a range but i dont know the code for that I try this but send me that error

System.InvalidOperationException: 'The ColumnName 'Atributo' the column does not march any column in the data source'.


DataTable dt = new DataTable();
conString = string.Format(conString, filePath);

using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;

//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[4]["TABLE_NAME"].ToString();
//string Range = dtExcelSchema.Rows[4]["COLUMN_NAME"].ToString();
connExcel.Close();

//Read Data from Specific Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT DISTINCT* From [4.2$A5:ZZ]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
connExcel.Close();
}
}
}

conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name.
sqlBulkCopy.DestinationTableName = "dbo.Tipo_Adquisicion";

//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Atributo", "Tipo_Adquisicion");
sqlBulkCopy.ColumnMappings.Add("PERÍODO INFORMACIÓN", "descripcion");
// sqlBulkCopy.ColumnMappings.Add("Country", "Country");

con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}

Continue reading...