How to import data from EXCEL to SQL database in ASP.NET

Question

0.00/5 (No votes)

See more:

C#

The Microsoft Office Access database engine could not find the object 'Sheet1$'.  Make sure the object exists and that you spell its name and the path name correctly.

What I have tried:

protected void Button1_Click(object sender, EventArgs e)
{
String strConnection = @"Data Source=PARAMERP4-PC\SPEEDPEQ;Initial Catalog=XYZ;Integrated Security=True";
//file upload path
string path = FileUpload12.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [CLIENT],[TASK],[DESCRIPTION],[DATE],[TIME TAKEN],[CALL ATTENDED BY],[ISSUE RESOLVED BY],[Help By] from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Export_Excell_Sheet";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}

Excell collumn name :----CLIENT TASK, DESCRIPTION, DATE, TIME TAKEN, CALL, ATTENDED BY, ISSUE RESOLVED BY, Help By

Data Base Column name :--tbale name -Export_Excell_Sheet

[Export_Excell_Sheet]
(
[CLIENT] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TASK] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATE] [datetime] NULL,
[TIME_TAKEN] [datetime] NULL,
[CALL_ATTENDED_BY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISSUE_RESOLVED_BY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Help_By] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

Posted 15-Apr-16 2:27am

ashishpne

Updated 15-Apr-16 21:29pm

Add a Solution

Comments

ZurdoDev 15-Apr-16 8:49am

Does your workbook have a sheet named "Sheet1?"

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)

Mahesh_Bhosale · Answer 1 · 2016-04-15T02:49:00

Solution 1

Use following Ref :
Using SqlBulkCopy to import Excel SpreadSheet data into SQL Server in ASP.Net using C# and VB.Net[^]
and
Could not read data from excel in c# - Stack Overflow[^]

Posted 15-Apr-16 2:49am

Mahesh_Bhosale

Jiyaji_ · Answer 2 · 2016-04-15T21:29:00

C#

private void ImporttoSQL(string sPath)
      {
          string sSourceConstr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath);
          string sDestConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
          OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
          using (sSourceConnection)
          {
              string sql = string.Format("Select * FROM [{0}]", "Sheet1$");
              OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
              sSourceConnection.Open();
              using (OleDbDataReader dr = command.ExecuteReader())
              {
                  using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
                  {
                      bulkCopy.DestinationTableName = "Alh_portal";
                      bulkCopy.WriteToServer(dr);
                  }
              }
          }
      }