Importing multiple Excel files and saving them as .dta files

[Chris Rooney]

I'm not very familiar with loops in Stata, and this is essentially my first attempt, which is why it is probably not working.

Basically, I have around 135 Excel workbooks (not sheets!) in a directory and want to create a loop to save them as .dta files. Googling around a bit, I came across this answer from Nick Cox: http://www.stata.com/statalist/archi.../msg00083.html

My excel files are currently named as Data_1, Data_2, Data_3 etc. I just wanted to test with four files before I actually applied the loop to all the files to test if it was working as I expected, and this is shown in the code below. Note that for each Excel workbook, the data is on Sheet 1.

The error that Stata brings up is 'No; data in memory would be lost' (r4)

Here is my code:

local mylist 1 2 3 4
foreach x of local mylist {
import excel D:\Testing\Data_`x'.xlsx,
sheet("1") firstrow clear

save `x', replace

}

[daniel klein]

Chris, the options should be in the same line as the command. So either code

...
import excel D:\Testing\Data_`x'.xlsx, sheet("1") firsrow clear

or

...
import excel D:\Testing\Data_`x'.xlsx, ///
sheet("1") firsrow clear

Also see xls2dta from SSC for a canned solution.

Best
Daniel

[Chris Rooney]

Chris, the options should be in the same line as the command. So either code

...
import excel D:\Testing\Data_`x'.xlsx, sheet("1") firsrow clear

or

...
import excel D:\Testing\Data_`x'.xlsx, ///
sheet("1") firsrow clear

Also see xls2dta from SSC for a canned solution.

Best
Daniel

Ah thanks, will see if it works now. But the xls2dta add-on looks promising as well! Thanks for the tip