[URL="http://datawatch.com/_products/monarch_pro.php"]Monarch Pro V10[/URL] will fit the bill nicely for your requirements.
It runs on both XP and Vista, and with Datawatch's status with Microsoft is bound to support later OS versions as well.
Extensive [URL="http://********************/tips/monarch-batch-file-generator"]command line parameters[/URL] offer basic automation, and a COM interface permits more [URL="http://********************/tips/monarch-programming-class"]advanced control[/URL] over automation.
Throw in [URL="http://********************/tips/new-year-new-features"]support for Office 2007 file formats[/URL] and you're good to go.
I've been using Monarch for seven years now, and I'm sure that once you discover and take advantage of Monarch's many benefits, you'll wonder how you got along without it.
Monarch is almost completely automatable via command line or COM interfaces, as Kruncher says. There is a hefty difference in price between Monarch and DataPump (about 1:10 as far as I recall) and it might be helpful to bear in mind the differences.
Monarch is licenced for installation on one machine, for use by one user. If you need multiple users to control Monarch, then either a network licence of Monarch or a single licence of DataPump (which offers a remote admin interface as well as a web interface) is the solution.
A batch file running Monarch leaves no traces. If you need to keep a record to prove that the job ran on the 1st of the month, then you'll need DataPump.
3. Wildcard inputs
If your input file is called 20090601.csv this month, and 20090701.csv next month, your Monarch projects won't handle it. You can get around this with some scripting to rename the input files, but if you want an easy and robust way of managing it, use DataPump.
DataPump can lurk and wait for an input file to arrive in a specific location, by setting a process to be run as "monitored". You can also schedule the process using the Windows Scheduler. Very useful if you aren't in the office on the day the process needs to run!
In DataPump, exports can be called "Summary-Sales-20090701.xls" - you can also datestamp them, or give them a counter ID. Exports can also be distributed by FTP or email.
There's a complete .NET scripting interface for DataPump. The Pro version also integrates with SQL Server Development Tools so that you can include DataPump operations when developing routines in Visual Studio.
If your report layout changes, a Monarch model would deliver wrong or empty data, and in a batch file there's no way of knowing that an error has occured. DataPump lets you stop a process, and report an error, if the report layout changes by including a verification step that requires n rows in the filtered table to be present, so you can build a filter which tests for expected behaviour. It's not foolproof, but you can configure the error handling to email you an alert.
I'm sure others will add to this...
The one thing that concerns me is your comment:
If your input file is called 20090601.csv this month, and 20090701.csv next month, your Monarch projects won't handle it....
What is it about the file that Monarch can't handle....I think this is almost exactly what will happen. How have you gotten around this issue?
A Monarch project defines the input file(s) explicitly (e.g. c:\input\salesreport.prn, not as c:\input\*.prn).
There are hacks - you could have a batch job that perhaps reads a directory listing (using dir > listing.prn, for example) and if a filename matches your criteria, outputs a fixed width text summary in Monarch containing a calculated field of the form:
"C:\Program Files\Monarch\Program\Monarch.exe" ...
followed by the name of the relevant model, file and export desired.
It's not for the faint-hearted, but it's workable.
This line will use the default csv data file as was setup in the project file:
But this line will replace the default data source with C:\new_csv_data.csv
"C:\program files\monarch\program\monarch" /prj:"C:\Desktop\old_csv_project.xprj" /exp:"C:\Desktop\newdata.xls" /datasource:"C:\new_csv_data.csv" /expfileopt:overwrite
In my testing I used olddata.xls and newdata.xls to ensure that it was operating as expected.
You'll still need the name of the file, but a %i (or is it %%i ... I never remember that exactly... Hey Nick, Mr. Batch File!) substitution should help with that.