Rapid import and infill of semi-structured data

import-infill.do

global data "${wd}/ppol_528_GuzmanLidia/data/Data_exp"
local data : dir "${data}/xlsx/" files *
clear
  tempfile allData
  save `allData' , emptyok
cap prog drop infill
prog def infill
  syntax anything
 
  qui foreach var of varlist `anything' {
    
    local theValue = ""
    forv i = 1/`c(N)' {
        
        local nextValue = `var'[`i']
        if ("`nextValue'" == "") | ("`nextValue'" == ".") {
            cap replace `var' = "`theValue'" in `i'
            cap replace `var' = `theValue' in `i'
        }
        else {
            local theValue = "`nextValue'"
        }
    }
  }
end
foreach file in `data' {
        
    qui import excel using "${data}/xlsx/`file'" , clear  cellrange(A10) first    
    qui drop if Chapter == "Total"    
    set trace off   
    qui infill Year Month Province  
    qui destring * , replace
    
    di "`file' -- `c(N)'"
    
    drop G-K    
    qui compress    
    qui append using `allData'  
    qui save `allData' , replace
    
    }