Appending many excel files

[wesso dadoyan]

if i have the following 3 files with 400,000 rows each:
"File1.xlsx'
"File2.xlsx"
"File3.xlsx"
How can I append the 3 files in stata and then use the data as one dataset?

[Joseph Coveney]

There might be an FAQ on this somewhere, but if not, then you could try something like the following.

drop _all
tempfile cumulator
quietly save `cumulator', emptyok
forvalues i = 1/3 {
    import excel using File`i'.xlsx, . . . clear
    generate str source = "File`i'.xlsx"
    append using `cumulator'
    quietly save `cumulator', replace
}

[wesso dadoyan]

It is not working. I received the following:

. drop _all

. tempfile cumulator

. quietly save `cumulator', emptyok

. forvalues i = 1/3 {
2. import excel using File`i'.xlsx, . . . clear
3. generate str source = "File`i'.xlsx"
4. append using `cumulator'
5. quietly save `cumulator', replace
6. }
option . not allowed
r(198);

end of do-file

r(198);

.

[Joseph Coveney]

Sorry. In place of the three dots, you should substitute the options for import excel that are correct for your Excel workbook files. See the help file for import excel for further information about the available options.

[daniel klein]

Also see xls2dta (SSC).

If these three files are the only files in the current working directory, then

xls2dta , clear generate(source) : append using .

will do what you want.
Best
Daniel

[Raoul Reulen]

Also see xls2dta (SSC).

If these three files are the only files in the current working directory, then

xls2dta , clear generate(source) : append using .

will do what you want.
Best
Daniel

Does this work with CSV files or would I have to convert these to Excel first?

[daniel klein]

Does this work with CSV files or would I have to convert these to Excel first?

Concerning the file conversion, xls2dta is a simple wrapper for import excel, so you would need to convert to Excel first. It is probably easier to just write the loop for importing yourself. You can find links in the announcement of the xls2dta update.

I guess I could probably implement other importing routines such as insheet or the newer import delimited, but this would take a couple of days and it has not often been requested.

Best
Daniel