Create DataSet from Excel Spreadsheet

Posted on 2006-04-27
Last Modified: 2006-11-18
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);
Question by:BoggyBayouBoy
    LVL 42

    Accepted Solution

    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)
    LVL 1

    Author Comment

    Using a fully qualified path and forward slashes in the connection string did the trick... Thanks again frodoman.

    eg c:/data/mydata1.xls
    LVL 42

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
    IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now