Appending multiple excel files

[Dario Maimone Ansaldo Patti]

Dear All,

I need to append together more than 200 excel files. All of them have the same structure: the first 5 rows contain a description of each file and the number of columns are equal. However the number of rows is different and at the end of each dataset there are two rows with a further description of the data. An example is below:

While I can use cellrange to skip the first five rows, I am wondering if there is any chance to ask Stata to import only data, skipping the last to rows at the end (notice that the last two rows after the data have the same content).

Thanks in advance for your help.

Dario

EDIT: It might be better to add the data rather than pics:

 
Albania

FDI stock abroad, by geographical destination













(Millions of US dollars)













Reporting Country
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022

Argentina
-
-
-
-
-
-
-
-
-
-

Australia
-
-
-
-
-
-
-
-
-
-

Austria
- 0
 0
 0
 0
 0
 0
- 5
 0
 0
-

Azerbaijan
 0
 0
 0
 0
 0
 0
 0
 0
 0
-

Bangladesh
-
-
-
-
-
-
-
-
-
-

Barbados
-
-
-
-
-
-
-
-
-
-

Belarus
-
-
-
-
-
-
-
 0
-
 0

Belgium
-
-
- 3
-
- 1
 1
 0
 0
 1
-

Benin
-
-
-
-
-
-
-
-
-
-

Bolivia, Plurinational State of
-
-
-
-
-
-
-
-
-
-

Bosnia and Herzegovina
 0
 0
 0
 0
 0
 2
 4
 5
 6
-

Botswana
-
-
-
-
-
-
-
-
-
-













Source: UNCTAD, FDI/MNE database.










Note: Data are based on information reported by the economies listed above.

 
Algeria

FDI stock abroad, by geographical destination













(Millions of US dollars)













Reporting Country
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022

Argentina
-
-
 0
 0
 0
 0
 0
 1
 1
-

Australia
 0
 0
 0
 0
 0
 0
 0
 0
 0
-

Austria
 0
 0
 1
 1
 1
 0
 0
 0
 0
-

Azerbaijan
-
-
-
-
 0
 0
 0
 0
 1
-

Bahrain
-
-
-
-
-
-
-
- 0
- 0
- 0

Bangladesh
-
-
-
- 0
 0
 0
 0
 0
 0
-

Barbados
-
-
-
-
-
-
-
-
-
-

Belarus
-
-
-
-
-
-
 0
-
-
-

Belgium
 0
-
 0
 2
 4
- 23
 0
 0
 0
-

Benin
-
-
-
-
-
-
-
-
-
-

Bolivia, Plurinational State of
-
-
-
-
-
-
-
-
-
-

Bosnia and Herzegovina
 0
 0
 0
 0
 0
 0
 0
 0
 0
-

Botswana
-
-
-
-
-
-
-
-
-
-

Brazil
- 1
- 0
- 0
- 0
-
- 0
 0
 0
 0
-

Bulgaria
 1
 1
-
 0
- 0
- 1
- 1
- 1
- 1
- 1

Burkina Faso
-
-
-
-
-
 12
-
-
-
-

Cabo Verde
-
-
-
-
-
-
-
-
-
-

Cambodia
-
-
-
-
-
-
-
-
-
-

Canada
-
-
-
-
-
-
-
-
-
-

Chile
-
-
-
-
-
-
-
-
-
-

China
 12
 8
 9
 8
 2
 3
 7
 8
 9
-

Costa Rica
-
-
-
-
-
-
-
-
-
-













Source: UNCTAD, FDI/MNE database.










Note: Data are based on information reported by the economies listed above.

[Joseph Coveney]

While I can use cellrange to skip the first five rows, I am wondering if there is any chance to ask Stata to import only data, skipping the last to rows at the end

You can use the describe option of import excel, which will leave the worksheet range in a returned local macro in lieu of actually importing the worksheet. With a little string manipulation,you can modify the contents of that returned macro to omit the last two rows of the range, and then feed the truncated range to a cellrange() option in a second pass import excel command that actually imports the worksheet.

But it might be easier just to import the worksheet (skipping the first five rows as you mention) and then adding a

drop in -2/l

step to the loop just before appending to the previous worksheet.

Or you can append all the worksheets including their last two rows, and then at the end

drop if strpos(<varname>, "Source") == 1 | strpos(<varname>, "Note:") == 1

to the concatenated worksheets.

I think that there's a user-written command that automates appending multiple Excel worksheets on SSC. I can't remember its name, but perhaps it has a feature that allows automating the kind of range selection that you want to do.

[Dario Maimone Ansaldo Patti]

Joseph Coveney Thanks for your suggestion. Indeed, there is a user-written command, xls2dta, to import excel file easily. Unfortunately, it does not have an option to tailor the selection of data. I thing using drop if could be much easier. Thanks again.

[Girish Venkataraman]

I had a similar issue and adapted code from this forum (sorry I should have remembered who I stole it from) to import multiple excel files with similar structure but 4 sheets and 5 sample rows (to delete). I have Stata 18 and use frames.

The main/master sheet is called pathology and 3 others called ngs, clinical and transplant. Each child sheet has same 5 repeating columns for patient identifiers which I delete down here. I just dont have a bottom repeating row like yours. You might be able to adapt this code after reading through the logic and including code from #2. This also uses one ssc program dirtools. I can pass the excel file to you if you are keen to see how it works. I am sure there is a more elegant way to do this but this works for me.

// Generate 4 temporary files to hold each of the sheets
*--------------------------------------------------------------

clear
frames reset


tempfile master                // pathology data sheet--all identifier sheet 
save "`master'" , emptyok

tempfile ngs                // ngs datasheet
tempfile clinical            // clinical datasheet
tempfile transplant            // transplant datasheet
save "`ngs'" , emptyok
save "`clinical'" , emptyok
save "`transplant'", emptyok

// Separately stack data from all centers within one sheetclass but all 3 sheets are separate still separate (long merge data for all centers)
*---------------------------------------------------------------------------------------------------------------------------------------------

filelist , pattern("*v?.xlsm")    // Will pick all needed files based on this pattern. Needs dirtools and filelist from SSC for this
local N_files = c(N)

    forvalues i = 3/`N_files' {    // Change this later for the real project
        local filename = c(pwd) + c(dirsep) + filename[`i']                               // Creates loop to go through all the files
    
        preserve
        clear
            import excel using "`filename'" , sheet("pathology") case(lower) firstrow        // First row is variable names
                drop if missing(digitcentercodecenter)
                drop if ustrregexm(studyserialnostudyid, "ample")                             // Drop all the first 5 rows of sample data
        append using "`master'"
        save "`master'" , replace
        restore    

        preserve
        clear
            import excel using "`filename'" , sheet("ngs") case(lower) firstrow        // First row is variable names
                drop if ustrregexm(studyserialnostudyid, "ample")                            // Drop all the first 5 rows of sample data
                drop if missing(diagnosisdatedodx)

        append using "`ngs'", force
        save "`ngs'" , replace
        restore    
        
        preserve
        clear
            import excel using "`filename'" , sheet("clinical") case(lower) firstrow
                drop if ustrregexm(studyserialnostudyid, "ample")                // Drop all the first 5 rows of sample data
                drop if missing(diagnosisdatedodx)

        append using "`clinical'"
        save "`clinical'" , replace
        restore        
        
        preserve
        clear
            import excel using "`filename'" , sheet("transplant") case(lower) firstrow
                drop if ustrregexm(studyserialnostudyid, "ample")                // Drop all the first 5 rows of sample data
                drop if missing(diagnosisdatedodx)

        append using "`transplant'"
        save "`transplant'" , replace
        restore    
    }



//  Laterally merge in all three datasheets into a wide form (wide merge path+ngs+clinical)-> One row per patient
*----------------------------------------------------------------------------------------------------------------
clear
use "`master'"
frame copy default master

use "`ngs'"
frame copy default ngs

use "`clinical'"
frame copy default clinical

use "`transplant'"
frame copy default transplant

// Link ngs and clinical into the master pathology frame
*--------------------------------------------------------
frame change master
frlink 1:1 pathologyaccessionaccn, frame(ngs)                // Create link using pathology accession number
frlink 1:1 pathologyaccessionaccn, frame(clinical)            // Create link using pathology accession number
frlink 1:1 pathologyaccessionaccn, frame(transplant)        // Create link using pathology accession number

// Import both into pathology frame
*----------------------------------
qui frget dateofngsdongs-cnagainscnagain, from(ngs)                        // Get all ngs and clinical -> Pathology Master sheet
qui frget priormdstherapyprmdstx-commentsclinicalcommclin, from(clinical)
qui frget pretransplantresponse-commentstransplantcommtsp, from(transplant)
drop ngs clinical transplant

// Remove all temporary frames
*-----------------------------
erase "`clinical'" 
erase "`ngs'" 
erase "`transplant'" 
erase "`master'"