1 item has been added to your cart.

Can I use ODBC to write to an existing Excel file?

The general answer is yes; however, be aware of some limitations of the Excel ODBC driver.

Closing Excel worksheet before using Stata

For the Excel ODBC driver to work properly, any instance of Excel that has the spreadsheet open must first be closed. If the worksheet is left open in Excel and you try to perform ODBC operations on the same worksheet using Stata, an error will result. Unfortunately, the error message that the driver issues is not descriptive and does not provide any guidance about closing the other application.

Unsupported SQL

The Excel ODBC driver does not support the use of SQL statements DELETE, UPDATE, or ALTER. Microsoft lists this shortcoming on their support website in an article written for Excel 97, even though it applies to later Excel versions:

The driver will not support DELETE, UPDATE, or ALTER TABLE statements. While it is possible to update values, DELETE statements will not remove a row from a table based on an Excel spreadsheet. These operations are not supported. Basically, you can only append (insert) to a table.

Due to the SQL limitations, the following Stata ODBC insert command with the overwrite option will fail because the operation first clears the table using a DELETE statement.

        . webuse restaurant, clear
        . odbc insert, table(restaurant) dsn(test_one) overwrite

If you perform any direct SQL on an Excel worksheet, you will also not be able to use these three commands. However, you can still append data to an existing table, for example, using the following command:

        . webuse restaurant, clear
        . odbc insert, table(restaurant) dsn(test_one) insert 

You can also use the create option to create a new sheet or table.

        . webuse restaurant, clear
        . odbc insert, table(restaurant) dsn(test_one) create

Solutions to limitation

If you really need a repository for data, consider using a database such as Microsoft Access. The database does not have to be anything fancy, but it would provide more robust capabilities for storing and updating data than an Excel spreadsheet would. If needed, an Excel spreadsheet can then be populated with portions of the Access database. However, Stata would interact with the Access database via ODBC.

If you are serious about sticking with an Excel spreadsheet, you might conclude that ODBC was simply overkill and that you only needed to outsheet the data and import it via Excel. You might lose some characteristics of your spreadsheet, such as colors, display formats, and dimensions, but you would have lost them using ODBC as well. When transferring data between formats, only the data are transferred. Information that is useless for one application will typically not be carried over.

If you are using Excel in Office XP or Office 2003, you may also consider the added XML support, which works with Stata 9 or later. For more information, see xmluse in [D] xmlsave.