importing and appending loops

[Buyadaa Oyunchimeg]

Dear all,

I tried appending some files using the following codes. Both worked but there are some problems (Code 1 created many duplicates; Code 2- many missing values). I am wondering what could be the problem.


Any help would be appreciated.
Oyun

Code 1:

cd "C:\PC\Results"

forval i = 1/88 {
    preserve
    import excel "C:\PC\Results\Results_copy.xlsx", sheet("Week `i'") firstrow clear
    save "C:\PC\Results\Results_copy_`i'.dta", replace
    restore
    sort idnum
    append using "C:\PC\Results\Results_copy_`i'.dta", force
    save "C:\PC\Results\Results_all'.dta", repalce
    rm "C:\PC\Results\Results_copy_`i'.dta"
            }    

Code 2:

clear
tempfile building
save `building', emptyok

forval i = 1/88  {
    import excel "C:\PC\Results\Results_copy.xlsx", sheet("Week `i'") firstrow clear
    gen source = `"`i'"'
    display `"Appending `i'"'
    append using `building'
    save `"`building'"', replace
} 

[Ali Atia]

forval i = 1/88  {
    clear
    import excel "C:\PC\Results\Results_copy.xlsx", sheet("Week `i'") firstrow
    gen source = `"`i'"'
    tempfile file`i'
    save `file`i''
    local files `files' `file`i''
}
clear
append using `files'

[Buyadaa Oyunchimeg]

Thank you very much for your help.
I have tried the code. It works, but still I got many missing values and duplicates. Is this because of string/numeric mismatch ?

(r(106), so I used force)

"variable comment is str52 in master but byte in using data
you could specify append's force option to ignore this string/numeric mismatch. The using variable would then be treated as if it contained ".
r(106);

[Ali Atia]

Using the force option with append will cause all data in that variable to be treated as missing if Stata formats it as a string upon importing from Excel. Instead of doing that, and potentially losing important data, you can use the import excel's -allstring- option to import all variables as string. After appending them, you can manually destring those variables which should, in fact, be stored as numeric.

As to the duplicates, I don't see any reason the code in #2 should produce duplicates, unless the sheets themselves contain duplicate data. This is something only you can confirm or deny, since I don't have access to the spreadsheet.

[Buyadaa Oyunchimeg]

Thank you very much for your help!