Get data from Excel file using ADO.NET

lam_lvl · January 17th, 2005, 03:39 AM

THIS IS MY CODE

Private Sub GetDataFromXLS()
        Dim strConn As String
        Dim objConn As OleDbConnection
        Dim sql As String

        strConn = _
              "Provider=Microsoft.Jet.OLEDB.4.0; " & _
              "Data Source=C:\Data.xls;" & _
              "Extended Properties=Excel 8.0;HDR=Yes;"

        objConn = New OleDbConnection(strConn)
        objConn.Open()

        sql = "SELECT ProductCode, ProductName FROM [Data$]"
        '-Data is name of sheet in Data.xls.

        Dim objCommand As New OleDbCommand(sql, objConn)

        Dim objAdapter As New OleDbDataAdapter(objCommand)

        Dim objDS As New DataSet

        objAdapter.Fill(objDS)

        With Me.gridProduct
            .DataSource = objDS.Tables(0).DefaultView
            .DataBind()
        End With

        objConn.Close()
    End Sub

I use Excel 2003.

I don't understand where's wrong in above, but it showed error following:

Line 50: strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.xls;Extended Properties=Excel 8.0;HDR=Yes;"
Line 51: objConn = New OleDbConnection(strConn)
Line 52: objConn.Open()
Line 53:
Line 54: sql = "SELECT ProductCode, ProductName FROM [Data$]"

[OleDbException (0x80004005): Unspecified error]
   System.Data.OleDb.OleDbConnectionInternal.Initiali zeProvider() +135
   System.Data.OleDb.OleDbConnectionInternal..ctor(Ol eDbConnection conn, OleDbConnectionString constr) +84
   System.Data.OleDb.OleDbConnectionFactory.CreateCon nection(DbConnectionOptions options, Object providerInfo, DbConnectionBase owningObject) +55
   System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnectionBase owningConnection, DbConnectionPoolGroup poolGroup) +26
   System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnectionBase owningConnection) +51
   System.Data.ProviderBase.DbConnectionClosed.Open(D bConnectionBase outerConnection) +189
   System.Data.ProviderBase.DbConnectionBase.Open() +62
   GUIVP.ImportData.btnView_Click(Object sender, EventArgs e) in E:\Inetpub\wwwroot\GUIVP\Admin\ImportData.aspx.vb: 52
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEven t(String eventArgument) +232
   System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +5
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +31
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5157

Please help me in this situation.
Thank you alot.

L.V.L

sanjaybhr · February 8th, 2005, 09:14 PM

Quote:

quote:Originally posted by lam_lvl
THIS IS MY CODE

Private Sub GetDataFromXLS()
        Dim strConn As String
        Dim objConn As OleDbConnection
        Dim sql As String

        strConn = _
             "Provider=Microsoft.Jet.OLEDB.4.0; " & _
             "Data Source=C:\Data.xls;" & _
             "Extended Properties=Excel 8.0;HDR=Yes;"

        objConn = New OleDbConnection(strConn)
        objConn.Open()

        sql = "SELECT ProductCode, ProductName FROM [Data$]"
        '-Data is name of sheet in Data.xls.

        Dim objCommand As New OleDbCommand(sql, objConn)

        Dim objAdapter As New OleDbDataAdapter(objCommand)

        Dim objDS As New DataSet

        objAdapter.Fill(objDS)

        With Me.gridProduct
            .DataSource = objDS.Tables(0).DefaultView
            .DataBind()
        End With

        objConn.Close()
    End Sub

I use Excel 2003.

I don't understand where's wrong in above, but it showed error following:

Line 50: strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.xls;Extended Properties=Excel 8.0;HDR=Yes;"
Line 51: objConn = New OleDbConnection(strConn)
Line 52: objConn.Open()
Line 53:
Line 54: sql = "SELECT ProductCode, ProductName FROM [Data$]"

[OleDbException (0x80004005): Unspecified error]
<B style="color:black;background-color:#ffff66">System.Data.OleDb.OleDbConnectionIn ternal.InitializeProvider</B>() +<B style="color:black;background-color:#A0FFFF">135</B>
System.Data.OleDb.OleDbConnectionInternal..c tor(OleDbConnection conn, OleDbConnectionString constr) +84
System.Data.OleDb.OleDbConnectionFactory.Cre ateConnection(DbConnectionOptions options, Object providerInfo, DbConnectionBase owningObject) +55
System.Data.ProviderBase.DbConnectionFactory .CreateNonPooledConnection(DbConnectionBase owningConnection, DbConnectionPoolGroup poolGroup) +26
System.Data.ProviderBase.DbConnectionFactory .GetConnection(DbConnectionBase owningConnection) +51
System.Data.ProviderBase.DbConnectionClosed. Open(DbConnectionBase outerConnection) +189
System.Data.ProviderBase.DbConnectionBase.Op en() +62
GUIVP.ImportData.btnView_Click(Object sender, EventArgs e) in E:\Inetpub\wwwroot\GUIVP\Admin\ImportData.aspx.vb: 52
System.Web.UI.WebControls.Button.OnClick(Eve ntArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBa ckEvent(String eventArgument) +232
System.Web.UI.WebControls.Button.System.Web. UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +5
System.Web.UI.Page.RaisePostBackEvent(IPostB ackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameVa lueCollection postData) +31
System.Web.UI.Page.ProcessRequestMain(Boolea n includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5157

Please help me in this situation.
Thank you alot.

L.V.L

sanjaybhr · February 8th, 2005, 09:16 PM

Hi I am also getting the same problem. If you/any one got the solution please let me know.
Thanks,
Sanjay..

gcere · February 9th, 2005, 05:04 PM

Hi,

Your connection string is not correct.

Try this one..

strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Classeur1.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""

Bye.

Geoffrey CÃ©rÃ©
Epsilon Technologies

sanjaybhr · February 10th, 2005, 07:14 PM

Hi
I am trying to extract data from Excel file & insert into SQL data table. My program works when I run it through VS.NET but fails when I use IIS server. Please help me..

Exception Details: System.Data.OleDb.OleDbException: Unspecified error

Source Error:

Line 34: myCommand.Fill(myDataSet, "ExcelInfo");
Line 35: DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
Line 36: DataGrid1.DataBind();

Source File: c:\Telemarketing\UploadExcel.aspx Line: 34

Stack Trace:

[OleDbException (0x80004005): Unspecified error]
   System.Data.OleDb.OleDbConnectionInternal.Initiali zeProvider() +135
   System.Data.OleDb.OleDbConnectionInternal..ctor(Ol eDbConnection conn, OleDbConnectionString constr) +84
   System.Data.OleDb.OleDbConnectionFactory.CreateCon nection(DbConnectionOptions options, Object providerInfo, DbConnectionBase owningObject) +55
   System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnectionBase owningConnection, DbConnectionPoolGroup poolGroup) +26
   System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnectionBase owningConnection) +51
   System.Data.ProviderBase.DbConnectionClosed.Open(D bConnectionBase outerConnection) +189
   System.Data.ProviderBase.DbConnectionBase.Open() +62
   System.Data.Common.DbDataAdapter.FillInternal(Data Set dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +86
   ASP.UploadExcel_aspx.uploadme(String ss) in c:\Telemarketing\UploadExcel.aspx:34
   ASP.UploadExcel_aspx.Button1_Click(Object Source, EventArgs e) in c:\Telemarketing\UploadExcel.aspx:120
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEven t(String eventArgument) +232
   System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +5
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +31
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint

My Original CODE:

    string strConn;
    // ss is the excel file name with the path
    string filename=ss ;
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + filename + ";Extended Properties=Excel 8.0;HDR=Yes;";
    //You must use the $ after the object you reference in the spreadsheet
    OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM
                                 [list$]", strConn);
    DataSet myDataSet = new DataSet();
    myCommand.Fill(myDataSet, "ExcelInfo");
    DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
    DataGrid1.DataBind();

sydneyausguy · February 3rd, 2006, 07:58 PM

I used gcere connection string and still get the error anyone got a fix yet?

yzer · March 18th, 2008, 12:32 PM

This is the solution:
http://www.themssforum.com/Ado/Syste...n-Unspecified/

PerttiOO · February 27th, 2014, 05:19 AM

Quote:

Originally Posted by yzer

This is the solution:
http://www.themssforum.com/Ado/Syste...n-Unspecified/

First unfortunately this link does not work anymore.

Second I struggled with this issue quite some time and I wanted to share some info about it.
As you mentioned this issue occurs on IIS server and there are couple of things that can be the problem.
For example:
- Do you have a read permissions over the folder that the file is in?
- If you have an ASP.NET app than check your web.config file, does it define impersonate to true?
I read somewhere that because when .NET reads an excel file, it creates a temp folder to store the data, and that temp folder can only be access by the ASP.NET account. So if you impersonate as another user on the computer / network and tried to use ASP.NET to read the data in the excel file, you will not be able to. Read more here under the Set Impersonation section: http://support.microsoft.com/kb/827190.

Also you can try using an alternative approach, I would recommend you this VB.NET excel component. Itâs fast and furious ;)
Here is how you can export Excel data to a DataTable in VB.NET:

Code:

' Load Excel file.
Dim workbook = ExcelFile.Load("Workbook.xls")

' Select active worksheet from the file.
Dim worksheet = workbook.Worksheets.ActiveWorksheet

' Extract the data from the worksheet to newly created DataTable starting at 
' first row and first column for 10 rows or until the first empty row appears.
Dim dataTable = worksheet.CreateDataTable(New CreateDataTableOptions() With
    {
        .StartRow = 0,
        .StartColumn = 0,
        .NumberOfRows = 10,
        .ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
    })