st: RE: -odbc-, Excel cell range

["Rodrigo Martell" <rodrigo.martell@frontier-economics.com.au>]

Hi Philipp,

I can successfully select ranges by highlighting the range of cells I want in Excel and then doing the following:

1. Go to Insert in the Menu Bar > Name > Define  and give your cell range a name like MyRange or whatever.
-odbc- will recognize this as a table when it tries to import data.

2. Go to Stata and use the -odbc- command to load the table you've just defined with a command that looks like this:

	 odbc load, dsn("Excel Files;DBQ=test.xls") table(MyRange)    
             
note: here I'm assuming test.xls lives in your current directory so that no full path specification is needed.

3. Done. Now the data should be in Excel.

I hope this helps.

Cheers,

Rodrigo

Rodrigo Martell

 <http://www.frontier-economics.com> 	
Frontier Economics Pty. Ltd.
395 Collins Street
Melbourne VIC 3000
Australia
www.frontier-economics.com 	
switch:
direct:
fax:
mobile:
email:


+61 (0)3 9620 4488
+61 (0)3 9613 1518
+61 (0)3 8614 2711
+61 (0)407 909 811
rodrigo.martell@frontier-economics.com <mailto:rodrigo.martell@frontier-economics.com> 


This e-mail, including any attachments, may contain confidential and privileged information for the sole use of the intended recipient(s). Any review, use, disclosure or distribution by others is strictly prohibited. If you are not the intended recipient (or authorised to receive information for the recipient), please contact the sender by reply e-mail and delete all copies of this message. Thank you.

	



-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu]On Behalf Of Philipp Rehm
Sent: Friday, 22 December 2006 9:10 AM
To: statalist@hsphsun2.harvard.edu
Subject: st: -odbc-, Excel cell range


I am trying to read data from an Excel (work)sheet into Stata, using -odbc-.

This works:
odbc load, dsn("Excel Files;DBQ=test.xls") exec("SELECT * from [s1$]")

The file "test.xls" is from here: 
http://www.ats.ucla.edu/stat/stata/faq/test.xls, and looks like this:
. list

      +--------+
      |  a   b |
      |--------|
   1. |  2   5 |
   2. |  3   6 |
   3. |  4   8 |
   4. |  5   9 |
   5. | 76   0 |
      +--------+


I would like to define a certain cell-range in the Excel worksheet from 
which the data are read into Stata. Let's say I want to choose only the 
bottom row, so that the data would look like this:
      +--------+
      |  a   b |
      |--------|
   1. | 76   0 |
      +--------+

I thought that this code would get me there:
odbc load, dsn("Excel Files;DBQ=test.xls") exec("SELECT * from [s1$a6:b6]")

However, what I get is this:
. list

      +---+
      | b |
      |---|
   1. | 5 |
   2. | 6 |
   3. | 8 |
   4. | 9 |
   5. | 0 |
      +---+

So, my question is: using -odbc-, how can I select a cell-range inside a 
worksheet of an Excel file?

Thanks,
Philipp
*
*   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/

*
*   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/