none
How to read restricted excel file using OLEDB deriver? RRS feed

  • Question

  • When I am trying to read an excel with restricted access getting an error says "External table is not in the expected format".But without restriction or physically opened file able to read using OLEDB driver.Able to read the restricted file using interop, but performance is extremely bad.Requesting your help on it.

    Thanks in advance.

    Jino

    Tuesday, March 26, 2019 12:24 PM

Answers

  • Hi

    Thank you for posting here.

    According to your description, you want to read restricted excel file using OLEDB.

    Based on my research, I could not find a way to read restricted excel file using OLEDB.

    However, I find using Microsoft.Office.Interop.Excel could solve your problem.

    You could refer to the following code.

            
       string path = @"D:\t1.xlsx";
    
                var file = new excel.Application();
    
                excel.Workbook workbook = file.Workbooks.Open(path, ReadOnly: true, Password: "test123");
    
                excel.Worksheet worksheet = (excel.Worksheet)workbook.Sheets[1];
    
                Console.WriteLine(worksheet.Cells[1,1].value);

    Result:

    Best regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, March 27, 2019 6:26 AM
    Moderator

All replies

  • Hello,

    We need to see your connection string

    In the regard see this post.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 26, 2019 12:43 PM
    Moderator
  • This is the connection string. 

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=substitution;Extended Properties=Excel 12.0;Excel 12.0; HDR = YES;IMEX=1;pooling = false;

    Tuesday, March 26, 2019 12:51 PM
  • Is the Excel file a .xls or .xlsx ?

    Also what do you mean by restrict access ?


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Tuesday, March 26, 2019 1:12 PM
    Moderator
  • Have you tried

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=substitution;Extended Properties=Excel 12.0;Excel 12.0; HDR = YES;IMEX=1;pooling = false;READONLY=TRUE


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 26, 2019 1:16 PM
    Moderator
  • Tried above connection string also.Its not working.

    And the excel file restricted access.

    • Edited by JinoTK Tuesday, March 26, 2019 2:03 PM
    Tuesday, March 26, 2019 1:30 PM
  • Tried above connection string also.Its not working.

    And the excel file restricted access.

    Please explain exactly what you mean by restricted access. Right now it appears OleDb does not support whatever "restricted access" means. 

    OleDb has limited capabilities in regards to working with Excel.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, March 26, 2019 2:52 PM
    Moderator
  • System.Data.DataTable dt1 = new System.Data.DataTable();
                    //try
                    //{
                    string path = @"D:\TFSProjects\restricted file\restrictedfile.xlsb";
                    string str = "Provider =Microsoft.ACE.OLEDB.12.0;Data Source=substitution;Extended Properties=Excel 12.0 Macro;Excel 12.0; HDR = YES;IMEX=1;pooling = false;READONLY=TRUE;";
                    string strConn = str.Replace("substitution", path);
                    string strSheetName = "Tower";
                    using (OleDbConnection conn = new OleDbConnection(strConn))
                    {
                        using (OleDbDataAdapter dtad = new OleDbDataAdapter("select * from [" + strSheetName + "$]", conn))
                        {
                            dtad.Fill(dt1);

                        }
                    }


    Wednesday, March 27, 2019 5:09 AM
  • Also restricted excel means the file access permissions is enabled in user level.When we are opening an excel we will get an option for protect the file.

    File->Info->Protect Workbook

    Wednesday, March 27, 2019 5:13 AM
  • Hi

    Thank you for posting here.

    According to your description, you want to read restricted excel file using OLEDB.

    Based on my research, I could not find a way to read restricted excel file using OLEDB.

    However, I find using Microsoft.Office.Interop.Excel could solve your problem.

    You could refer to the following code.

            
       string path = @"D:\t1.xlsx";
    
                var file = new excel.Application();
    
                excel.Workbook workbook = file.Workbooks.Open(path, ReadOnly: true, Password: "test123");
    
                excel.Worksheet worksheet = (excel.Worksheet)workbook.Sheets[1];
    
                Console.WriteLine(worksheet.Cells[1,1].value);

    Result:

    Best regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, March 27, 2019 6:26 AM
    Moderator
  • Thank you Jack.

    I already tried this solution, but the thing is for large excel files reading time is extremely bad.For a single sheet reading this method taking around 15 minutes, but the same unprotected sheet i am able to read using OLEDB within 5 second.This is the issue.

    Thanks in advance,

    Jino T K.

    Wednesday, March 27, 2019 7:06 AM