Append Excel File's Sheets

[Budu Gulo]

My Excel file "ws_dscd" has three sheets : "UK-US", "CN-JP", and "Others". The first row of each sheet contains variable names. How can I append all the sheets ?

[Clyde Schechter]

Something like this:

clear*
tempfile building
save ` building', emptyok

foreach s in UK-US CN-JP Others {
    import excel using ws_dscd.xlsx, firstrow sheet("`s'") clear
    append using `building'
    save `"`building'"', replace
}

use `building', clear

Note: Not tested. Beware of typos, etc.

Of course, this assumes that the data in those three sheets are actually suitable for appending (common variable names, same variable types for same-named variables, etc.) Don't be surprised if you find out they are not really compatible. It may make more sense to import each sheet into a separate data set first, and then separately clean up those data sets so that they have consistent variable names and data types, and then append those.

[Budu Gulo]

Clyde Schechter thank you for the quick response. I have set the current directory according to the file location and applied the code. I get "invalid file specification" error.

. clear*

. tempfile building

. save ` building', emptyok
invalid file specification
r(198);

[Carole J. Wilson]

Looks like you have an extra space between the left ` and the word building.

[Budu Gulo]

Thanks Carole J. Wilson! Now the code works perfectly.

I have another question: If I wanted to append ALL sheets of a given Excel file, how will the code in #2 change?

[Clyde Schechter]

If there are only a small number of sheets, and if you know their names before you write the code, just list them all in the -foreach- command.

If there is a large number of sheets, or if their names are not known before you write the code, then it gets a little more complicated.

clear*
tempfile building
save `building', emptyok

import excel using ws_dscd.xlsx, describe
local n_sheets `r(N_worksheet)'
forvalues i = 1/`n_sheets' {
    local sheetname_`i' `r(worksheet_`i')'
}

forvalues i = 1/`n_sheets' {
    import excel using ws_dscd.xlsx, clear firstrow sheet(`"`sheetname_`i''"')
    append using `building'
    save `"`buildilng'"', replace
}

use `building', clear

Again, not tested. Beware of typos. (Sorry about that extra space, before.)

My earlier warning that the sheets may not actually be sufficiently compatible to append in this way applies even more strongly with a large number of them. Good luck!

[Budu Gulo]

Thanks a lot Clyde Schechter! I will keep the warning in mind.

[Budu Gulo]

Similar problem with a little twist! I want to ignore row 1 of the sheets, and use the solution provided in #2.

[daniel klein]

The suggested loop is implemented in xls2dta (SCC) for standard tasks like this.

xls2dta , clear allsheets importopts(cellrange(A2)) : append using filename

The twist is just specifying cellrange().

Best
Daniel

[Budu Gulo]

daniel klein : I applied your code on a toy data set. Unfortunately, I did not get the desired output.

xls2dta , clear allsheets importopts(cellrange(A2)) : append using append_excel_2nd_row.xlsx

. list

     +----------------------+
     |  A     B     C     D |
     |----------------------|
  1. |  A     B     C     D |
  2. | 12    21   132    13 |
  3. | 23    23    23   254 |
  4. |  A     B     C     D |
  5. | 53    59   533    55 |
     |----------------------|
  6. | 66   623   623    66 |
     +----------------------+

As you can see, the observations 1 and 4 are unwanted.

[River Huang]

Dear Budu, Please try

xls2dta , clear allsheets importopts(cellrange(A2) firstrow) : append using append_excel_2nd_row.xlsx

[Budu Gulo]

The code now works perfectly. Thanks River Huang and daniel klein !