none
excel odbc insert problem

    Question

  • I am building a program in C# that combines several different excel files into one excel file with several sheets.  The excel driver does not support batch queries, so I must insert into the new sheet line by line.  The problem that I am running into is that I have one sheet that fails to allow the insert after a number of them have already been inserted.

        my error is-
    System.Data.Odbc.OdbcException: ERROR:[HY001] [Microsoft][ODBC Excel Driver] Cannot open any more tables.

    I know it is not the connections, I know it is not the amount of data being inserted into the table.

    Previous insert statments that are almost identical work, and it consistently fails on the same records, but if you try the same record more than once it will often work.

    I have no clue and would appreciate the help
    Wednesday, November 1, 2006 11:56 PM

Answers

  • I discovered if I close the connection after about 500 inserts, and then reopen it before I continue that everything works the way it should.  Must be a problem with the driver.
    Wednesday, November 8, 2006 3:35 PM

All replies

  • It could explain why you are getting this error

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;304536

    Thursday, November 2, 2006 11:21 AM
    Moderator

  • Actually you do not need to insert line by line. You can use SQL to insert data from one Workbook into another. Below is an example:

    INSERT INTO [WorksheetName$] (F1, F2, F3, F4) SELECT F1, F2, F3, F4 from [Excel 8.0;DATABASE=E:\My Documents\Book20.xls;HDR=NO;IMEX=1].[Sheet1$];
    Thursday, November 2, 2006 1:55 PM
  • Thanks for the thoughts, but I have the latest driver for XP it is like SP9 I think.
    So this is not my problem.
    Thursday, November 2, 2006 4:28 PM
  • This may work, but I haven't tried because I have one other thing that I am trying to do.  I need to place all the data I select out of the one sheet in the other sheet as text.  So I need to format them all with "'" + DataReader.GetValue(x).ToString().Replace("'","") + "'".

    additional information:  If I just catch the error and let the program continue it will do other inserts into the same sheet.  Still looking for explanation, but I appreciate the help so far.
    Thursday, November 2, 2006 4:34 PM
  • I discovered if I close the connection after about 500 inserts, and then reopen it before I continue that everything works the way it should.  Must be a problem with the driver.
    Wednesday, November 8, 2006 3:35 PM