none
The Microsoft Jet database engine cannot open the file. It is already opened exclusively by another user, or you need permission to view its data.

    Question

  • Hi,

    I'm trying to get the Excel data using C# programming. For this, i wrote the following code

    string FullPath = "C:\\Data\\Book1.xls";
    string MyPath = System.IO.Path.GetDirectoryName(FullPath);
    string myFilename = System.IO.Path.GetFileName(FullPath);
    string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MyPath + ";Extended Properties='Excel 8.0;'";
    string CmdText = "select * from " + myFilename;
    OleDbConnection Con = new OleDbConnection(ConnectionString);
    OleDbDataAdapter adptr = new OleDbDataAdapter(CmdText, Con);
    DataTable dt = new DataTable();
    adptr.Fill(dt);
    return dt;
    

    I'm using Microsoft Excel 2007 version. I've also given the full permission to the respective folder but still it's generating error. Any idea Folks ?

    Thanks


    Sohaib Khan | http://muhskhac.spaces.live.com
    Thursday, November 11, 2010 12:58 PM

Answers

  • You should have debugged the connection.. Your assembly of the data source and the select command is wrong:

    namespace TestProject4
    {
        using System.Data;
        using System.Data.OleDb;
        using Microsoft.VisualStudio.TestTools.UnitTesting;
         [TestClass]
        public class UnitTest1
        {
            [TestMethod]
            public void TestMethod1()
            {
                string FullPath = "C:\\temp\\test.xls";
                string MyPath = System.IO.Path.GetDirectoryName(FullPath);
                string myFilename = System.IO.Path.GetFileName(FullPath);
                string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                  "Data Source=" + FullPath +
                  ";Extended Properties='Excel 8.0;'";
                string CmdText = "select * from [yourSheetName$]";
                OleDbConnection Con = new OleDbConnection(ConnStr);
                OleDbDataAdapter adptr = new OleDbDataAdapter(CmdText, Con);
                DataTable dt = new DataTable();
                adptr.Fill(dt);
            }
        }
    }

    You need to use the full path including the file name for the data source value.

    In the SELECT command you need to address the sheet by its name.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Marked as answer by Sohaib Khan Friday, November 12, 2010 1:25 PM
    Friday, November 12, 2010 12:42 PM

All replies

  • hi Sohaib,

    You also need to assign the permissions to the file, not only the folder. Use the Task Manager to check whether an hidden Excel instance is running or not.

    For the correct connection string see

    http://www.connectionstrings.com/excel-2007

    or

    http://www.connectionstrings.com/excel


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, November 11, 2010 1:10 PM
  • Stefan,

    When we assign permission to the folder then it automatically assigns the same permission to all the files inside that folder. Anyway, I've checked this as well.

    Also, there is no hidden instance is running. The connection string seems fine to me. (You can also check which i defined above)

    Any other idea ?

    Thanks


    Sohaib Khan | http://muhskhac.spaces.live.com
    Thursday, November 11, 2010 1:23 PM
  • hi Sohaib,

    Any other idea ?

    Create a new text file on the desktop, change its extension to .udl. Double-click it and test whether it works.

    btw, as you are using C:\Data as folder, is this folder virtualized (UAC)?

    http://msdn.microsoft.com/en-us/library/bb756960.aspx


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, November 11, 2010 1:29 PM
  • Stefan,

    I need to work with *.xls format. My logic works with *.csv. So, it doesn't help me if i test anything other than *.xls then it will be of no use.

    Thanks


    Sohaib Khan | http://muhskhac.spaces.live.com
    Thursday, November 11, 2010 1:44 PM
  • Do it! It's a test whether your connection string is correct or not. And it eliminates your application as error source.

    If the connection can be established, then you can open the file with Notepad and examine the used connection string.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, November 11, 2010 1:52 PM
  • Create a new text file on the desktop, change its extension to .udl. Double-click it and test whether it works.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

    Done. What's next ?


    Sohaib Khan | http://muhskhac.spaces.live.com
    Thursday, November 11, 2010 2:33 PM
  • Done. What's next ?
    Sohaib Khan | http://muhskhac.spaces.live.com
    Thursday, November 11, 2010 2:33 PM
  • Have you entered the correct connection information in the Data Link Properties? Does the Test Connection runs with success or failure?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, November 11, 2010 2:40 PM
  • When i enter my connection string

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Data\\Book1.xls;Extended Properties='Excel 8.0;'

    then it returns error "Test connection failed because an error is initializing provider. [Microsoft][ODBC Driver Manager] Invalid connection string attribute"

    whereas if i create connection using ''build'' then it generates as 'Successful'


    Sohaib Khan | http://muhskhac.spaces.live.com
    Thursday, November 11, 2010 2:49 PM
  • Open the .udl file with a text editor, e.g. Notepad. Copy the connection string into your code as

    string testConnectionString = @"insertConnectionStringHere";

    Use the @ the circumvent the necessity to double the slashes in the string.

    Test your code with this string.

    Another test you can do in your code: Test whether you can find the file specified in myFilename. See

    http://msdn.microsoft.com/de-de/library/system.io.file.exists.aspx


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, November 11, 2010 3:28 PM
  • Stefan,

    I created following odbc connection using udl file.

    Provider=MSDASQL.1;Password=155Pa$$w0rd;Persist Security Info=True;User ID=spsadmin1;Extended Properties='DSN=Excel Files;DBQ=C:\SharePoint Data\Book1.xls;DefaultDir=C:\SharePoint Data;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;';Initial Catalog=C:\SharePoint Data\Book1.xls";// "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MyPath + ";Extended Properties='Excel 8.0;'

    It works fine when i test the connection in the udl file but generates error in the code

    "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

    Whereas, If i try to create Microsoft.Jet provider connection using udl then it simply gives error during testing in the udl

    "Test connection failed because an error is initializing provider"

     


    Sohaib Khan | http://muhskhac.spaces.live.com
    Friday, November 12, 2010 7:39 AM
  • Have you read my other post about using @"connectionString"?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Friday, November 12, 2010 10:03 AM
  • Another test you can do in your code: Test whether you can find the file specified in myFilename. See

    http://msdn.microsoft.com/de-de/library/system.io.file.exists.aspx

    >>> The above method returns "File Exists"


    Sohaib Khan | http://muhskhac.spaces.live.com
    Friday, November 12, 2010 12:04 PM
  • You should have debugged the connection.. Your assembly of the data source and the select command is wrong:

    namespace TestProject4
    {
        using System.Data;
        using System.Data.OleDb;
        using Microsoft.VisualStudio.TestTools.UnitTesting;
         [TestClass]
        public class UnitTest1
        {
            [TestMethod]
            public void TestMethod1()
            {
                string FullPath = "C:\\temp\\test.xls";
                string MyPath = System.IO.Path.GetDirectoryName(FullPath);
                string myFilename = System.IO.Path.GetFileName(FullPath);
                string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                  "Data Source=" + FullPath +
                  ";Extended Properties='Excel 8.0;'";
                string CmdText = "select * from [yourSheetName$]";
                OleDbConnection Con = new OleDbConnection(ConnStr);
                OleDbDataAdapter adptr = new OleDbDataAdapter(CmdText, Con);
                DataTable dt = new DataTable();
                adptr.Fill(dt);
            }
        }
    }

    You need to use the full path including the file name for the data source value.

    In the SELECT command you need to address the sheet by its name.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Marked as answer by Sohaib Khan Friday, November 12, 2010 1:25 PM
    Friday, November 12, 2010 12:42 PM