none
How to use an odbcDataAdapter to write to an empty Excel worksheet RRS feed

  • Question

  • I'm trying to use an odbcDataAdapter to write to an empty Excel worksheet.

    I'm beginning to think this is not possible as there seems to be an expectation that there are columns pre-defined in the Excel worksheet.  If I start with a worksheet that has the column names in the first row and use a connection string w/o the ExtendedProperty FirstRowHasNames=0 the data is written to the Excel worksheet.

    Please let me know if writing to an empty Excel worksheet should be possible.

    The log below indicates what I am trying.  Let me know if I have an error in my approach.

    Thanks,
    Eric

    ------------------
    Creating header row in Excel file
    Connection string = Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Documents and Settings\Eric\My Documents\Visual Studio 2008\Projects\ExcelTest\ExcelTest\ExcelTest.xls; READONLY=FALSE; FIRSTROWHASNAMES=0
    Adapter Select command: SELECT * FROM [Sheet1$]
    Adapter Insert command: INSERT INTO [Sheet1$] ([F1], [F2], [F3], [F4], [F5], [F6], [F7]) VALUES (?, ?, ?, ?, ?, ?, ?)
    Adapter Update command: Nothing
    Adapter Delete command: Nothing
    Parameter 0: DBType = AnsiStringDirection = InputIsNullable = FalseOdbcType = VarCharName = @p1Precision = 0Scale = 0Size = 0SourceColumn = F1NullMapping = FalseSourceVersion = Current
    Parameter 1: DBType = AnsiStringDirection = InputIsNullable = FalseOdbcType = VarCharName = @p2Precision = 0Scale = 0Size = 0SourceColumn = F2NullMapping = FalseSourceVersion = Current
    Parameter 2: DBType = AnsiStringDirection = InputIsNullable = FalseOdbcType = VarCharName = @p3Precision = 0Scale = 0Size = 0SourceColumn = F3NullMapping = FalseSourceVersion = Current
    Parameter 3: DBType = AnsiStringDirection = InputIsNullable = FalseOdbcType = VarCharName = @p4Precision = 0Scale = 0Size = 0SourceColumn = F4NullMapping = FalseSourceVersion = Current
    Parameter 4: DBType = AnsiStringDirection = InputIsNullable = FalseOdbcType = VarCharName = @p5Precision = 0Scale = 0Size = 0SourceColumn = F5NullMapping = FalseSourceVersion = Current
    Parameter 5: DBType = AnsiStringDirection = InputIsNullable = FalseOdbcType = VarCharName = @p6Precision = 0Scale = 0Size = 0SourceColumn = F6NullMapping = FalseSourceVersion = Current
    Parameter 6: DBType = AnsiStringDirection = InputIsNullable = FalseOdbcType = VarCharName = @p7Precision = 0Scale = 0Size = 0SourceColumn = F7NullMapping = FalseSourceVersion = Current
    Source table contains 1 rows
    Error updating Excel sheet, on C:\Documents and Settings\Eric\My Documents\Visual Studio 2008\Projects\ExcelTest\ExcelTest\ExcelTest.xls
    Error: ERROR [HYS22] [Microsoft][ODBC Excel Driver] The INSERT INTO statement contains the following unknown field name: 'F2'.  Make sure you have typed the name correctly, and try the operation again.
    ------------------

    Monday, August 9, 2010 9:31 PM

Answers

  • Ahah.

    It's called SELECT INTO.

    SELECT INTO will create a new worksheet with the column names given.
    Note this will err if the named worksheet already exists in the workbook.

    SELECT '' AS [F1], '' AS [F2], '' AS [F3] INTO [newworksheetname]

    The first row of the worksheet will contain the column names, F1 in cell A1, F2 in cell B1, F3 in cell C1 on a worksheet tab named 'newworksheetname'.  It places the corresponding blank values in row 2.

    Subsequently, you can append rows to the newly created worksheet with an INSERT INTO command as shown in my first post in this thread (changing [sheet1$] to [newworksheetname]).

    Eric

    Tuesday, August 10, 2010 4:08 PM