Re: st: load EXCEL to STATA

["G. Dai" <dgecon@gmail.com>]

thanks,Martin,Peter and Phil. Employing StatTransfer is really good
idea. I never heard of anything about python, but I'll see how it
goes.


On Thu, Apr 1, 2010 at 8:30 AM, Lachenbruch, Peter
<Peter.Lachenbruch@oregonstate.edu> wrote:
> 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/
>

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