Transfer data from excel to SQL

Question

I have created a demo for uploading an Excel file to webserver and then copying data from it to SQL server db. It is working perfectly.

ASPX Design:

    <table>
           <tr>
              <td>
                   <span style="color: Red">*</span>Attach Excel file
              </td>
              <td>
                   <asp:FileUpload ID="fileuploadExcel" runat="server" />
              </td>
           </tr>
           <tr>
              <td></td>
              <td>
                  <asp:Button ID="btnSend" runat="server" Text="Export" onclick="btnSend_Click"  />
              </td>
</tr>
</table>

Code behind:

 private String strConnection = "Data Source=Test-PC;Initial Catalog=ExcelMapping;User ID=Test;Password=Test";
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btnSend_Click(object sender, EventArgs e)
        {
            //file upload path
            string path = fileuploadExcel.PostedFile.FileName;
            fileuploadExcel.SaveAs(Server.MapPath(path));
            //Create connection string to Excel work book
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(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 [lat],[long] from [Sheet1$]", excelConnection);
            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();
            DataSet ds = new DataSet();

            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            //Give your Destination table name
            sqlBulk.DestinationTableName = "Test";
            sqlBulk.ColumnMappings.Add(0, 1);
            sqlBulk.ColumnMappings.Add(1, 2);
            sqlBulk.WriteToServer(dReader);
            excelConnection.Close();
        }
    }

But when I use the same code in an existing application. It gives me error while opening the connection with Excel stating

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

Please suggest me what to do to resolve this.

You say, you have a sample solution working on the same machine? — nunespascal
– nunespascal, Commented Jul 9, 2013 at 7:12
have you tried using the ODBC Data Source Administrator to add that provider? — rajeemcariazo
– rajeemcariazo, Commented Jul 9, 2013 at 7:12
@rajeem_cariazo no i haven't but same code is working in demo. — Deep Sharma
– Deep Sharma, Commented Jul 9, 2013 at 7:17
Are you by any chance running on a 64-bit machine? The Excel OleDB stuff is known to have issues on 64-bit platforms. — marc_s
– marc_s, Commented Jul 9, 2013 at 7:18

nunespascal · Accepted Answer · 2013-07-09 07:17:58Z

1

You need to have Office System Driver: Data Connectivity Components installed.

This is a 32 bit component. So you need to set your platform to x86.

answered Jul 9, 2013 at 7:17

nunespascal

17.7k2 gold badges45 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Deep Sharma Over a year ago

I cannot switch it to 32-bit.. i m running this app on win 7 64 bit. if i build it for 32 - bit other components in app stops working.

Steve · Accepted Answer · 2013-07-09 07:29:13Z

1

This message occurs when the bitness of your application is not the same as the installed ACE.OLEDB provider and usually occurs on a 64 bit system

For example, you compile for AnyCPU platform and then run your app on a 64bit system. In this case, your application runs as 64bit code, but 64bit code cannot use 32bit drivers. If, on your target system there is a 32bit ACE.OLEDB, you get the message. Of course this is true also in reverse. Your app is x86 compiled but the ACE.OleDb is 64bit. (No you can't install both the 32bit and the 64bit version of the provider)

Of course I am assuming that the drivers are already installed, if not, you can download them from the Microsoft Access Database Engine page, but keep in mind that they should have the same bitness as your installed Microsoft Office. And here comes the big problem. If you have installed the 32bit version of Office you need the 32bit version of the Database Engine. This means that you need to have an app compiled for x86 or you need to install the 64bit version of Microsoft Office.

edited Jul 9, 2013 at 7:29

answered Jul 9, 2013 at 7:17

Steve

217k22 gold badges242 silver badges296 bronze badges

4 Comments

Deep Sharma Over a year ago

my app is 64 bit compiled. I am running it on win 7 64 bit. and build is set to "Any CPU"

Steve Over a year ago

Do you have the 32bit version of Office installed?

Steve Over a year ago

Sorry but you can't install the 64bit drivers with that version of Office, you need to install the 64bit Office

Steve Over a year ago

There is an inconsistency between your code and the message. You use Microsoft.ACE.OleDb.12.0 but your error message talks about Microsoft Jet.OleDb.12.0. (That really doesn't exists) Could you check if this is only a typo or you effectively have this wrong?

marc_s · Accepted Answer · 2013-07-09 07:34:34Z

1

I had faced this problem once. I followed these steps:

Just follow the steps:

Right click on the Solution File in Solution Explorer
Click on the Configuration Manager.
Click on the Active Platform Drop down, if x86 is already there then select that, else click on New.
Select x86 from the new platform dropdown
Compile and run your application.

You can follow THIS link for step by step pictorial representation.

edited Jul 9, 2013 at 7:34

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 9, 2013 at 7:16

C Sharper

8,66430 gold badges96 silver badges158 bronze badges

Comments

StackTrace · Accepted Answer · 2013-07-09 07:17:31Z

0

Error comes becase you dont have Office 2007 installed on machine.

So, you need to install 2007 Office System Driver: Data Connectivity Components from: http://www.microsoft.com/download/en/details.aspx?id=23734

answered Jul 9, 2013 at 7:17

StackTrace

9,44636 gold badges122 silver badges211 bronze badges

1 Comment

Deep Sharma Over a year ago

I have office 2010 installed

sangram parmar · Accepted Answer · 2013-07-09 08:35:14Z

try this

without OLEDB ..Very simple code

refrence: exceldatareader.codeplex.com/

download dll and add refrence

FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

//5. Data Reader methods
while (excelReader.Read())
{
    //excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

Collectives™ on Stack Overflow

Transfer data from excel to SQL

5 Answers 5

1 Comment

4 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

4 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related