Hello everyone,

I just joined Statalist. So this is my first post.

I would like to give you a brief idea about what I want to do and then explain the problem.

I have daily meter reading data of many firms stored in excel files of 3 sheets for each firms. I want to make a single dta file for each firm or file which has the same name as the excel file. I had to clean each excel sheets for a suitable data structure.

Following some posts in this forum, I was able to make a do file that worked partially:-

- cd "${january_T1}/J"

// number of sheets 3, so i=3
foreach f of local filenames {
forval i=1/3 {
display `"Importing `f'"'

import excel using "${january_T1}/J/`f'" ,sheet("Table `i'") cellrange(A6:R31) clear // the cellrange for sheets 1 and 2 is same
tostring _all, replace // making all observations string

replace A = "int_" + A if _n!=1 // adding prefix to intervals

rename A intervals // rename first column as intervals which is hourly intervals

** add prefix to first row as they start with numbers

foreach v of var B-Q {
replace `v' = "date_" + `v' if _n == 1

}
*** Replacing first row as variable names

foreach x of varlist B-Q {
local vname = strtoname(`x'[1])
rename `x' `vname'
}

drop in 1 // dropping first row

sxpose2, clear varname // transpose columns to rows for appending sheets

*** Replacing first row as variable names
foreach x of varlist _varname-_var24 {
local v_name = strtoname(`x'[1])
rename `x' `v_name'
}

cap gen filename = "`f'_Sheet`i'"

save "${meter_temp}\January\T1b/`f'_sheet`i'.dta", replace //
}

// loop over all sheets in each data file, while appending them and converting them to one dta for each firm for the month

*I am struggling to do the appending of 3 sheets using the code below, as I am not sure how to put it inside the loop above or even outside the loop.*

// number of sheets 3, so i=3

foreach f of local filenames {

use "${meter_temp}\January\T1b/`f'_sheet1.dta" , clear
append using "${meter_temp}\January\T1b/`f'_sheet2.dta"
*append using "${meter_temp}\January\T1b/`f'_sheet3.dta"

save "${meter_work}\January\T1b/`f'.dta", replace

}

Issues:

1) in ${meter_temp}\January\T1b folder, sheet 1 and 2 dta files are separately generated (for example t-6_8639746_january.xlsx_sheet1.dta and t-6_8639746_january.xlsx_sheet2.dta) however in the file names of sheet 1 and 2 ".xlsx" gets captured which I do not want. How can I remove this .xlsx part?
2) How can I append data of sheet 1-3 for each firm from the same folder keeping the same name? - shared an image file of all the files in the folder (https://ibb.co/ZHnJpyX)