sexta-feira, 25 de março de 2011

Generating CSV files using sqlcmd and groovy

Just sharing a simple groovy script I wrote for generating a CSV file from the database.
It depends on SQLServer's sqlcmd command line utility.
It takes 4 input parameters - needed to open a database connection, scans the current directory for .sql files then executes each of them using sqlcmd generating a .temp file. The temp file is then processed - the first and last 2 lines are removed - and renamed to a .csv file.

def username = args[0]
def password = args[1]
def host = args[2]
def database = args[3]
def dir = './'

def ant = new AntBuilder()
def p = ~/.*\.sql/
new File( dir ).eachFileMatch(p) { f ->

    def sqlFile = f.name
    def tempCsvFile = sqlFile.replaceAll(/.sql/,'')

    def cmd = "sqlcmd -S $host -U $username -P $password -d ${database} -i ${sqlFile} -W  -o ${tempCsvFile}.temp -s ;"
    def process = cmd.execute()

    ant.move(file: "${tempCsvFile}.temp", tofile:"${tempCsvFile}.csv", overwrite: true ) {
  headfilter(lines:"-1", skip: "2")
  tailfilter(lines: "-1", skip: "2" )

This could probably be achieved in many other ways.
But it worked like a charm for me!!