Loop/program to convert a folder of excel files to stata files

[Karishma DSouza]

Hi,

I have a folder with excel files, each with a different name. I need to export each of them to stata while running an already prepared do file on each of them. I am not sure how to do this. In the past, I have always imported one excel file at a time using the import excel command but assume that I probably can write a loop to pull in each file and run the do file on each? Any guidance on how to approach this would be much appreciated.

Thanks,
Karishma

[daniel klein]

See xls2dta from SSC.

[Daniel Schaefer]

Sounds like you have the right idea. I didn't test, but your for loop might look something like this:

cd "path/to/working/directory"
foreach excel_file_name in file_name_1 file_name_2 file_name_3{
    import excel using `excel_file_name', clear
    do do_file_name
}

There are lots of ways to do this. You could just paste the entire do file into this for loop, and get rid of the line starting with the -do- command. You could turn your do file into a command using -program-. It's really up to you.

Check out the programing with do files documentation https://www.stata.com/manuals13/u16.pdf

[cu dao huy]

Herewith my solution:

cd "path to your folder"
global filenames : dir"."files"*.xlsx"
foreach filename in $filenames {
    import excel using `"`filename'"', first all clear
    local filename1 = subinstr("`filename'"," ","",.)
    local filename2 = subinstr("`filename1'",".xlsx","",.)
    save `"`filename2'.dta"', replace
clear

[daniel klein]

It is always good to understand and be able to implement the general idea. Once we have done that, we can save ourselves some trouble. Here is the xls2dta syntax for comparison:

cd "path to your folder"
xls2dta : import . , firstrow allstring

[Karishma DSouza]

Thank you. That certainly helped me understand and Cu Dao Huy's code worked perfectly. I do have another question. I am also trying to create a column with the filename of each file. However, the code below appears to fail and I'm not sure why.

global filenames : dir"."files"*.xls"
foreach filename in $filenames {
    import excel using `"`filename'"', firstrow cellrange(A2) all clear
    gen file_name = "`filename'"  // Adding column for filename date
    local filename1 = subinstr("`filename'"," ","",.)
    local filename2 = subinstr("`filename1'",".xls","",.)
    do "$datadir\Pharmaprojects\Pharmaprojects_3June20.do"
    save `"`filename2'.dta"', replace
    }
clear

[cu dao huy]

Thank you. That certainly helped me understand and Cu Dao Huy's code worked perfectly. I do have another question. I am also trying to create a column with the filename of each file. However, the code below appears to fail and I'm not sure why.

global filenames : dir"."files"*.xls"
foreach filename in $filenames {
import excel using `"`filename'"', firstrow cellrange(A2) all clear
gen file_name = "`filename'" // Adding column for filename date
local filename1 = subinstr("`filename'"," ","",.)
local filename2 = subinstr("`filename1'",".xls","",.)
do "$datadir\Pharmaprojects\Pharmaprojects_3June20.do"
save `"`filename2'.dta"', replace
}
clear

Hi,
you may want to fix your do-file bugs, try "set trace on" before your loop. It will show where the bugs occur in specifically step!

[Karishma DSouza]

Thanks. That helped and "set trace" was a great tip that I didn't know about.