RE: st: load EXCEL to STATA

["Lachenbruch, Peter" <Peter.Lachenbruch@oregonstate.edu>]

Another small tip for StatTransfer is that you can change the type of variable as you read in.  I just had a number of worksheets in Excel that would be read into Stata as strings, even though they were supposedly numbers.  In the variables tab in StatTransfer you can change the type (assuming they are the string equivalent of numbers) quite easily.  The researcher had also used -9 for missing on most variables, but 99999 for missing on dates (which gave Sept 9 99 and a very large number).  The mvdecode in Stata was easily used.  

Also, I had labeled variables in an earlier data set, and needed to get the right labels set up.  A merge with the old data did the trick.  

Finally, most of the variables were in UPPER CASE - renvars did the trick - actually, I left the remaining string variables in UPPER CASE for easy identification.

There probably are easier ways to do this, but these worked just fine.

Again, StatTransfer is absolutely indispensable to my work.  You can get it through the Stata store or from Circle Systems.

Tony

Peter A. Lachenbruch
Department of Public Health
Oregon State University
Corvallis, OR 97330
Phone: 541-737-3832
FAX: 541-737-4001


-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Phil Schumm
Sent: Thursday, April 01, 2010 5:29 AM
To: statalist@hsphsun2.harvard.edu
Subject: Re: st: load EXCEL to STATA

On Mar 31, 2010, at 10:16 PM, G. Dai wrote:
> I have a very big data set which is consist of hundreds of  smaller  
> EXCEL table sheet.  Each EXCEL table sheet actually includes many  
> county observations in one state.
>
> To read the EXCEL in my STATA, I need first OPEN the EXCEL file and  
> then SAVE it as CSV form.  However, this easy task seems very time  
> consuming since hundreds of such sheet need to be open and save!
>
> So, is there any easy to read the EXCEL sheet without saving it into  
> CSV form?  Last, the EXCEL sheet actually includes a title and short  
> description notes at the beginning of the sheet, which is very  
> annoying.


As Martin suggested, Stat/Transfer would make short work of this,  
including giving you the ability to exclude the title and description  
at the beginning of each sheet.  Stat/Transfer is a great program, and  
should be part of any data analyst's toolkit.

That said, another possibility you might consider is the xlrd module  
available for Python (http://pypi.python.org/pypi/xlrd).  This will  
permit you to work with the data (and even the formatting) in Excel  
files without having to deal with Microsoft's Component Object Model  
(i.e., you can use it on any platform).  A good tutorial is available  
here:

     http://www.python-excel.org/

For example, to read all of the sheets in a workbook and write them to  
tab-delimited files (which you could then -insheet- in Stata), you  
could do something like this:


import xlrd, csv

workbook = xlrd.open_workbook('workbook.xls')
for name in workbook.sheet_names():
     sheet = workbook.sheet_by_name(name)
     data = []
     for row in range(sheet.nrows):
         data.append(sheet.row_values(row))
     outfile = open('%s.txt' % name, mode='w')
     writer = csv.writer(outfile, dialect='excel-tab')
     writer.writerows(data)
     outfile.close()


Personally, I use Stat/Transfer whenever possible, but occasionally  
use xlrd when (1) the format of the Excel workbook is funky enough  
that Stat/Transfer can't handle it, or (2) I have to provide a  
solution to someone who doesn't own Stat/Transfer.


-- Phil

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/