none
ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object. RRS feed

  • Question

  • What is wrong with this part of code? I have only one sheet in the workbook, in this case, it is xlWorksheet

    Thanks.

                var filePath = string.Empty;
                OpenFileDialog OpenFile = new OpenFileDialog();
                
                OpenFile.Filter = "Excel Files|*.xl*"; //Filter for excel file
                OpenFile.Title = "Select your test case file";
                OpenFile.FilterIndex = 2;  //Don't know what it mean
                OpenFile.RestoreDirectory = true;
    
                if (OpenFile.ShowDialog() == DialogResult.OK)
                {
                    //Get the path of specified file
                    filePath = OpenFile.FileName;
                }
    
                //Read excel data into DataTable
                //Create COM Objects. Create a COM object for everything that is referenced
    
                Excel.Application xlApp = new Excel.Application();
                //filePath should already be open in the above, correct? It seems Open is not correct here?
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
                //The excel file has only one sheet, with header, want to import Column A-G into database
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    
    
                SqlBulkCopy oSqlBulk = null;
    
                // SET A CONNECTION WITH THE EXCEL FILE.
                OdbcConnection myExcelConn = new OdbcConnection("Driver={Microsoft Excel Driver (*.xls)}; DBQ=" + filePath +"; DriverID=790");
    
    
                try
                {
                    myExcelConn.Open();
    
                    // GET DATA FROM EXCEL SHEET.
                    OdbcCommand ObjCmd =
                        new OdbcCommand("SELECT * FROM " + xlWorksheet, myExcelConn);
    
                    // READ THE DATA EXTRACTED FROM THE EXCEL FILE.
                    OdbcDataReader objBulkReader = null;
                    objBulkReader = ObjCmd.ExecuteReader();
    
    
    
                    xlWorkbook.Close(false);
                    xlApp.Quit();


    • Edited by VA_er Thursday, March 28, 2019 3:37 AM
    Thursday, March 28, 2019 3:27 AM

Answers

  • Hi

    I have reproduced your problem.

    You could try the following change.

    Change

     OdbcCommand ObjCmd =new OdbcCommand("SELECT * FROM " + xlWorksheet.Name, myExcelConn);

    Into

     OdbcCommand ObjCmd = new OdbcCommand("SELECT * FROM [" + xlWorksheet.Name+ "$]", myExcelConn);

    I have tested it, it worked.

    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.

    • Marked as answer by VA_er Friday, March 29, 2019 11:31 AM
    Friday, March 29, 2019 5:26 AM
    Moderator

All replies

  • If the first part works, then use the xlWorksheet variable to access the data. For example, to read from some cell:

       var value = xlWorksheet.Cells[2, 3];

       string text = value.ToString();

     

    Maybe the second part cannot work because the file is locked by first part.

    Also, try this:

      new OdbcCommand("SELECT * FROM [" + xlWorksheet.Name + "$]", myExcelConn);

    • Edited by Viorel_MVP Thursday, March 28, 2019 6:16 AM
    Thursday, March 28, 2019 6:12 AM
  • Hi 

    Thank you for posting here.

    For your question, you want to solve the exception that in the picture.

    Change

    OdbcCommand ObjCmd =new OdbcCommand("SELECT * FROM " + xlWorksheet, myExcelConn);


    Into

      OdbcCommand ObjCmd =new OdbcCommand("SELECT * FROM " + xlWorksheet.Name, myExcelConn);


    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.


    Thursday, March 28, 2019 8:42 AM
    Moderator
  • Hi 

    Thank you for posting here.

    For your question, you want to solve the exception that in the picture.

    Change

    OdbcCommand ObjCmd =new OdbcCommand("SELECT * FROM " + xlWorksheet, myExcelConn);


    Into

      OdbcCommand ObjCmd =new OdbcCommand("SELECT * FROM " + xlWorksheet.Name, myExcelConn);


    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.


    Thank you very much.

    It is very painful for newbie to write it correctly, now I got different error.

    If I want to get the excel data into objBulkReader , how should I write the query? It seems I still got the Odbc part wrong (see below screenshot). I do write sheet name after From, correct? The sheet has title row. It should read data from table, not sheetname, but how can code know excel table name, I did not assign any table name in excel file.

    When the sheetname has empty space, it does not pick up whole sheetname.

    Then I changed sheetname by removing empty space, it still gives me the error.

    How to write that line of code correctly?

    Thanks.


    • Edited by VA_er Friday, March 29, 2019 2:05 AM
    Friday, March 29, 2019 2:03 AM
  • Hi

    Thanks for the feedback.

    It seems that your current problem has been solved. If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find the solution quickly if they face a similar issue. To your new question, you could create a new thread in the C# forum due to one question for one thread.

    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.

    Friday, March 29, 2019 3:03 AM
    Moderator
  • Not really, xlWorksheet.Name gets rid of previous error, but it pops up new error. SampleSheetname refers to xlWorksheet.Name 
    Friday, March 29, 2019 3:12 AM
  • Hi

    I have reproduced your problem.

    You could try the following change.

    Change

     OdbcCommand ObjCmd =new OdbcCommand("SELECT * FROM " + xlWorksheet.Name, myExcelConn);

    Into

     OdbcCommand ObjCmd = new OdbcCommand("SELECT * FROM [" + xlWorksheet.Name+ "$]", myExcelConn);

    I have tested it, it worked.

    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.

    • Marked as answer by VA_er Friday, March 29, 2019 11:31 AM
    Friday, March 29, 2019 5:26 AM
    Moderator
  • Thanks much!
    Friday, March 29, 2019 11:31 AM