Transferring entire content of log file into excel

[Kerrie Ho]

Hi Statalisters,

I am trying to find a way to transfer the entire content from a log file into excel in such a way that it retains the stata formatting. When I try copy and pasting the entire log file the formatting gets messed up and I end up with one cell per row of data where there should be several columns.

The only way I've been able to retain the formatting is if I paste each table across separately by doing this:

highlight output table > right click - copy table > pasting in excel

It doesn't work when I try and do more than one table at a time.

All the things I've tried only seem to work for individual tables. I've tried the export and putexcel functions and also the excel wizard.

Do you have any other suggestions? I've got hundreds of tables within the log file and it's a big pain to do each one separately.

Thanks in advance!
Kerrie

[Joseph Coveney]

What Stata command is giving you all of these tables in your log file? If it is an estimation command it will return its results in e-return scalars and matrices. Otherwise, it is likely to return its results in r-return scalars and matrices. Either way, you should be able to take whatever it returns and place them into a Microsoft Excel workbook without having to copy and paste tables from the log file. I believe that there are user-written commands that have automated at least some of these kinds of tasks for exporting results to various external documents and worksheets.

[eric_a_booth]

I'd advise taking a close look at -tabout- , -fre-, or -summout- (all from SSC , so use -ssc install- to install them or -findit- to find them, and there are many other options if you look at -findit export table- ). You can loop the results from these programs to produce hundreds of tables in a single spreadsheet or across many spreadsheets (often you will use 'modify' or 'append' options to put multiple tables in the same sheet).

-putexcel- does allow you to place multiple tables with formatting into the same excel worksheet without copy/pasting the tables. If it's failing, or failing to act as you intended, show us the code you're attempting so that we can help provide some guidance. Again, this does what your describing and you could loop this to produce hundreds of tables (it uses the r return scalars/matricies that Joseph describes and allows you to retain any preset formatting/templates) . This example does what you describe:

sysuse auto, clear
sum price
return li
cap rm `"test.xlsx"'

**example 1: multiple tables in same sheet:
putexcel A3=rscalars using `"test.xlsx"', sheet("test") modify keepcellf
**add title
putexcel A1=("Here is price data") using  `"test.xlsx"', sheet("test") modify keepcellf
**add tables
sum mpg
putexcel C3=rscalars using  `"test.xlsx"', sheet("test") modify keepcellf
putexcel C1=("Here is MPG data") using  `"test.xlsx"', sheet("test") modify keepcellf
tab rep78, matcell(cell) matrow(rows)
putexcel A12=("Repairs Summary") B12=("#") using  `"test.xlsx"', sheet("test") modify
putexcel A13=matrix(rows) B13=matrix(cell) using  `"test.xlsx"', sheet("test") modify


**ex 2:  loop & new sheet for each var, but you can do them all in the same sheet too
ds make, not
foreach j in `r(varlist)' {
    di `"`j'"'
    su `j'
    putexcel A3=rscalars using `"test.xlsx"', sheet("S_`j'") modify keepcellf
    putexcel A1=(`" Example for `j' "') using  `"test.xlsx"', sheet("S_`j'") modify keepcellf
    }
    
    
di `"{browse  `"test.xlsx"': Click to open  `"test.xlsx"' }"'

HTH,
Eric

[Kerrie Ho]

Hi Jospeh and Eric,

Thanks for your advice.

I managed to run the analyses and export the output tables using the following loop:

foreach x of varlist studying employed NEET {
svy: logistic `x' i.gender i.age_group2 i.indig_status i.disability_LThealth ib1.k10categories
putexcel A3=matrix(r(table), names) using YPFCtestreg1, sheet("`x'") modify
margins gender age_group2 indig_status disability_LThealth k10categories
putexcel A17=matrix(r(table), names) using YPFCtestreg1, sheet("`x'") modify
}

It doesn't look exactly the same as the stata output but I can still figure out what's what. I would prefer if it looked exactly like the stata output. Any ideas?


Also, the levels of the variables (e.g., gender was 1=male 2=female) are showing like this:

1b.gender

2.gender

Is there a way I can get it to display male and female rather than 1b and 2?

Many thanks for your help!