How to open multiple DBF files in Stata or how to convert multpile DBF files using the stat/transfer command lop

[Alfonso Rodriguez]

Hey guys

Im trying to convert or to open from stata multiple dbf files (864 to be exact). I read the answer Nuno Soares elaborated for a similar case using 3000 excel files (http://www.stata.com/statalist/archi.../msg00895.html) and I was wondering if a similar approach could be used for DBF files. Wether it is to open them, do a simple processing of data and saving them, or converting all of them so they can be used later in another loop. Sorry to bother you.

Best regards
Alfonso

[Clyde Schechter]

StatTransfer is not a Stata command. It is a separate software package that translates nearly every kind of spreadsheet/database/statistical file into the nearest equivalent in any other such file format. If you do not have it, you can purchase online it from Circle Systems, Inc. It's a high quality product that I've been using for years. Their customer service, by the way, is excellent. And, to me, at least, the price feels quite reasonable considering how much capability the software has. (Full disclosurse: I have no financial or personal interest in the well-being of Circle Systems; there is no potential conflict of interest in these declarations.)

When installing StatTransfer, on your computer you will be given the option of installing some Stata ado files, originally written by Roger Newson, that enable you to invoke StatTransfer from within Stata. The -inputst- and -outputst- commands are the workhorses. If I recall, a little bit of user input is required to set up the underlying -stcmd.ado- file by specifying some directory or other, but it's not hard. If you already have StatTransfer running on your computer but your Stata installation does not have -stcmd.ado-, -inputst.ado-, and -outputst.ado-, you can get those as a package with -ssc install stcmd-. Again, some set up specifying a directory may be required to make it work properly.

But basically, once you've got it installed, it's pretty straightforward. You just create a list of the dbf files you want to work with, placing the names in a local macro. Then you loop over that macro to -inputst- them, do whatever modifications you want in Stata, and then you can append or merge them together if appropriate, or just save each one separately as a Stata data set, etc. The basic syntax to just convert them all to Stata files and save them goes like this:

local filenames whatever // PROBABLY CREATED WITH THE -":dir" MACRO EXTENDED FUNCTION

local filenames: subinstr local filenames ".dbf" "", all // GO TO FILENAMES WITHOUT EXTENSIONS

foreach f of local filenames {
    inputst `f'.dbf
    // DO DATA CLEANING, ETC.
    save `f', replace
}

Note, depending on what's in the filenames, you may need to enclose `f' within quotes in the -inputst- command.

Finally, a question for you: what does that final word in your post title, lop, stand for, mean, or refer to?

[Sergiy Radyakin]

Alfonso,

there is also an ODBC solution not requiring StatTransfer.

Clyde Schechter I guess 'lop' was meant to be 'loop'

Best, Sergiy