In my job we often have spreadsheets of data that we want to quickly turn into SQL scripts. (Yes, I know there are plenty of ways of importing spreadsheets into databases, but our DBAs want SQL scripts!) This is fairly simple task and could be done just by manually editing the CSV file and doing a few regular expression. However, one little annoyance is that you need to quote some of the columns in order to create a valid SQL script. Writing a Java program to do this would be quite time consuming, but with Groovy it is very quick. Here is the script I wrote:
File inputFile = new File("C:storestable.csv")
File outputFile = new File("C:storestable_quoted.csv")
// starts at 1
List columnsToQuote = [2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,23,24,25,27]
outputFile.delete()
inputFile.eachLine { line ->
String[] columns = line.split(",")
StringBuffer newLine = new StringBuffer()
// iterate over the columns and quote the ones that need it
columns.eachWithIndex{ column, i ->
if (columnsToQuote.contains(i+1)) {
newLine.append("'").append(column).append("'")
}
else {
newLine.append(column)
}
if (i != columns.length-1) {
newLine.append(",")
}
}
newLine.append("n")
// write the line into the output file
outputFile.append(newLine.toString())
}
You can see how much easier this is with Groovy – the files can be defined in a single line of code and you’re not forced to deal with lots of possible exceptions when doing the file IO. In addition, Groovy provides a very useful iteration closure – eachWithIndex – that allows you to iterate over a collection and be given both the object and its index on each iteration.