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

Re: st: RE: RE: Importing data from excel with multiple worksheets

From   Jeph Herrin <>
Subject   Re: st: RE: RE: Importing data from excel with multiple worksheets
Date   Fri, 23 May 2008 13:59:13 -0400

I run into this kind of thing sometimes; the best solution I have isn't
very good, perhaps because it isn't very Stata.

I insert a new worksheet, and then link the data from all the other
tables into a single large table with one header row. Excel is very
good at this: in fact, you can easily reference whole pages at once,
then cut out the nusiance rows and drag the data up to be in
consecutive rows.

It is not ideal, but has the advantages that:
   1. The original data isn't touched
   2. The links can always be rechecked; there's a "trail"
   3. It just takes a few minutes
   4. The resulting page can be copied/transferred directly
      into Stata.

hope this helps,

Lachenbruch, Peter wrote:
I have had a similar problem periodically: some well-meaning scientist
creates a data file in excel with the first set of data in rows 1-20,
say with row 1 having some descriptor of the lab data, row 2 having
variable names and rows 3 to 20 having the variable values.
They then skip a few rows and rows 25 to 50 have another set of data,
perhaps from a second hospital with more description in rows 25 & 26 and
then variable names in row 27 (often the same as in row 2), and data in
rows 28 to 50.
This goes on for 10 or 12 data sets, all related, but the text manages
to mess up any ability to read in the data. The brute force method will
split the data into subsets and save them, followed by data entry and
possible merging. Is there an easier, more efficient way to do this?

I should mention I don't have the issue at present, but it has been a
pain in the neck in the past.

Perhaps the most efficient way is to tell the person who created the
files to enter the data properly and come back, but this doesn't gain
many happy faces...


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

-----Original Message-----
[] On Behalf Of Martin Weiss
Sent: Friday, May 23, 2008 8:48 AM
Subject: st: RE: Importing data from excel with multiple worksheets

Try -xmluse- which allows specification of a specific worksheet. Hold
data in -tempfile-s and later append them one after another.

Martin Weiss

Diplom-Kaufmann Martin Weiss
Mohlstrasse 36
Room 415
72074 Tuebingen

Fon: 0049-7071-2978184




-----Original Message-----
[] On Behalf Of Rijo John
Sent: Friday, May 23, 2008 5:23 PM
To: stata
Subject: st: Importing data from excel with multiple worksheets


I have an excel file with many worksheets all of which contains same
set of variables but representing different states. Instead of copying
and pasting all of them one by one into stata and later stacking
different stata files, is there an easier way to stack all the excel
sheets into one data and export it to stata?

* For searches and help try:

* For searches and help try:

* For searches and help try:

*   For searches and help try:

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