Hi Statalist community,
I have a Excel workbook with 56 worksheets. Each worksheet has the same variables but they are disaggregated by education and age. I am trying import 1 worksheet at a time, do something within the worksheet, and then export it to a new Excel workbook. Based on the Statalist suggestions, I am not going to upload a Excel spreadsheet. Below is code that I develop that does what I want.
I created a local that includes the name of each of the 56 worksheets. However, this invites a lot of error and I am trying to streamline it. Below is what I tried to do but I seem to be missing something. I tried updating my code by using several foreach statements and created two locals to manage the string lists but I was not able to get this to run. Any suggestions would be great. Thank you so much for your help.
I have a Excel workbook with 56 worksheets. Each worksheet has the same variables but they are disaggregated by education and age. I am trying import 1 worksheet at a time, do something within the worksheet, and then export it to a new Excel workbook. Based on the Statalist suggestions, I am not going to upload a Excel spreadsheet. Below is code that I develop that does what I want.
Code:
forval j = 1/56 { clear all *create a local local period `" "G1 high_school, 18_19" "G1 high_school, 20_24" "G1 high_school, 25_29" "G1 high_school, 30_39" "G1 high_school, 40_49" "G1 high_school, 50_59" "G1 high_school, 60_69" "G2 some_college, 18_19" "G2 some_college, 20_24" "G2 some_college, 25_29" "G2 some_college, 30_39" "G2 some_college, 40_49" "G2 some_college, 50_59" "G2 some_college, 60_69" "G3 associates_degree, 18_19" "G3 associates_degree, 20_24" "G3 associates_degree, 25_29" "G3 associates_degree, 30_39" "G3 associates_degree, 40_49" "G3 associates_degree, 50_59" "G3 associates_degree, 60_69" "G4 ba_bas, 18_19" "G4 ba_bas, 20_24" "G4 ba_bas, 25_29" "G4 ba_bas, 30_39" "G4 ba_bas, 40_49" "G4 ba_bas, 50_59" "G4 ba_bas, 60_69" "G5 masters_degree, 18_19" "G5 masters_degree, 20_24" "G5 masters_degree, 25_29" "G5 masters_degree, 30_39" "G5 masters_degree, 40_49" "G5 masters_degree, 50_59" "G5 masters_degree, 60_69" "G6 phd_degree, 18_19" "G6 phd_degree, 20_24" "G6 phd_degree, 25_29" "G6 phd_degree, 30_39" "G6 phd_degree, 40_49" "G6 phd_degree, 50_59" "G6 phd_degree, 60_69" "G7 post_doc, 18_19" "G7 post_doc, 20_24" "G7 post_doc, 25_29" "G7 post_doc, 30_39" "G7 post_doc, 40_49" "G7 post_doc, 50_59" "G7 post_doc, 60_69" "G8 adult_ced, 18_19" "G8 adult_ced, 20_24" "G8 adult_ced, 25_29" "G8 adult_ced, 30_39" "G8 adult_ced, 40_49" "G8 adult_ced, 50_59" "G8 adult_ced, 60_69" "' local pd: word `j' of `period' *import a sheet from the Excel workbook import excel "C:\Users\sample10_03_2023.xlsx", sheet("`pd'") firstrow *Do something in the spreadsheet gen education_age="`pd'" keep education_age *export the data to a new spreadsheet export excel using "C:\Users\sample10_04_2023.xlsx", sheet("`pd'") sheetmodify firstrow(variables) nolabel clear all }
Code:
local education high_school some_college associates_degree ba_bas masters_degree phd_degree post_doc local age 18_19 20_24 25_29 30_39 40_49 50_59 60_69 forval i = 1/8 { foreach e of local education { foreach p of local age { import excel "C:\Users\sample10_03_2023.xlsx", sheet("`G`i' `e', 'p'") firstrow *Do something in the spreadsheet gen education_age="`G`i' `e', 'p'" keep education_age *export the data to a new spreadsheet export excel using "C:\Users\sample10_04_2023.xlsx", sheet("`G`i' `e', 'p'") sheetmodify firstrow(variables) nolabel clear all } } }
Comment