Import from Excel to specific columns in SQL Server

Question

I want to import a specific column from an Excel sheet and to be inserted into a specific column in the SQL Server table.

The code below does not insert any data into the table, I think because the primary key thing it show the data in grid view successfully but not insert the data so, I want to insert into one column not the whole table :

protected void ImportButton_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
        string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

        string FilePath = Server.MapPath(FolderPath + FileName);
        FileUpload1.SaveAs(FilePath);
        Import_To_Grid(FilePath);
    }
}

private void Import_To_Grid(string FilePath)
{
   string conStr = "";
   //switch (Extension)
   //{
   //    case ".xls": //Excel 97-03
   //        conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
   //                 .ConnectionString;
   //        break;
   //    case ".xlsx": //Excel 07
   //        conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
   //                  .ConnectionString;
   //        break;
   //}
   String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", FilePath);

   conStr = String.Format(excelConnString, FilePath);

   OleDbConnection connExcel = new OleDbConnection(conStr);
   OleDbCommand cmdExcel = new OleDbCommand();
   OleDbDataAdapter oda = new OleDbDataAdapter();

   DataTable dt = new DataTable();
   cmdExcel.Connection = connExcel;

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

   //Read Data from First Sheet
   connExcel.Open();
   cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
   oda.SelectCommand = cmdExcel;
   oda.Fill(dt);

   using (OleDbDataReader dReader = cmdExcel.ExecuteReader())
   {
       using (SqlBulkCopy sqlBulk = new SqlBulkCopy(_Conn.ConnStr()))
       {
           //Set your Destination table name 
           sqlBulk.DestinationTableName = "[AIS].[Inventory].[Inventory_Movments_TBL]";
           sqlBulk.WriteToServer(dt);

           //Bind Data to GridView
           GridView1.Caption = Path.GetFileName(FilePath);
           GridView1.DataSource = dt;
           GridView1.DataBind();
       }
   }

   connExcel.Close();
}

Clive Ciappara · Accepted Answer · 2017-09-30 11:09:35Z

1

I suggest you use SqlConnection as follows (unless you already have an SqlConnection in the '_Conn' variable):

    using (SqlConnection con = new SqlConnection(_Conn.ConnStr()))
    {
        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
        {
            //Set the database table name
            sqlBulkCopy.DestinationTableName = "dbo.tblPersons";

            //Might be a good idea to map excel columns with that of the database table (optional)
            sqlBulk.ColumnMappings.Add("Id", "PersonId");
            sqlBulk.ColumnMappings.Add("Name", "Name");
            con.Open(); // these might be the issue since you need to open..
            sqlBulk.WriteToServer(dtExcelData);
            con.Close(); // ..and close the connection
        }
    }

Also I would remove the open and close connections from here:

connExcel.Close();

//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);

An instead do 1 connection close after the fill (and remove the bottom 'connExcel.Close();' as well):

//Read Data from First Sheet
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();

answered Sep 30, 2017 at 11:09

Clive Ciappara

5585 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mohamed elyamani Over a year ago

with my all respect my code works fine on another table which doesn`t have PK just has one column , i will try your changes

Collectives™ on Stack Overflow

Import from Excel to specific columns in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related