Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
| From | Minsoo Kim <ibsulkim@gmail.com> |
| To | statalist <statalist@hsphsun2.harvard.edu> |
| 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/