Statalist


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

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


From   "Sergiy Radyakin" <[email protected]>
To   [email protected]
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 <[email protected]> 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" <[email protected]>
> To: <[email protected]>
> 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/



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