Using a loop to export excel files

[Marc Triller]

Hi, everyone,

I have an excel spreadsheet with World Bank development indicators for the years 2003-2013. I'd like to create a loop that exports a separate spreadsheet for each year.

Here is a sample of my data:

input int year str52 reporterdesc str3 reporteriso str17(gdp_pc manuf industry)
2003 "Afghanistan"              "AFG" "200.462375926026"  "768258326.019014"  "1030958898.99678"
2003 "Albania"                  "ALB" "1846.12012081207"  "231427088.171808"  "1349879571.14245"
2003 "Algeria"                  "DZA" "2117.04822899287"  "27868320908.4311"  "33920483855.7025"
2003 "American Samoa"           "ASM" "9043.682366545279" "108000000"         ".."               
2003 "Andorra"                  "AND" "31954.1556558944"  "85391740.59167489" "365933968.43826"

This is my code:

* Import excel file
import excel using "intermediate_data/wb_dev_indic.xlsx", firstrow

* Create local macros for each year
local yr "03 04 05 06 07 08 09 10 11 12 13"

* Loop through each year and export a separate spreadsheet
foreach i in `yr' {
   
    export excel using "intermediate_data/wb_`yr'.xlsx" if year=="20`yr'", replace
   
}

I am currently getting a "type mismatch" error code r(109), but I'm not sure why.

The year variable in my spreadsheet is an integer, so perhaps I messed up the syntax and the loop is expecting a string?

[Clyde Schechter]

* Loop through each year and export a separate spreadsheet
foreach i in `yr' {
   
    export excel using "intermediate_data/wb_`i'.xlsx" if year=="20`i'", replace
   
}

[Marc Triller]

Hi, Clyde,

I made the changes you suggested, but Stata is still issuing the same message: type mismatch, error code r(109). Any recommendations for troubleshooting this?

[Daniel Schaefer]

year=="20`i'"

looks like year is an int, but here it's treated as a string. Can you just remove the double quotes?

[Clyde Schechter]

Well, you didn't show example data, so I had to guess about your variable year. Since you had enclosed "20`yr'" in quotes, I guessed that it must be a string variable (which is a bad idea for a year variable, but should still be workable for this purpose.) So that's the only thing I can see that might still be a type mismatch error cause. So I suppose now that year is a numeric variable. In that case, to fix the problem, just remove the quotes from around 20`i' and you should be OK.

In the future to avoid guesswork, which has wasted both your time and mine, you should show example data whenever you ask for help with code. The helpful way to do that is with the -dataex- command. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Marc Triller]

Hi, Daniel and Clyde,

Removing the double quotes did the trick. Thanks for your help!

Clyde, regarding your most recent comment, I used -dataex- to generate sample data, copied a few lines and pasted it into my original post. Did I format it incorrectly or something?

[Clyde Schechter]

Clyde, regarding your most recent comment, I used -dataex- to generate sample data, copied a few lines and pasted it into my original post. Did I format it incorrectly or something?

Sorry. You are right--it was there and it's fine. Somehow it escaped my notice.

[Marc Triller]

No worries - I really appreciate your help with this!