SqlBulkCopy from XLSX file

[Michael Knope]

Hello,

I am using SqlBulkCopy to copy data from an XLSX file into my SQL database. In the XLSX extract, two columns have the same headers so the data is duplicating for each column in my SQL table. Is there anyway I can select from the XLSX column based on column number and not header?


excelCon.Open();
string excelQuery = "Select * from [Page 1$]";
OleDbCommand cmdExcel = new OleDbCommand(excelQuery, excelCon);
OleDbDataReader drExcel = cmdExcel.ExecuteReader();
using (SqlConnection sqlcon = new SqlConnection(sqlDb))
{
sqlcon.Open();
using (SqlBulkCopy excelCopy = new SqlBulkCopy(sqlDb))
{
excelCopy.ColumnMappings.Add("Request", "Request");
excelCopy.ColumnMappings.Add("Request item", "Request item");
excelCopy.ColumnMappings.Add("Number", "Number");
excelCopy.ColumnMappings.Add("Integration Ticket #", "Integration Ticket #");
excelCopy.ColumnMappings.Add("Request state", "Request state");
excelCopy.ColumnMappings.Add("State", "State");
excelCopy.ColumnMappings.Add("State", "State2");
excelCopy.ColumnMappings.Add("Opened", "Opened");
excelCopy.ColumnMappings.Add("Opened", "Opened2");
excelCopy.DestinationTableName = "tbl_All_Cerner_REQ";
excelCopy.WriteToServer(drExcel);
}
sqlcon.Close();
}

Continue reading...