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)
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)
Comment