Appending large Excel files and creating variables for each file

[John Egyir]

Dear all,

I have a large dataset, 1380 excel files in all. The files are monthly precipitation and have names band1-band1380. The first 12 files, band1-band12, correspond to months: 1, 2,….12 of 1900. Files, band13-band24 represent months: 1, 2,……12 of 1901…….and band1368-band1380 are for months:1,2,……12 of 2014. Each file does not contain variables for month and year.

I would like to write a loop to append all the files but call each file at a time and create the month and year variables in the order explained above.

Any help offered would be appreciated.

Thank you!
John

[Wei Hai Deng]

Would you like the final data in long or wide format?
Also, what is the data structure within each file?

[John Egyir]

Hi Wei,

Thank you for the quick response. Please I would like to have the final data in a lond form. I have just three vars in each file. All vars are numeric.

[Andrew Musau]

tempfile myfile
save `myfile', emptyok
local j 0
forval i=1/1380{
    import excel band`i'.xls, clear
    local ++j
    gen month=`j'
    gen year= 1899+ceil(`i'/12)
    append using `myfile'
    save `myfile', replace
    local j= cond(`j'==12, 0, `j')
}
use `myfile', clear

[John Egyir]

Hi Andrew,

Many thanks for the feedback and the code. I just tested the code. However, I received the following error: "option replace not allowed". When I remove the "replace" option, the code runs perfectly. But the final data contains observations only for month=12 and year=2014.

Is there a way to modify it?

[Andrew Musau]

See the edited code in #4.

[John Egyir]

Hi Andrew,

The eidted code worked perfectly. Thank you for the assistance!