st: Stat Transfer

[Simon Moore <simoncmoore@gmail.com>]

Hello Statalist

I occasionally process data that arrives from various locations in excel 
files (n > 50) and that change from time to time. I use Stat Transfer 
(v10) to convert these files to Stata (I am on v13MP) by including the 
following line in a .do file:

cd "`dir'"
stcmd *.xlsx *.dta /y

I have found that this does create .dta files but drops every 16th line
of data. Just wondering if anyone else here had encountered this and if
this is something to do with Stat Transfer, Stata of excel? It is a bit
of a worry.

My solution is to, rather clumsily, import the files into Stata and
save as .dta files:

	cd "`dir'"
	fs *.xlsx *.xls
	foreach f in `r(files)' {
		import excel "`dir'\`f'", firstrow
			loc id = substr("`f'",1, strpos("`f'",".")-1)
			save "`dir'\`id'.dta", replace
		clear
		}

dir is a local macro pointing to the location of the excel files.

fs is (I think) a user (Nicholas Cox) written package, but I don't
remember installing it. So maybe it comes with Stata these days?

One thing I notice is that this route has a tendency to include "empty"
variables. By this I mean columns in excel that contain no data and are
imported as variables with missing values. This is not an issue for me,
but is there a simple means of restricting the import to excel columns
that only contain data? For me I cannot specify a range as the number of 
columns in the sheet and names of variables differs between excel sheets 
(for example some will contain date of birth, some age and others both).

Regards
Simon Moore


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/