Looking for an SQL scripting language to manipulat

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.

Answer 3

I have written a python dbf module which has very rudimentary SQL support. However, even if the SQL is not up to your needs, it is easy to query the dbf files using python syntax.

Some examples:

import dbf

To create a results table and add records to it:

results = dbf.Table('results_table', 'name C(50); amount N(10, 4)')
record = results.append()
with record:
    record.name = 'something'
    record.amount = 99.928

# to open an existing table
table = dbf.Table('some_dbf_table').open()

# find all sales >= $10,000
records = table.pql("select * where sales >= 10000")

# find all transactions for customer names that start with Bob
records = table.pql("select * where customer.startswith('Bob')")

# nevermind thin sql veneer, just use python commands
records = table.find("sales >= 10000 and customer.startswith('Bob')")

# sum sales by customer
customer_sales = default_dict(int)  # if customer not already seen, will default to 0
for record in table:
    customer_sales[record.customer] += record.sales

# now add to results table and print them out
for name, total in sorted(customer_sales.items()):
    result_record = results.append()
    with result_record:
        result_record.name = name
        result_record.amount = total
    print "%s: %s" % (name, total)