Popular Posts

Feb 25, 2013

Import data from Excel to sqlserver with additional field


-> In excel file

             ID           Name Location1
1   Jubayer  Dhaka
         2    Jakia Dhaka
3   Khalid Dhaka
4   Jamal Manikgonj
5   Alim West




 ->create a database and table in sql server

 ->c# code


<div>
   
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
   
    </div>



if (FileUpload1.HasFile)   //Upload file here
            {

                FileUpload1.SaveAs(@"C:/temp/" + FileUpload1.FileName);

                string pat = @"C:/temp/" + FileUpload1.FileName;
                string path2 = string.Concat(Server.MapPath("App_Data/upload/" + FileUpload1.FileName));
                string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + pat + "';Extended Properties=" + '"' + "Excel 8.0;HDR=YES;" + '"';
                string file_out_ex = Path.GetFileNameWithoutExtension(path2);
               
                string CONSTANTVALUE = "Test";

//                string excelsql = "SELECT * FROM [Details$]";

                string excelsql = "select ID, Location1, '" + CONSTANTVALUE + "' as EXCELCUSTOM from [Details$]";
              

                using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
                {
                    OleDbCommand command = new OleDbCommand(excelsql, connection);
                    connection.Open();
                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        string sqlConnectionString = ConfigurationManager.ConnectionStrings["db_connection_string"].ConnectionString;


                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
                        {
                            bulkCopy.DestinationTableName = "Cities";
                            SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("ID", "long");
                            SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("Location1", "city");
                            SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("EXCELCUSTOM", "lati");
                          //  bulkCopy.ColumnMappings.Add("TABLECOLUMN3", "ConstantCol");

                           // "TABLECOLUMN1", "EXCELCOLUMN1"
                            bulkCopy.ColumnMappings.Add(mapping1);
                            bulkCopy.ColumnMappings.Add(mapping2);
                            bulkCopy.ColumnMappings.Add(mapping3);



                            try
                            {
                                bulkCopy.WriteToServer(dr);
                            }
                            catch (Exception ex)
                            {
                                Label1.Text = "The following problems occurs: " + ex.Message;
                            }
                            finally
                            {
                                dr.Close();
                            }
                        }
                    }
                }
            }
        }catch(Exception ex){
            throw ex;
        }