How to insert data from excel to datatable which has headers?

Question

I am reading excel having like million of records first i query my table (no records) and get Datable. i query my table to get columns name as define in my excel sheet using alias.

   var dal = new clsConn();

                var sqlQuery = "SELECT FETAPE_THEIR_TRANDATE \"Date\" ,ISSUER Issuer, ISSU_BRAN Branch , STAN_NUMB STAN, TERMID TermID, ACQUIRER Acquirer,DEBIT_AMOUNT Debit,CREDIT_AMOUNT Credit,CARD_NUMB \"Card Number\" , DESCRIPTION Description FROM ALLTRANSACTIONS";
                var returntable = dal.ReadData(sqlQuery);
                DataRow ds = returntable.NewRow();
                var dtExcelData = returntable;

So my datatable looks like this,

Then i read records from excel sheet

  OleDbConnection con = null;
                if (ext == ".xls")
                {
                    con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;");

                }
                else if (ext == ".xlsx")
                {
                    con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;IMEX=2;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"");
                }
                con.Open();
                dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString();
                //OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con);
                OleDbCommand ExcelCommand = new OleDbCommand("SELECT F1, F2, F3, F4, F5,F6,F7,F8,F9,F10 FROM [Sheet1$]", con);

                OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);

                try
                {
                    ExcelAdapter.Fill(dtExcelData); //Here I give the datatable which i made previously
                }
                catch (Exception ex)
                {
                    //lblAlert2.CssClass = "message-error";
                    //lblAlert2.Text = ex.Message;
                }

It reads successfully and fill data in datatable but creating its own column in data table like F1 to F10 how can i move this data to exactly match with my defined columns in datatable

How Will i manage this to not create other columns (f1,f2..f10) any workaround will be appreciable or Please explain what i am doing wrong and how can i achieve this.

UPDATE : My Excel file looks like this

Answer 1

The Microsoft.ACE.OLEDB.12.0 driver will handle both types of excel spreadsheets and using the same Extended Properties. i.e. "Excel 12.0" will open both .xls and .xlsx.

Leave the HDR=NO as OLEDB expects them in the first row and they are actually in row 11.

Sadly "TypeGuessRows=0;ImportMixedTypes=Text" is completely ignored by Microsoft.ACE.OLEDB.12.0, you've got to play around with the registry (yuk). Change your IMEX=2 to IMEX=1 to ensure that mixed data types as handled as text.

Change back to using "Select * From [Sheet1$]" and then I'm afriad that you are going to have to handle the source data manually.

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO\"");
con.Open();

DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string getExcelSheetName = (string)dt.Rows[0]["Table_Name"];

DataTable xlWorksheet = new DataTable();

xlWorksheet.Load(new OleDbCommand("Select * From [" + getExcelSheetName + "]", con).ExecuteReader());

//More than 11 rows implies 11 header rows and at least 1 data row
if (xlWorksheet.Rows.Count > 11 & xlWorksheet.Columns.Count >= 10)
{
    for (int nRow = 11; nRow < xlWorksheet.Rows.Count; nRow++)
    {
        DataRow returnRow = returntable.NewRow();

        for (int nColumn = 0; nColumn < 10; nColumn++)
        {
            //Note you will probably get conversion problems here that you will have to handle
            returnRow[nColumn] = xlWorksheet.Rows[nRow].ItemArray[nColumn];
        }
        returntable.Rows.Add(returnRow);
    }
}

I'm guessing you simply want to add the excel data into your ALLTRANSACTION table? You don't specify but it seems the likely outcome of this. If so this is a terrible way to do it. You don't need to read the whole table into memory append data and then update the database. All you need to do is read the excel file and insert the data to the Oracle table.

Some thoughts, your returntable will contain data so if you just want the structure of the table then add a "Where RowNum=0" to the Select statement. To add the data to your Oracle Database you could 1) Convert to using the Oracle Data Provider (ODP) and then use using OracleBulkCopy Class or 2) simply modify the above to insert row by row as you read the data. As long as you don't have a LOT of data in your Excel spreadsheet it will work just fine. Having said that a Million rows is a LOT so perhaps not the best option. You will need to validate the input as Excel is not the best data source really.