[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

Create DataSet from Excel Spreadsheet

Help needed!  Here's the code I tried to programmatically create a dataset from a excel spreadsheet.  All data is located in Sheet1 of the workbook.

The error i get says "Microsoft Jet database engine cold not fnd the object 'Sheet1'.  "Make sure the object exists and that you spell its name and path name correctly"

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;

public partial class test : System.Web.UI.Page
{

    protected void Button1_Click(object sender, EventArgs e)
    {
    DataSet DS = new System.Data.DataSet();
    OleDbConnection MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=MyData.XLS;Extended Properties=Excel 8.0;");
    OleDbDataAdapter MyCommand = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
    MyCommand.Fill(DS);
    MyConnection.Close();
    }
}
0
BoggyBayouBoy
Asked:
BoggyBayouBoy
  • 2
1 Solution
 
frodomanCommented:
Make sure you're hitting MyData.xls - you might try using the fully qualified path to the Excel file (e.g.: c:\inetpub\mydata.xls)
0
 
BoggyBayouBoyAuthor Commented:
Using a fully qualified path and forward slashes in the connection string did the trick... Thanks again frodoman.

eg c:/data/mydata1.xls
0
 
frodomanCommented:
Glad to help.

For future reference you might want to use:  Server.MapPath & "/mydata1.xls"  -- that way it'll work if you install the app in a different folder.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now