locked
Upload data From Excel to SQL (without excel headers) RRS feed

  • Question

  • User-582711651 posted

    Dear Friends, 

    How to upload excel to SQL directly, your support is expected pls<g class="gr_ gr_296 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation multiReplace" id="296" data-gr-id="296">..</g> 

    This is my excel (no need excel headers) 

    Master Employees Report generated on June 01, 2018 9:08 AM

    Slno

    Form No

    Branch

    Cluster Name

    Group Name

    EmpName

    Branch Code

    Status

    Updated date

    Verified By Code

    Verified By Name

    Verification Date

    Credit bureau response date

    Loan Cycle

    Loan category

    Applied Amount

    Mode

    Bank Name

    Bank  Branch

    IFSC Code

    A c No

    Gender

    Marital Status

    House Hold Annual Income

    MMI Brrower Aadhar

    MMI Voter Id

    Customer Mobile Phone no

    1

     

    SRK Nagar

    MGR Colony

    SRI CHELLIYAMMAN PADI

    sathiya manivanan

    1100

    CB response authenticated

    29-Jun-2018

     

     

     

    5/29/2018 12:00:00 AM

    1

     

    30000

     

     

     

     

     

    Female

    WIDOW

     

    685123373726

    333765371063

    6381223822

    2

     

    APJK Nagar

    Ring Road Cluster

    POOMAGAL (NEW) 19576

    GANGA

    1122

    CB response authenticated

    29-Jun-2018

     

     

     

    5/29/2018 12:00:00 AM

    1

     

    20000

     

     

     

     

     

    Female

    WIDOW

     

    580175638774

    333781447171

    9003815377

     

    This is my SQL Table, 


    CREATE TABLE [dbo].[KYC_DATATBL_TXN](
    [KYC_TBL_ITD] [bigint] IDENTITY(1,1) NOT NULL,
    [KYC_FormNo] [bigint] NULL,
    [KYC_BranchName] [varchar](70) NOT NULL,
    [KYC_ClusterName] [varchar](70) NULL,
    [KYC_GroupName] [varchar](150) NULL,
    [KYC_ClientName] [varchar](150) NULL,
    [KYC_BranchID] [varchar](30) NOT NULL,
    [KYC_Status] [varchar](150) NULL,
    [KYC_UpdatedDate] [datetime] NULL,
    [KYC_VerifiedByID] [bigint] NULL,
    [KYC_VerifiedByName] [varchar](150) NULL,
    [KYC_VerificationDate] [datetime] NULL,
    [KYC_CB_ResponseDate] [datetime] NULL,
    [KYC_LoanCycleID] [int] NULL,
    [KYC_LoanCategory] [varchar](250) NULL,
    [KYC_AppliedAmount] [decimal](18, 0) NULL,
    [KYC_DisbursementMode] [varchar](20) NULL,
    [KYC_BankName] [varchar](200) NULL,
    [KYC_BankBranchDetails] [varchar](500) NULL,
    [KYC_BankIFSC] [varchar](50) NULL,
    [KYC_BankAcctNo] [varchar](70) NULL,
    [KYC_Gender] [varchar](10) NULL,
    [KYC_MaritalStatus] [varchar](20) NULL,
    [KYC_HHAnnualIncome] [decimal](18, 0) NULL,
    [KYC_MemberAadhaarID] [bigint] NOT NULL,
    [KYC_MemberVoterID] [varchar](50) NULL,
    [KYC_MemberPhoneNo] [varchar](50) NULL,
    [KYC_RecordSavedOn] [datetime] NOT NULL --- This field for date of upload

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    Saturday, June 30, 2018 5:50 AM

Answers

  • User283571144 posted

    Hi ayyappan.CNN,

    According to your description, I suggest you could  Microsoft.Jet.OLEDB  provider to help you convert excel data to datatable in memory, then we could insert the data from the memory datatable to sql database.

    Notice:

    The Microsoft.Jet.OLEDB.4.0 only support xls, it doesn’t support the file of xlsx.

    If you want to use xlsx, I suggest you could use 'OLEDB.12.0'.

    More details about how to convert the excel data to database, you could refer to below code sample:

    ASPX:

    <form id="form1" runat="server">
            <div>
              <asp:fileupload runat="server" Id="uploadControl"></asp:fileupload>
                <asp:Button ID="Button1" runat="server" Text="Button" />
            </div>
        </form>
    

    Code-behind:

        protected void Page_Load(object sender, EventArgs e)
            {
                if (IsPostBack)
                {
                    if (uploadControl.HasFile)
            
     
                    {
                        //save the file into local file system,you can name your excel as you like
                        uploadControl.SaveAs(Server.MapPath("/myExcel.xls"));
     
     
                        //write the connection string to the uploaded file,don't forget to add the Server.MapPath to get the absolute path
                        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                                                               //the  HDR=NO means not defining the first row of the sheet 
                                                                               //in your excel  as the column name 
                       "Data Source=" + Server.MapPath("/myExcel.xls") + ";" + "Extended Properties='Excel 8.0;HDR=NO;';";
     
     
                        // create  the connection and open it
                        OleDbConnection conn = new OleDbConnection(strConn);
                        conn.Open();
     
                        // specify the command through which you can retrive data from the excel, "yourSheet" is the name of the sheet in your excel
                        string strExcel = string.Format("select * from [{0}$]", "yourSheet");
     
     
                        //create the object of OleDbDataAdapter,and fill a datatable to save the data in memory
                        OleDbDataAdapter da = new OleDbDataAdapter(strExcel, conn);
                        DataTable dataTable = new DataTable();
                        da.Fill(dataTable);
     
     
                        // definite a variable to record the row index
                        int count = 0;
                        string connectStr = "data source=localhost;initial catalog=bjhksj;integrated security=true";
                        string sql = "insert into KYC_DATATBL_TXN (KYC_FormNo,KYC_BranchName,KYC_ClusterName,KYC_GroupName,KYC_ClientName,KYC_BranchID," +
                            "KYC_Status,KYC_UpdatedDate,KYC_VerifiedByID,KYC_RecordSavedOn) values(@formNo,@branch,@cluster,@group,@empName,@branchCode,@status,@updateDate,@vCode,@time)";
                        foreach (DataRow row in dataTable.Rows)
                        {
                            count++;
     
                            // filter the row you don't want to  import to your database through the row index
                            if (count>3)
                            {
                               
                               
                                //create the sqlparameters
                                SqlParameter[] sqlParameters = new SqlParameter[] {
                                    new SqlParameter("@formNo", SqlDbType.BigInt) { Value = row[dataTable.Columns[1]] },
                                     new SqlParameter("@branch", SqlDbType.VarChar, 70) { Value = row[dataTable.Columns[2]] },
                                     new SqlParameter("@cluster", SqlDbType.VarChar, 70) { Value =row[dataTable.Columns[3]] },
                                     new SqlParameter("@group", SqlDbType.VarChar, 150) { Value = row[dataTable.Columns[4]] },
                                     new SqlParameter("@empName", SqlDbType.VarChar, 150) { Value =row[dataTable.Columns[5]] },
                                     new SqlParameter("@branchCode", SqlDbType.VarChar,30) { Value = row[dataTable.Columns[6]] },
                                        new SqlParameter("@status", SqlDbType.VarChar, 150) { Value = row[dataTable.Columns[7]] },
                                     new SqlParameter(   "@updateDate", SqlDbType.DateTime) { Value = row[dataTable.Columns[8]]},
                                    new SqlParameter("@vCode", SqlDbType.BigInt) { Value = row[dataTable.Columns[9]] },
                                     new SqlParameter("@time", SqlDbType.DateTime) { Value = DateTime.Now }
                                
                                };
     
     
     
     
                              
                                using (SqlConnection con = new SqlConnection(connectStr))
                                {
                                    using (SqlCommand cmd = new SqlCommand(sql, con))
                                    {
                                        cmd.Parameters.AddRange(sqlParameters);
                                        con.Open();
                                        cmd.ExecuteNonQuery();
                                    }
     
                                }
     
     
     
     
                            }
                           
                           
                        }
     
                    }
                }
            }
     
    

    Result:

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 3, 2018 5:17 AM

All replies

  • User-1171043462 posted

    Refer this article:

    Import data from Excel to GridView in ASP.Net

    It has both with Header and without Header implementation

    Saturday, June 30, 2018 6:23 AM
  • Saturday, June 30, 2018 9:10 AM
  • User283571144 posted

    Hi ayyappan.CNN,

    According to your description, I suggest you could  Microsoft.Jet.OLEDB  provider to help you convert excel data to datatable in memory, then we could insert the data from the memory datatable to sql database.

    Notice:

    The Microsoft.Jet.OLEDB.4.0 only support xls, it doesn’t support the file of xlsx.

    If you want to use xlsx, I suggest you could use 'OLEDB.12.0'.

    More details about how to convert the excel data to database, you could refer to below code sample:

    ASPX:

    <form id="form1" runat="server">
            <div>
              <asp:fileupload runat="server" Id="uploadControl"></asp:fileupload>
                <asp:Button ID="Button1" runat="server" Text="Button" />
            </div>
        </form>
    

    Code-behind:

        protected void Page_Load(object sender, EventArgs e)
            {
                if (IsPostBack)
                {
                    if (uploadControl.HasFile)
            
     
                    {
                        //save the file into local file system,you can name your excel as you like
                        uploadControl.SaveAs(Server.MapPath("/myExcel.xls"));
     
     
                        //write the connection string to the uploaded file,don't forget to add the Server.MapPath to get the absolute path
                        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                                                               //the  HDR=NO means not defining the first row of the sheet 
                                                                               //in your excel  as the column name 
                       "Data Source=" + Server.MapPath("/myExcel.xls") + ";" + "Extended Properties='Excel 8.0;HDR=NO;';";
     
     
                        // create  the connection and open it
                        OleDbConnection conn = new OleDbConnection(strConn);
                        conn.Open();
     
                        // specify the command through which you can retrive data from the excel, "yourSheet" is the name of the sheet in your excel
                        string strExcel = string.Format("select * from [{0}$]", "yourSheet");
     
     
                        //create the object of OleDbDataAdapter,and fill a datatable to save the data in memory
                        OleDbDataAdapter da = new OleDbDataAdapter(strExcel, conn);
                        DataTable dataTable = new DataTable();
                        da.Fill(dataTable);
     
     
                        // definite a variable to record the row index
                        int count = 0;
                        string connectStr = "data source=localhost;initial catalog=bjhksj;integrated security=true";
                        string sql = "insert into KYC_DATATBL_TXN (KYC_FormNo,KYC_BranchName,KYC_ClusterName,KYC_GroupName,KYC_ClientName,KYC_BranchID," +
                            "KYC_Status,KYC_UpdatedDate,KYC_VerifiedByID,KYC_RecordSavedOn) values(@formNo,@branch,@cluster,@group,@empName,@branchCode,@status,@updateDate,@vCode,@time)";
                        foreach (DataRow row in dataTable.Rows)
                        {
                            count++;
     
                            // filter the row you don't want to  import to your database through the row index
                            if (count>3)
                            {
                               
                               
                                //create the sqlparameters
                                SqlParameter[] sqlParameters = new SqlParameter[] {
                                    new SqlParameter("@formNo", SqlDbType.BigInt) { Value = row[dataTable.Columns[1]] },
                                     new SqlParameter("@branch", SqlDbType.VarChar, 70) { Value = row[dataTable.Columns[2]] },
                                     new SqlParameter("@cluster", SqlDbType.VarChar, 70) { Value =row[dataTable.Columns[3]] },
                                     new SqlParameter("@group", SqlDbType.VarChar, 150) { Value = row[dataTable.Columns[4]] },
                                     new SqlParameter("@empName", SqlDbType.VarChar, 150) { Value =row[dataTable.Columns[5]] },
                                     new SqlParameter("@branchCode", SqlDbType.VarChar,30) { Value = row[dataTable.Columns[6]] },
                                        new SqlParameter("@status", SqlDbType.VarChar, 150) { Value = row[dataTable.Columns[7]] },
                                     new SqlParameter(   "@updateDate", SqlDbType.DateTime) { Value = row[dataTable.Columns[8]]},
                                    new SqlParameter("@vCode", SqlDbType.BigInt) { Value = row[dataTable.Columns[9]] },
                                     new SqlParameter("@time", SqlDbType.DateTime) { Value = DateTime.Now }
                                
                                };
     
     
     
     
                              
                                using (SqlConnection con = new SqlConnection(connectStr))
                                {
                                    using (SqlCommand cmd = new SqlCommand(sql, con))
                                    {
                                        cmd.Parameters.AddRange(sqlParameters);
                                        con.Open();
                                        cmd.ExecuteNonQuery();
                                    }
     
                                }
     
     
     
     
                            }
                           
                           
                        }
     
                    }
                }
            }
     
    

    Result:

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 3, 2018 5:17 AM