Wednesday, January 21st, 2009...9:42 am

Easily Creating Fixed Length Flat Files With Groovy

Jump to Comments

Here’s an (I think elegant) way to create flat files with Groovy.

I want this script to be easy to read by future developers. So if someone wants to know what the format of the file is they can see it just by opening the script.

Classes We’ll Use

Groovy:

  • SQL: We’ll use this library to access the database, it has some nice closure methods like eachRow which make iterating over results easy.
  • String: Groovy includes a string class that has some nice methods like padRight and padLeft, handy for making fixed length files.

Java:

  • File Writer: The easiest way to create files in java.

The Plan

Define our file in a map at the top of the file, it will be SQL Alias Name : Length.

Execute our SQL, for each row iterate through our file definition map and pad each value by the appropriate length.

The Code

Pretty self explanatory — a few neat groovy things we use in this script:

  • Triple quotes for a multiline string (our sql)
  • The format method to easily format a date (bye bye simple date format) for the file name
  • The elvis operator ?: makes the results null safe so we can call padRight.
  • The each method on Groovy’s Map that accepts a closure, we use that to iterate through our file definition.

[java]
import groovy.sql.Sql

import java.io.File;
import java.io.FileWriter;

def fileName = “FLATFILE” + String.format(‘%tY%

def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:midb01",
"xxx","xxx", //user,pass
"oracle.jdbc.driver.OracleDriver") //jdbc driver

FileWriter writer = new FileWriter(fileName);

//this map defines the field length based on alias id from the SQL
def fileDef = [COLUMN1:14,
COLUMN2:9,
COLUMN3:5,
COLUMN4:16]

def qry = """SELECT column1,column2,
column3,column4 FROM OurTable"""

def test = sql.eachRow(qry){row ->
fileDef.each{ k,v-> writer.append ((row[k] ?: ” “).padRight(v))}
writer.append “\n”
}
writer.close()

[/java]

1 Comment

  • Using File.withWriter would be better because it will close resources on exception.

    new File(filename).withWriter { w ->
    w

Leave a Reply