[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

From |
"Sergiy Radyakin" <serjradyakin@gmail.com> |

To |
statalist@hsphsun2.harvard.edu |

Subject |
Re: RE: st: Appending several excel data sets into one |

Date |
Mon, 17 Sep 2007 16:14:28 +0200 |

Here is a quote from Mr. Coveney "If workbooks contain multiple sheets, you would like to access the sheet names. Excel doesn't provide this facility, but an add-in called ASAP-utilities http://www.asap-utilities.com/ includes one to create an index sheet listing all sheets in the workbook. " And this is exactly where my comment was pointed to (index sheet). Otherwise I would have posted a VBA code that appends 200 sheets together. The quoted utilities package actually retails for $49! What a deal! (after having a look at what the utilities actually do, e.g. "Displays the filename in the titlebar" or "Close all saved files" I think that the code of each of those utilities is no longer than 5-10 lines). If we go the "external way", why don't we just buy software to append Excel sheets? Just google it and take the first best (in my case Google came up with http://www.office-excel.com/excel-addins/tables-transformer.html for 39$, which claims to be able to append Excel sheets. I honestly have no clue on what it does and how it does it, but it looks more adequate than the utilities suggested above.) The second-best DigDB provides a price comparison of the similar tools, which might be useful for other users: http://www.digdb.com/purchase/ but I feel a pain to pay $50 for 10 lines of code (feels like having to buy .ado files -- something unheard of). It might take 5 more minutes to find a ready snippet on the web. As for reproducibility of the results, you can execute VBA code from the same Stata do file. I do not see a problem here (it does not look elegant, though). Best regards, Sergiy Radyakin On 9/17/07, Michael Blasnik <michael.blasnik@verizon.net> wrote: > I think you miss the point. Many Stata users, myself included, receive data > they need to analyze from others in Excel format -- sometimes in many separate > xls files. These files often have data type (and format) issues -- at least > when accessing them using default methods and the MS ODBC driver. I don't want > to launch Excel, edit the raw datasets (running macros, etc), save my new > versions and then import these files into Stata. Instead, I want to be able to > work completely within Stata to get the data I need as it is provided in > original format. By using an all-Stata approach, I can produce a single do file > that performs an entire analysis -- from raw data to final results. This level > of automation is critical to me so that I have a fully reproducible and > documented analysis. Just because Excel CAN do something doesn't mean that's a > good approach to take. > > Michael Blasnik > > p.s. it appears that you have a sticky "?" key on your keyboard :) > > ----- Original Message ----- > From: "Sergiy Radyakin" <serjradyakin@gmail.com> > To: <statalist@hsphsun2.harvard.edu> > Sent: Monday, September 17, 2007 8:52 AM > Subject: Re: RE: st: Appending several excel data sets into one > > > > Excel does not provide a facility to access sheet names????????? > > Download utilities???? > > What are we talking about? > > ------------------------------------------------------------------------------------------ > > Public Sub Liste() > <snip>> End Sub > > ------------------------------------------------------------------------------------------ > > This will create an index sheet in an arbitrary Excel file. > > (No external utilities or black magic required). > > Will probably work on every Excel version from the late 1980s or so... > > Couple of lines can obviously be removed, but are left for clarity. > > > > As for the types of variables, you can simply declare cells of having > > cirtain type, e.g. numeric, or strings, or dates in exactly the same > > way you will do it in any other system. Since Stata supports only two > > types of variables, you do not see it very often explicitly, unless > > you want to create a new variable. (oh, yes, yes, there are date > > variables in Stata 10 too.) However istead of directly specifiying > > the type, you rely on the "The DaVinci Code of the Missings": > > > > gen a = . > > gen b = "" > > > > so having seen this, any new-comer will be puzzled by the following line: > > > > gen c = "." > > > > What is the variable type? Is it assigned missing values ? How can I be sure? > > > > > > Excel will do a good job as long as it is programmed to do it. Just > > switch off all automatic "bells and whistles" and tell it what you > > want to do in a program (same as you do in Stata). > > > > At least when it comes to programming, Excel is much superrior to > > Stata with its Yeti-like macros found in no other interpreters aside > > assembler. > > > > I have originally suggested to append the sheets in Excel, and after > > following the responces with StatTransfer etc. still think it is not a > > bad idea. > > > > Best regards, > > Sergiy Radyakin > > * > * 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/

**Follow-Ups**:**Re: RE: st: Appending several excel data sets into one***From:*"Neil Shephard" <nshephard@gmail.com>

**Re: RE: st: Appending several excel data sets into one***From:*"Michael Blasnik" <michael.blasnik@verizon.net>

**References**:**Re: RE: st: Appending several excel data sets into one***From:*Joseph Coveney <jcoveney@bigplanet.com>

**Re: RE: st: Appending several excel data sets into one***From:*"Sergiy Radyakin" <serjradyakin@gmail.com>

**Re: RE: st: Appending several excel data sets into one***From:*"Michael Blasnik" <michael.blasnik@verizon.net>

- Prev by Date:
**Re: RE: st: Appending several excel data sets into one** - Next by Date:
**Re: RE: st: Appending several excel data sets into one** - Previous by thread:
**Re: RE: st: Appending several excel data sets into one** - Next by thread:
**Re: RE: st: Appending several excel data sets into one** - Index(es):

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