Looking for an SQL scripting language to manipulate DBF files.

Question

I have a large collection of DBF files (about 1100 of them) that I need to analyze for a client. Each file contains a single table. I need to perform a query on each table, copy the results into one large results table (which will hold the results from all files), and then move on to the next DBF file. At the end I need to save the results table in a format I can manipulate later. Does anyone know of a scripting language that can make this easy for me?

There are a few caveats: 1.) I need something that works in Vista (something that runs in DOS, python or GNU Octave is okay too). 2.) I'm not a database administrator, but I do have fairly good programming skills. 3.) I have only a basic working knowledge of SQL. I can write the queries, my problem is opening the DBF files and saving the results. 4.) I've actually accomplished this using MS Access, but it's a messy solution. So I'm looking for something that doesn't use Access.

I've been reading up on various scripting languages for SQL. Most of the sites I've seen get into things about servers, setting up relations, security and all those things. These issues are well beyond my understanding and aren't my concern. I just want to query these files, get my results, and get out. Is there something out there that's easily accessible for beginners, yet significantly powerful?

Any help would be much appreciated.

Answer 1

I would do this with SSIS. Looping and Data transformations are fairly easy in SSIS.

Answer 2

My first choice would be Visual FoxPro. It handles .dbf files natively, has an interactive environment and provides SQL SELECT capability. The SELECT statement has an INTO clause that sends query results to another table. Some kinds of MSDN subscription include FoxPro on the DVDs and make it available for download from MSDN.

dBASE is available too.

If necessary, the .dbf file structure is easy to manipulate with code. In the past I've had to write code to modify .dbf files in C and Delphi. It was never more than an afternoon's work. A Google Code Search will probably yield .dbf-related code for any major programming language. The .dbf file format and related file formats are documented on MSDN.