Microsoft has a tool that should be in every DBA’s toolbox. Even if you aren’t running MSSQL on a Windows server it’s very handy to run on your windows desktop to get info out of flat files very quickly.
This can be used as a great alternative to DTS because you can insert the records into the DB that you want instead of inserting all records and then filtering.
This is a utility that allows you to run a SQL query against a directory of flat files in various formats as if they were in a database. Most of the functions you can think of which are in SQL and are not in many other command line tools for parseing flat files are availabel such as Distinct, group by, order by, count, max, min, avg, etc. There are 3 main parts to it: the input format, the query and the output format. There are many different input formats, I most commonly use IISLog directories as an input, and it automically knows how to recognize it. One awesome thing is that it keeps track of which files it’s been through so it can be setup to only process deltas.
The Queries use a limitied set of SQL syntax.
The outputs can display on the screen, another file, or direct into a database.
here’s an example of a logparser query that will give you a count of how many hits occured on a file in a specified timeframe from all of the IISLogs:
Logparser -i:IISW3C "Select count(*) from f:\IISlogs\*.log where cs-uri-stem like '%%PAGEYOUARELOOKINGFOR'and TO_TIME(time) BETWEEN TIMESTAMP('15:45:48', 'hh:mm:ss') AND TIMESTAMP('21:03:48', 'hh:mm:ss')"
