Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
st: Merge files of tables with headers and multiple identifiers
From
Minsoo Kim <[email protected]>
To
statalist <[email protected]>
Subject
st: Merge files of tables with headers and multiple identifiers
Date
Wed, 13 Jul 2011 00:01:41 -0500
Hi,
I am using STATA SE/11.2 and I have been trying to merge ten excel
files(2000~2009). Each excel file has about 600 hundreds of tables
with headers for each table and with two empty rows between each
table.
I would like to illustrate what I need to do with two excel
files(2000, 2001) for now. The original exce files I have can be
simiplified as follows:
<Excel File1>
Company Name Year of File Table Name
Gross Earning Oper. Exp & Taxes Net Earnings
ABERD 2000 Income Statement 1997
300 50
ABERD 2000 Income Statement 1998
311 52
31
ABERD 2000 Income Statement 1999
323 55
32
Company Name Year of File Table Name Asset
Road and Equipment Securities Owned Total
ALABA 2000 Balance Sheet 1997
60 20
500
ALABA 2000 Balance Sheet 1998
62 23
502
Company Name Year of File Table Name Liabilities
Captial Stock Bonded Debt Total
ALABA 2000 Balance Sheet 1997
70 10
500
ALABA 2000 Balance Sheet 1998
76 12
502
<Excel File2>
Company Name Year of File Table Name
Gross Earning Oper. Exp & Taxes Net Earnings
Surplus for Year
ABERD 2001 Income Statement 1998
311 52
31
ABERD 2001 Income Statement 1999
323 55
32
ABERD 2001 Income Statement 2000
303 53
30 16
Company Name Year of File Table Name Asset
Road and Equipment Securities Owned Total Cash
ABERD 2001 Balance Sheet 1998
62 23
502
ABERD 2001 Balance Sheet 1999
63 22
503 15
Company Name Year of File Table Name Liabilities
Captial Stock Total
ABERD 2001 Balance Sheet 1998
76 502
ABERD 2001 Balance Sheet 1999
72 503
<Goal Data Set>
Company Name Year of File Table Name
Gross Earning Oper. Exp & Taxes Net Earnings
Surplus for Year
ABERD 2000 Income Statement 1997
300 50
ABERD 2000 Income Statement 1998
311 52
31
ABERD 2000 Income Statement 1999
323 55
32
ABERD 2001 Income Statement 2000
303 53
30 16
Company Name Year of File Table Name Asset
Road and Equipment Securities Owned Total Cash
ABERD 2000 Balance Sheet 1997
60 20
500
ABERD 2000 Balance Sheet 1998
62 23
502
ABERD 2001 Balance Sheet 1999
63 22
503 15
Company Name Year of File Table Name Liabilities
Captial Stock Bonded Debt Total
ABERD 2000 Balance Sheet 1997
70 10
500
ABERD 2000 Balance Sheet 1998
76 12
502
ABERD 2001 Balance Sheet 1999
72
503
As above, I need to add data of other nine excel files into the first
data set(the earliest excel file). If it doesn't have any match with
the first excel file, I still need to add it into the first data set
in a seperate table.
As there are headers in each table and multiple identifiers, merge
command by itself cannot generate the Goal Data Set as I desire.
Can anyone help me to make resolve this issue?
Thanks,
--
Minsoo
The University of Chicago
*
* 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/