JOINGING MULTIPLE SHEETS OF DATA FROM excel

[saptorshee chakraborty]

Dear All,

I have Survey data for six years each year containing 26 variables and more than 400 thousand entries for each variable. Is it possible to join the data year by year into a single STATA dat file from the EXCEL file. The data for each year in the Excel file is on a different sheet.

Any help will be appreciated.


Regards

[Rich Goldstein]

your question is not completely clear but here is a guess: import each sheet by itself and make a file and then append the files; see

h append

[George Ford]

foreach sheet in year2010 year2011 year2012 year2013 year2014 {
    import excel filename.xlsx, sheet(`sheet') firstrow clear
    save `sheet', replace
}
clear
set obs 0
foreach file in  year2010 year2011 year2012 year2013 year2014 {
    append using `file'
} 
save datafile, replace

[Nick Cox]

I would do something like george Ford's helpful code. But if year was not part of the data on each worksheet, something more like this could be a good idea.

forval year = 2010/2014 { 
     import excel filename.xlsx, sheet(year`year'), firstrow clear 
     gen year = `year' 
     save `year'
}

append using 2013 2012 2011 2010

Note that when the loop is done, the last year's data is in memory, so you just need to append the other years' data.

Naturally any code here needs to be adapted to the names and years you have, which we can't see.

[George Ford]

I did not realize you could include multiple files in append. Neat.

[George Ford]

And note Nick added the g = `year' variable, which is important if it's not the original data (but you'd figured that out pretty quick).

[saptorshee chakraborty]

This works perfectly. Thank you very much indeed.

[Nick Cox]

 
  import excel filename.xlsx, sheet(year`year'), firstrow clear

should have been

 
  import excel filename.xlsx, sheet(year`year') firstrow clear