Stata The Stata listserver
[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

Re: st: How well does Stata interact with Excel?


From   [email protected] (James Hassell, StataCorp)
To   [email protected]
Subject   Re: st: How well does Stata interact with Excel?
Date   Tue, 13 Jan 2004 09:56:08 -0600

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/



© Copyright 1996–2024 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index