export table to excel using 'tabout' AFTER opening excel sheet with 'putexcel'

[Becca Merrill]

Hi all, I'm new to posting on Statalist but have gleaned from your collective knowledge and cordial wisdom for a few years. I am also versed in how to pronounce and write Stata

I have a problem for which I have not, even eventually, been able to work out. I want to export to the same excel workbook, in the same sheet using two different export commands, tabout and putexcel. I know that putexcel is precise in it's placement of data, meaning you have to tell putexcel precisely where to put data, so I always begin with putexcel. Then, I try to append a table from tabout to the same worksheet, but it does not append. I get no error. The putexcel command executes as expected, but the tabout results are not exported to excel. In my reading of tabout help files, I thought that it figures out where to put appended tables based on what's already there, so I thought this would work.

I have 4033 observations on a terminal server using Stata 15.1, so I unfortunately can't use the stataex command. 'enr_cats' and 'year' are both numeric. Here is a replication of my code: (I'm using courier new font so it looks more familiar to Stata eyes)

putexcel set "$output\enrollment3_table.xls", replace
putexcel A1=""
putexcel close

tabout enr_cats year using "$output\enrollment3_table.xls", c(freq) f(0) append clab(No._Schools)
tabout enr_cats year using "$output\enrollment3_table.xls", c(col) f(2) append clab(%_Schools)

I've tried with and without the 'putexcel close' command and get the same result. No export of tabout to excel and no error.

Any ideas? Is what I'm asking not possible?


Many thanks for years of help and possible new help,
Becca

[Becca Merrill]

Oh, and Stata's output says:

Table output written to: filename\enrollment3_table.xls

after both tabout commands

[William Lisowski]

Welcome to Statalist.

Here is a reproducible example of your code that anyone can run.

clear
set obs 100
set seed 42
generate year = runiformint(2011,2015)
generate enr_cats = runiformint(1,4)

erase "gnxl.xls"

putexcel set "gnxl.xls", replace
putexcel A1="hello, world!"
utexcel close

tabout enr_cats year using "gnxl.xls", c(freq) f(0) append clab(No._Schools)
tabout enr_cats year using "gnxl.xls", c(col) f(2) append clab(%_Schools)

I am assuming you are running the released version 2.0.8 of tabout rather than the beta version.

. which tabout
/Users/lisowskiw/Library/Application Support/Stata/ado/plus/t/tabout.ado
*! 2.0.8 Ian Watson 15mar2019
*! tabout version 3 (beta) available at: http://tabout.net.au

Your problem is that tabout does not write Excel datasets, it writes tab delimited text files, despite the xls extension you have given it.

I demonstrate that by commenting out the putexcel commands and running the code, and then using a text editor to open gnxl.xls, which shows me tab delimited text.

    year                    
enr_cats    2011    2012    2013    2014    2015    Total
    No. Schools    No. Schools    No. Schools    No. Schools    No. Schools    No. Schools
1    6    5    5    8    9    33
2    6    3    9    8    6    32
3    5    2    3    5    1    16
4    3    2    5    5    4    19
Total    20    12    22    26    20    100

    year                    
enr_cats    2011    2012    2013    2014    2015    Total
    % Schools    % Schools    % Schools    % Schools    % Schools    % Schools
1    30.00    41.67    22.73    30.77    45.00    33.00
2    30.00    25.00    40.91    30.77    30.00    32.00
3    25.00    16.67    13.64    19.23    5.00    16.00
4    15.00    16.67    22.73    19.23    20.00    19.00
Total    100.00    100.00    100.00    100.00    100.00    100.00

When I instead double-click on the file, Excel warns me of a format mismatch with possible corruption or malicious content, and then allows me choose to open the file, and in doing so, it recognizes that the file contains tab delimited text and imports it as best it can. So it looks as if gnxl.xls were an Excel file, but it is not.

When I uncomment the putexcel commands and run the code, the tabout command spews the most amazing garbage to my screen.

. tabout enr_cats year using "gnxl.xls", c(freq) f(0) append clab(No._Schools)

Table output written to: gnxl.xls

��.ࡱ.�................>...��  .............................����....����........       ...
...���������������������������������������������������������������������������������������������
> ����������������������������������������������������������������������������������������������
> ����������������������������������������������������������������������������������������������
> ����������������������������������������������������������������������������������������������
> �������������������������������������������������     .......� �.��......�...�.�.....�...\.p..
> ..L                                                                                           
>                  B...�.a.....=.....�.......................�.....�.....=........?�'8.......X.@
> .....�....."...........�.....�.....1...�...��.........A.r.i.a.l.1...�...��.........A.r.i.a.l
> .1...�...��.........A.r.i.a.l.1...�...��.........A.r.i.a.l.1...�...��.........A.r.i.a.l.�..
> .....�� .....@ @ ..� �.......�� .....@ @ ..� �.......�� .....@ @ ..� �.......�� .....@ @ ..� �
> .......�� .....@ @ ..� �.......�� .....@ @ ..� �.......�� .....@ @ ..� �.......�� .....@ @ ..�
>  �.......�� .....@ @ ..� �.......�� .....@ @ ..� �.......�� .....@ @ ..� �.......�� .....@ @ .
> .� �.......�� .....@ @ ..� �.......�� .....@ @ ..� �.......�� .....@ @ ..� �......... .....@ @
>  ..� �.....,.�� ..�..@ @ ..� �.....*.�� ..�..@ @ ..� �.....     .�� ..�..@ @ ..� �.....+.�� ..

and on like that for quite some distance, a clear indication that something is not right.

[Becca Merrill]

Ah! I see.

I checked putexcel's file compatibility and it looks like putexcel only works with excel file formats (.xls and .xlsx), so the two commands are incompatible to export to the same document.

Thank you, William for the entertaining and informative illustration!