Joseph Coveney mentioned that ODBC could be used to update an Excel
spreadsheet. Joseph was correct, at least in theory. The problem is
that the ODBC driver for Excel is very limiting. Apparently workbooks
can be created with ODBC, but they can never be updated once they are
populated initially.
I found the following explanation of Microsoft's website. The posting refers
to Excel 97, but from what I can see the behavior is the same for Excel 2000.
Here is the URL:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q178/7/17.ASP&NoWebContent=1
Here is the most relevant excerpt:
Excel Driver Limitations
* Inserting into table:
Applications that want to use the Save As option for Excel data would
issue a CREATE TABLE statement for the new table and then do subsequent
INSERT operations into the new table. INSERT statements result in an
append to the table. No other operations can be done on the table until
it is closed and reopened the first time. After the table is closed
the first time, no subsequent inserts can be done.
----------------
If you are using Stata for data management, not having the ability to
UPDATE your excel file could be a big problem.
A work around that I recently tested involved using an Access database
(*.mdb) to hold the data and then use Excel to import the data. Using this
method is admittedly somewhat long-winded, but it does seem to work where
the direct method does not. I want to stress that I have _not_ tested this
extensively and there could be pitfalls, although I don't know of any right
now.
I have included an example below for using an Access database to act as
an interface for Stata and Excel. Although Stata can write to the database,
I do not believe that Excel can be used to update it. However, you will be
able to save the imported data into its own *.xls file.
****************
1. Using the ODBC Data Source Administrator, create a new ODBC
data source using the "Microsoft Access Driver (*.mdb)".
2. Once this has been done, Stata can be used to read and write
data to the new data source.
Example:
. webuse restaurant, clear
. odbc insert, table(restaurant) dsn(test_one) create
Note: Since the database was initially empty, the first step
was to create the table and add the data. The -create-
option did just that. Also see that my -dsn- was
called "test_one". This was the name that I used when
creating the database in step 1.
3. The data can now be integrated with Excel.
a) Open Excel.
b) Start with an empty workbook.
c) Enter the "Data" menu and select "Get External Data" and then
select "New Database Query".
d) A dialog will be presented that will allow you to choose the
data source. In my test case, this was called "test_one" as
noted above.
e) Once the correct data source has been selected, finish the
wizard to import the data into Excel.
4. Keeping Stata and Excel in sync.
Excel can be kept in sync with Stata by using "! Refresh Data"
under the "Data" menu.
Example:
Let assume you have completed the steps above, leaving Stata and
Excel open. Now in Stata do the following:
. keep in 1/5
. keep fam - kids
. odbc insert, table(restaurant) dsn(test_one) overwrite
Now in Excel use "! Refresh Data" under the "Data" menu.
Here is what you get.
family_id restaurant income cost kids
1 1 39 5.444694519 1
1 2 39 6.194459915 1
1 3 39 8.182085037 1
1 4 39 9.861741066 1
1 5 39 9.667908669 1
****************
James Hassell
[email protected]
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
