CODEWITHSUNDEEP
c#sqlexcel
afrikaan
afrikaan

Reputation: 425

select query at excel sheet

i have a problem with select query. My aim is selecting some columns in excel sheet and copy them in my sql table. i have six colums to copy . When i tried to copy orders changing because of the identity column.

Here is my code;

  string path = Server.MapPath(Session["excel_sheet"].ToString());
        //Create connection string to Excel work book
        string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        //Create OleDbCommand to fetch data from Excel
        OleDbCommand cmd2 = new OleDbCommand("Select [column_1],[column_2],[column_3],[column_4],[column_5],[column_6] from [Sheet1$]", excelConnection);
        excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd2.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(con);
        //Give your Destination table name
        sqlBulk.DestinationTableName = "MYtable";
        sqlBulk.WriteToServer(dReader);
        excelConnection.Close();

my table has columns like this identity_column,column_1,column_2,column_3,column_4,column_5,column_6.

After the copy for example in excel column_4 strings writes to sql column_3 it must write to column_4 . i'm wating for an answer

Upvotes: 0

Views: 3167

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

You can iterate through the columns and map them, this will map the column names in excel to the same name in the connection (ie Excel column_1 = sql column_1)

    SqlBulkCopy sqlBulk = new SqlBulkCopy(con);
       //Define column mappings 

    for (int i = 0; i < dReader.FieldCount; i++)
    {
        sqlBulk.ColumnMappings.Add(dReader.GetName(i), dReader.GetName(i));
     }

Upvotes: 0

dittu
dittu

Reputation: 78

You should use ColumnMappings property of SqlBulkCopy Class.

for ex:

sqlBulk.ColumnMappings.Add("column_1", "column_1");

sqlBulk.DestinationTableName = "MYtable";

Upvotes: 0

Related Questions