Re: st: Stat Transfer

[Nick Cox <njcoxstata@gmail.com>]

I cannot help on line dropping.

Two notes on the side:

1. -fs- remains a user-written command from SSC, so I surmise you
installed it at some point.

2. As recently mentioned here, -dropmiss- (SJ) is best used in some form like

dropmiss, force

which is a relatively clean way to -drop- all variables that have all
observations missing. I am a bit fuzzy on the original motivation (it
goes back to 1997 at least) but the tendency of spreadsheet users to
leave blank columns that there were then read as variables by Stata
was something that has been fixable this way for a long while. I don't
know of an option to do this with -import excel-.


Nick
njcoxstata@gmail.com


On 8 March 2014 16:34, Simon Moore <simoncmoore@gmail.com> wrote:
> 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/
*
*   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/