Leading zeros at import

[louise nek]

Hi!
I want to import Excels files in a loop where the names include zeros. However, STATA ignores the zeros and reports that the Excel files cannot be found.

The Excel files are called: P+091231.xls, P+101231.xls etc. (located in the folder raw).

This is my code:

forvalues j=09(1)12{
import excel "raw\P+`j'1231.xls", sheet("A") firstrow clear
save "temp\P_20`j'12.dta", replace
clear
}

Error message: file raw/P+91231.xls not found. (So here STATA has searched for the file ...9... instead of ...09...)

Is it possible to solve this without having to change the names of the original Excel files?

Thank you very much for you help!
/Louise

[Charlie Joyez]

Hello Louise,
I think the issue comes the the forvalues loop, which implies you're using numbers, and treats the 09 as a 9.

You could try using another loop specification, as a foreach loop which allows strings, and will treat "09" as a string.

See example below (and note the " " around the `j')

foreach j in 09 10 11 12{
import excel "raw\P+"`j'"1231.xls", sheet("A") firstrow clear
save "temp\P_20"`j'".dta", replace
clear
}

Nb : code not tested, beware of typos.

Best,
Charlie

[Mike Lacy]

A quick hack for this would be to just detect any values of j < 10, and insert a leading "0" into the local:

forvalues j=09(1)12{
     if (`j' < 10) {
        local jj = "0`j'"
    else {
       local jj = "`j'"
    }
    import excel "raw\P+`jj'1231.xls", sheet("A") firstrow clear
    save "temp\P_20`jj'12.dta", replace
   clear
}

I think there's a way to apply a -format- to a number put into a local, which is the more general way to solve this problem, but I can't recall it.

Another "nice" solution is to use an extended macro function to get a list of files and use -foreach- per Charlie's suggestion:

local flist: dir "raw" files "P*.xls"
foreach f of local flist {   
      import "`f'" ........
      local out = subinstr("`f'", "xls", "dta", .)
      save "temp\`out'", replace
}

Both codes are untested, but should be pretty close.

[Clyde Schechter]

I think there's a way to apply a -format- to a number put into a local, which is the more general way to solve this problem, but I can't recall it.

Yes, here we could do it as

forvalues j = 9/12 {
    local jj: display %02.0f `j'
    import excel "raw/P+`jj'1231.xls", // ... etc.
}

[louise nek]

Wow great, it works! Thanks a lot! =)

[Nick Cox]

Clyde's solution is also documented in this 2010 paper, accessible to all:

http://www.stata-journal.com/sjpdf.h...iclenum=pr0051