import all excel sheets from a specified folder, and save as STATA files

[Natasha Arora]

Hello - I am attempting to import all excel sheets from a specified folder, and save as STATA files. I am using the following code, and do not receive an error message - but no files are imported. Can anyone detect an error, or have an alternate solution to this problem?



local files : dir "G:\CTRHS\CCHE\Projects\KFHP - MELO\14. Virtual Care Initiative\3--Data\Clinical Data\Baseline data downloads\AP" files ".xlsx"

cd "G:\CTRHS\CCHE\Projects\KFHP - MELO\14. Virtual Care Initiative\3--Data\Clinical Data\Baseline data downloads\AP"

foreach file in `files' {
import excel using `file', sheet("Backend for data export ") firstrow clear
local hcid = HC_ID[1]
save "`hcid'.dta", replace
}

[daniel klein]

The local maco files does not contain anything; you want

..."*.xlsx"

Also, see xls2dta (SSC):

[William Lisowski]

Added in edit: This crossed with Daniel's answer, who recommends his excellent xls2dta package, which I had planned on recommending until I thought that running your do-file in pieces might be the problem. Daniel correctly points out the error in your matching pattern, but I leave my answer in case it helps.

I believe your problem is that you have written your code in the do-file editor window, and then rather than running everything at once, you are running it by selecting a few lines and running them, then selecting the next few lines and running them, and so on.

Consider the following example. In the do-file editor window, I have a two-line program that I run in its entirety.

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD17616.000000"

. local message Hello, world.

. display "The message is `message'"
The message is Hello, world.

.
end of do-file

Now I run the same two lines by selecting the first line and running it, then selecting the second line and running it.

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD17616.000000"

. local message Hello, world.

.
end of do-file

. do "/var/folders/xr/lm5ccr996k7dspxs35yqzyt80000gp/T//SD17616.000000"

. display "The message is `message'"
The message is

.
end of do-file

The important thing to keep in mind is that local macros vanish when the do-file within which they were created ends. If you look carefully at the results above, you'll see that when I selected a single line to run, it was copied into a temporary do-file and run, so even though both lines are in the same window in the do-file editor, they are run as separate do-files, and local macro defined in the first line vanishes at the end of that do-file, and is undefined when the second line is run.

So in your case, if you ran the first line, and then the succeeding lines, the local macro files will be undefined and the loop will loop over nothing.

[Clyde Schechter]

There is nothing obviously wrong with the code. Several ideas cross my mind:

1. That directory name is a little funky. It's very long and it also contains a . character (which is legal but which might confuse something for a filename extension). If you -cd- to that directory as your first command, you won't need to mention it in your -local files- command and that might help.

2. You don't describe what the contents of HC_ID[1] might look like, but it is possible that in combination with that pathname, the resulting string is too long to work as a path/filename combo (that would be an OS limitation, not a Stata limitation). If that's the case you probably have to rename some of the directories along the path to come up with something shorter.

3. If none of those things help, try putting -display `"`files'"' after the -local files- command to verify that Stata is actually finding those .xlsx files.

4. Are you sure you have both read and write access to that directory?

5. Are you running this all together in one fell swoop? If you are doing it line by line or in any way that separates the -local files- command from the -foreach command-, the contents of `files' will have been forgotten by the time the -foreach- loop is reached (although I would expect that to produce an error since -foreach file in {- would be illegal syntax.)

Hope this helps.

Added: Crossed with #2 and #3. I note that Daniel Klein in #2 is right--I didn't notice that error in the code. I'm pretty sure that's the only problem.

[Natasha Arora]

Thank you all for your responses, Daniel, Clyde, William. xls2dta is great and I learned a lot this afternoon!

I used the following code successfully.

** import all Excel files, convert into STATA fles, and append into a single STATA file
cd "G:\CTRHS\CCHE\Projects\KFHP - MELO\14. Virtual Care Initiative\3--Data\Clinical Data\Baseline data downloads\AP"
xls2dta, save(./) sheets("Backend for data export ") : import excel using ./, firstrow allstring
xls2dta , save(./all_data_appended.dta) : append

*load the appended file
use "G:\CTRHS\CCHE\Projects\KFHP - MELO\14. Virtual Care Initiative\3--Data\Clinical Data\Baseline data downloads\AP\all_data_appended.dta", clear

*imported all variables as strings in order to not have mismatches; convert to numeric
destring Mar_21_clinic - May_22_video, force replace