How to import Excel sheet to Sql server 2008 r2 database table using ASP.net c#

Question

0.00/5 (No votes)

See more:

, +

I'm working on importing excel sheet data to my database through asp.net c# web application. My database structure is already defined. Now I need to code for the same.

My excel sheet format is like that

<table border="1"> 
<tr>
<td>Name</td>
<td>Code</td>
<td>Basic</td>
<td>Alwnce</td>
<td>Conv</td>
</tr>
<tr>
<td>ABC</td>
<td>1126</td>
<td>7600</td>
<td>1140</td>
<td>800</td>
</tr>
<tr>
<td>XYZ</td>
<td>2003</td>
<td>9225</td>
<td>923</td>
<td>800</td>
</tr>
</table>

And my database structure like this

<table border="1"> 
<tr>
<td>Name</td>
<td>Code</td>
<td>Amount</td>
<td>CompCode</td>
</tr>
<tr>
<td>ABC</td>
<td>1126</td>
<td> 7600</td>
<td> 10</td>
</tr>
<tr>
<td>ABC</td>
<td>1126</td>
<td> 1140</td>
<td> 26</td>
</tr>
<tr>
<td>ABC</td>
<td>1126</td>
<td> 800</td>
<td> 30</td>
</tr>
<tr>
<td>XYZ</td>
<td>2003</td>
<td> 9225</td>
<td> 10</td>
</tr>
<tr>
<td>XYZ</td>
<td>2003</td>
<td> 923</td>
<td> 26</td>
</tr>
<tr>
<td>XYZ</td>
<td>2003</td>
<td> 800</td>
<td> 30</td>
</tr>


</table>

(10 for Basic,26 for Alwnce,30 for Conv. These are repeated no. for every employee)

Kindly share your idea.

Posted 9-Apr-15 19:20pm

GT1335

Updated 9-Apr-15 19:39pm

RajeeshMenoth

v4

Add a Solution

Comments

King Fisher 10-Apr-15 1:32am

Sorry i tried to format it :(

GT1335 10-Apr-15 1:33am

Its ok. I just need answer.

4 solutions

Solution 1

You need either OleDb Connection String [^] Or

Excel Data reader[^] to read the Excel and get the data in Data Set.

Then you can use your custom method to build query, or use bulk insert to insert the records to database.

http://stackoverflow.com/questions/6093007/copying-data-from-datatable-to-sql-server[^]

How to save data from a DataSet into a T-SQL table[^]

Posted 9-Apr-15 19:37pm

Sudhansu Sekhar Prusty

Comments

GT1335 10-Apr-15 1:50am

I used Oledb connection string. I know how to import simple as it is format in database but I dont have any idea about this type of structure.

Solution 2

1. Save ur Excel file into .csv Format.
Then try to fiollow this method. I hope it ll help you

Posted 9-Apr-15 19:44pm

Krishna Rpr

Comments

GT1335 10-Apr-15 2:23am

Can i customize this .csv format to my sql database table format?

Solution 3

Hi,

The given link will provide Import Excel Sheet data and records into SQL Server 2005, 2008, 2012 using ASP.Net

Read this link : How to Import Excel Sheet data into SQL Server using ASP.Net

Posted 9-Apr-15 20:23pm

RajeeshMenoth

Comments

GT1335 10-Apr-15 2:30am

Here I've different column name and structure from excel to sql database table.

[no name] 10-Apr-15 2:59am

This link will help you click here

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)

GT1335 · Accepted Answer · 2015-04-12T23:59:00

Finally I made it.
Hear is a solution.

protected void btnUpload_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            if (fUpload.FileName != null)
            {
                string fileLocation = Server.MapPath("~/Attachment/") + fUpload.FileName;
                if (System.IO.File.Exists(fileLocation))
                {
                    System.IO.File.Delete(fileLocation);
                }
                fUpload.SaveAs(fileLocation);

                string excelConnectionString = string.Empty;
                excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                                    + fileLocation + ";Extended Properties='Excel 12.0 Xml;HDR=YES'";

                string fileExtension = System.IO.Path.GetExtension(fUpload.FileName);

                if (fileExtension == ".xls")
                {
                    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.4.0;Data Source="
                                    + fileLocation + ";Extended Properties='Excel 8.0 Xml;HDR=YES'";

                }
                else if (fileExtension == ".xlsx")
                {
                    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                                    + fileLocation + ";Extended Properties='Excel 12.0 Xml;HDR=YES'";

                }

                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                excelConnection.Open();
                DataTable dt = new DataTable();

                dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                String[] excelSheets = new String[dt.Rows.Count];
                int t = 0;

                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[t] = row["TABLE_NAME"].ToString();
                    t++;
                }
                OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);


                string query = string.Format("Select * from [{0}]", excelSheets[0]);
                using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                {
                    dataAdapter.Fill(ds);
                }

                excelConnection.Close();
                excelConnection1.Close();

                StartUploadEmails(ds);
            }
            else
            {
                lblErrro.Text = "Please select file to upload.";
            }
        }

public void StartUploadEmails(DataSet ds)
        {
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string EmpName = ds.Tables[0].Rows[i]["EmpName"].ToString();
                string EmpCode = ds.Tables[0].Rows[i]["EmpCode"].ToString();
                string Basic = ds.Tables[0].Rows[i]["EmpCode"].ToString();
                string Allowance = ds.Tables[0].Rows[i]["Allowance"].ToString();
                string Cony = ds.Tables[0].Rows[i]["Cony"].ToString();
                string query = "";
                for (int j = 0; j < 3; j++)
                {
                    switch (j)
                    {
                        case 0:
                            query = "insert into GeetEmployee (EmpName, EmpCode, Amount, CompanyCode) values ('" + EmpName + "','" + EmpCode + "', '" + Convert.ToInt32(Basic) + "', '" + (long)TypeCode.Basic + "')";
                            ExecuteData(query);
                            break;
                        case 1:
                            query = "insert into GeetEmployee (EmpName, EmpCode, Amount, CompanyCode) values ('" + EmpName + "','" + EmpCode + "', '" + Convert.ToInt32(Allowance) + "', '" + (long)TypeCode.Allowance + "')";
                            ExecuteData(query);
                            break;
                        case 2:
                            query = "insert into GeetEmployee (EmpName, EmpCode, Amount, CompanyCode) values ('" + EmpName + "','" + EmpCode + "', '" + Convert.ToInt32(Cony) + "', '" + (long)TypeCode.Cony + "')";
                            ExecuteData(query);
                            break;
                        default:
                            break;
                    }

                }

            }
        }

private void ExecuteData(string query)
        {
            try
            {
                cn.Open();
                cmd = new SqlCommand(query, cn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                cn.Close();
            }
        }

public enum TypeCode
    {
        Basic = 10,
        Allowance = 20,
        Cony = 30
    }

How to import Excel sheet to Sql server 2008 r2 database table using ASP.net c#

4 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us