I would like to import excel sheet data in my access database table

Question

0.00/5 (No votes)

See more:

my design code like

ASP.NET

<body>
    <form id="form1"  runat="server">
    <div>
    <asp:FileUpload ID="FileUpload1" runat="server" />
    <asp:Button ID="Button1" Text="Upload" OnClick = "Upload" runat="server" />
    </div>
    </form>
</body>

What I have tried:

my code is like

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;
using ACCESS = Microsoft.Office.Interop.Access;


namespace DevRural
{
    public partial class UploadDoc : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void Upload(object sender, EventArgs e)
        {
            //Upload and save the file
            string excelPath = Server.MapPath("~/Excel/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
            FileUpload1.SaveAs(excelPath);

            string conString = string.Empty;
            string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
            switch (extension)
            {
                case ".xls": //Excel 97-03
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 or higher
                    conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                    break;

            }
            conString = string.Format(conString, excelPath);
            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {
                excel_con.Open();
                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                DataTable dtExcelData = new DataTable();

                

                //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
                dtExcelData.Columns.AddRange(new DataColumn[24] { new DataColumn("Sno", typeof(int)),
                new DataColumn("BranchCode", typeof(int)),
                new DataColumn("TempRegistrationNo",typeof(int)),
                new DataColumn("RegistrationNo",typeof(string)),
                new DataColumn("EnrollmentNo",typeof(string)),
                new DataColumn("Salutation",typeof(string)),
                new DataColumn("FirstName",typeof(string)),
                new DataColumn("MiddleName",typeof(string)),
                new DataColumn("LastName",typeof(string)),
                new DataColumn("GuardianType",typeof(string)),
                new DataColumn("FatherOrSpouseFirstName",typeof(string)),
                new DataColumn("FatherOrSpouseLastName",typeof(string)),
                new DataColumn("DOB",typeof(string)),
                new DataColumn("Gender",typeof(string)),
                new DataColumn("MaritalStatus",typeof(string)),
                new DataColumn("Category",typeof(string)),
                new DataColumn("Minority",typeof(string)),
                new DataColumn("Religion",typeof(string)),
                new DataColumn("PState",typeof(string)),
                new DataColumn("PDistrict",typeof(string)),
                new DataColumn("PBlockCity",typeof(string)),
                new DataColumn("PPinCode",typeof(int)),
                new DataColumn("PVillageLocation",typeof(string)),
                new DataColumn("PAddress1",typeof(string))          
            
            });

                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                {
                    oda.Fill(dtExcelData);
                }
                excel_con.Close();
                

                //string consString = ConfigurationManager.ConnectionStrings["DDUGKY"].ConnectionString;

                OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\accessdb\rural.mdb");

                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\accessdb\rural.mdb"))
                {
                    //using (SqlBulkCopy sqlBulkCopy = new OleDbDataAdapter(con))  
                 
                    using (OleDbDataAdapter SqlBulkCo =new OleDbDataAdapter() )
                    
                    {
                        FileInfo fi = new FileInfo(FileUpload1.PostedFile.FileName);
                        string ext = fi.Extension;
                        if (ext == ".xls" || ext == ".xlsx")
                        {
                            //Set the database table name
                            //SqlBulkCo.DestinationTableName = "[dbo].[stuReg]";

                            //[OPTIONAL]: Map the Excel columns with that of the database table
                            SqlBulkCo.ColumnMappings.Add("Sno", "Sno");
                            SqlBulkCo.ColumnMappings.Add("BranchCode", "BranchCode");
                            SqlBulkCo.ColumnMappings.Add("TempRegistrationNo", "TempRegistrationNo");

                            SqlBulkCo.ColumnMappings.Add("RegistrationNo", "RegistrationNo");
                            SqlBulkCo.ColumnMappings.Add("EnrollmentNo", "EnrollmentNo");
                            sqlBulkCopy.ColumnMappings.Add("Salutation", "Salutation");

                            sqlBulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                            sqlBulkCopy.ColumnMappings.Add("MiddleName", "MiddleName");
                            sqlBulkCopy.ColumnMappings.Add("LastName", "LastName");

                            sqlBulkCopy.ColumnMappings.Add("GuardianType", "GuardianType");
                            sqlBulkCopy.ColumnMappings.Add("FatherOrSpouseFirstName", "FatherOrSpouseFirstName");
                            sqlBulkCopy.ColumnMappings.Add("FatherOrSpouseLastName", "FatherOrSpouseLastName");


                            sqlBulkCopy.ColumnMappings.Add("DOB", "DOB");
                            sqlBulkCopy.ColumnMappings.Add("Gender", "Gender");
                            sqlBulkCopy.ColumnMappings.Add("MaritalStatus", "MaritalStatus");

                            sqlBulkCopy.ColumnMappings.Add("Category", "Category");
                            sqlBulkCopy.ColumnMappings.Add("Minority", "Minority");
                            sqlBulkCopy.ColumnMappings.Add("Religion", "Religion");

                            sqlBulkCopy.ColumnMappings.Add("PState", "PState");
                            sqlBulkCopy.ColumnMappings.Add("PDistrict", "PDistrict");
                            sqlBulkCopy.ColumnMappings.Add("PBlockCity", "PBlockCity");

                            sqlBulkCopy.ColumnMappings.Add("PPinCode", "PPinCode");
                            sqlBulkCopy.ColumnMappings.Add("PVillageLocation", "PVillageLocation");
                            sqlBulkCopy.ColumnMappings.Add("PAddress1", "PAddress1");

                            con.Open();
                            sqlBulkCopy.WriteToServer(dtExcelData);
                            con.Close();
                            ScriptManager.RegisterStartupScript(Page, GetType(), "script1", "alert('Excel file successfully imported into DB');", true);
                        }
                        else
                        {

                        }

                    }
                }
            }
        }
    }
}

Posted 3-Apr-16 21:45pm

Member 11036582

Updated 18-Apr-16 22:32pm

Maciej Los

v2

Add a Solution

Comments

Thomas Nielsen - getCore 4-Apr-16 4:16am

MS Access has a brilliant import guide, so depending on how much you'd actually need to rerun this import a lot of times? ... if not use the import data guide, it rocks at that task.
Possibly you'd have to save as .csv first

Member 11036582 4-Apr-16 4:54am

dear Nielsen i want client only browse a Excel sheet and save data in my database Table .i have already save in SQL database but dont save in Access Database can u help ?

Thomas Nielsen - getCore 19-Apr-16 4:18am

Not sure i understand exactly, so they browse for an excel sheet which already exist, download it and make their changes and you then decipher what is new and update the database?
This cannot be done automatically, you'll have to write the logics yourself, which makes sense because you're the defender of your DB integrity

phil.o 4-Apr-16 4:16am

What is the problem? What is your question?

Member 11036582 4-Apr-16 4:56am

sir i want upload excel sheet and save excel sheet data in my database i am use Access database. can u help me?

Maciej Los 4-Apr-16 6:59am

And what's an issue?

Member 11036582 4-Apr-16 8:05am

sir i want a browse Excel Sheet and save my Access database what code i write in my button click event? i have write a this code but this is not working please send me complete source code with design plz..

Member 11036582 4-Apr-16 8:19am

if i am using SQL database so i am using using
(SqlBulkCopy sqlBulkCopy = new OleDbDataAdapter(con))
namespace
but if i am use Access Database so what namespace are use in bulk data copy

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sandeep Nishad · Answer 1 · 2016-04-03T23:13:00

C#

string Access = Server.MapPath("App_Data/contacts.mdb");
string Excel = Server.MapPath("App_Data/Book1.xls");
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel+";Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand())
  {
    cmd.Connection = conn;
    cmd.CommandText = "INSERT INTO [MS Access;Database=" + Access + "].[TableName_Person] SELECT * FROM [Sheet1$]";
    conn.Open();
    cmd.ExecuteNonQuery();
  }
}

for further help
C# Corner : Error Display[u7se this link]

try this one i hope it work

Thomas Nielsen - getCore · Answer 2 · 2016-04-18T22:32:00

Not very clear of what the user case is, but if you want to allow users to edit an excel sheet online, a fine implementation suggestion is here:
spreadsheet.codeplex.com

Now i'm presuming your challanges is not the simple file upload or download, you didn't at least write that so in case you want or need that i trust you can. Generally not a good idea IMO, but that's not my call.

If your problem is to interact with Excel so that you can update MSACCESS ISAAM DB's i recommend the .net interop assemblies, they're pretty good even if somewhat slow:
https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.aspx

The best and by far fastest option is to use open xml sdk, though some very old versions of excel will not be supported:
Welcome to the Open XML SDK 2.5 for Office[^]

I would like to import excel sheet data in my access database table

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0