How to append CSV file with PostgreSQL query export in Datagrip?

Question

I want to append an existing CSV file (Template for Google Ads Offline Conversion Tracking) with the results of a query from PostgreSQL using Datagrip. Execute to file works just fine to export the query results as a CSV file. But obviously that just creates a new file with only the results of the query. I was thinking to load the existing template in the groovy script that's there in datagrip, and then add its content to the top of the created file, but I can't make any headway on how to do this.

I found (maybe?) how to load the file, but I have no clue how to actually use this and put it at the top of the file so the results of the query can be added below it.

fh = new File("C:/1.csv")
def csv_content = fh.getText('utf-8')

That's all I got for loading the file with no clue on how to use this going forward.

Alternatively I considered to just manually add the info of the template by hand to the script, so it doesn't have to load another file, but just takes that info and then adds the query results afterwards. If you could give me a way on how to directly add rows like these would be very helpful too.

The groovy script file that is included in Datagrip is as follows:

/*
 * Available context bindings:
 *   COLUMNS     List<DataColumn>
 *   ROWS        Iterable<DataRow>
 *   OUT         { append() }
 *   FORMATTER   { format(row, col); formatValue(Object, col); getTypeName(Object, col); isStringLiteral(Object, col); }
 *   TRANSPOSED  Boolean
 * plus ALL_COLUMNS, TABLE, DIALECT
 *
 * where:
 *   DataRow     { rowNumber(); first(); last(); data(): List<Object>; value(column): Object }
 *   DataColumn  { columnNumber(), name() }
 */

SEPARATOR = ","
QUOTE     = "\""
NEWLINE   = System.getProperty("line.separator")

def printRow = { values, valueToString ->
  values.eachWithIndex { value, idx ->
    def str = valueToString(value)
    def q = str.contains(SEPARATOR) || str.contains(QUOTE) || str.contains(NEWLINE)
    OUT.append(q ? QUOTE : "")
      .append(str.replace(QUOTE, QUOTE + QUOTE))
      .append(q ? QUOTE : "")
      .append(idx != values.size() - 1 ? SEPARATOR : NEWLINE)
  }
}

if (!TRANSPOSED) {
  ROWS.each { row -> printRow(COLUMNS, { FORMATTER.format(row, it) }) }
}
else {
  def values = COLUMNS.collect { new ArrayList<String>() }
  ROWS.each { row -> COLUMNS.eachWithIndex { col, i -> values[i].add(FORMATTER.format(row, col)) } }
  values.each { printRow(it, { it }) }
}

Answer 1

You need to pass content of your file to OUT variable like this:

fh = new File("C:/1.csv")
def csv_content = fh.getText('utf-8')
OUT.append(csv_content).append("\n")

Everything that is passed to OUT.append will be written to new file.

Just add it to the beginning of the script:

/*
 * Available context bindings:
 *   COLUMNS     List<DataColumn>
 *   ROWS        Iterable<DataRow>
 *   OUT         { append() }
 *   FORMATTER   { format(row, col); formatValue(Object, col); getTypeName(Object, col); isStringLiteral(Object, col); }
 *   TRANSPOSED  Boolean
 * plus ALL_COLUMNS, TABLE, DIALECT
 *
 * where:
 *   DataRow     { rowNumber(); first(); last(); data(): List<Object>; value(column): Object }
 *   DataColumn  { columnNumber(), name() }
 */

SEPARATOR = ","
QUOTE     = "\""
NEWLINE   = System.getProperty("line.separator")

fh = new File("C:/1.csv")
def csv_content = fh.getText('utf-8')
OUT.append(csv_content).append("\n")

def printRow = { values, valueToString ->
  values.eachWithIndex { value, idx ->
    def str = valueToString(value)
    def q = str.contains(SEPARATOR) || str.contains(QUOTE) || str.contains(NEWLINE)
    OUT.append(q ? QUOTE : "")
      .append(str.replace(QUOTE, QUOTE + QUOTE))
      .append(q ? QUOTE : "")
      .append(idx != values.size() - 1 ? SEPARATOR : NEWLINE)
  }
}

if (!TRANSPOSED) {
  ROWS.each { row -> printRow(COLUMNS, { FORMATTER.format(row, it) }) }
}
else {
  def values = COLUMNS.collect { new ArrayList<String>() }
  ROWS.each { row -> COLUMNS.eachWithIndex { col, i -> values[i].add(FORMATTER.format(row, col)) } }
  values.each { printRow(it, { it }) }
}

Note that you may copy paste CSV-Groovy.csv.groovy to the same directory (e.g. MY-CSV.csv.groovy) and modify new file. This new extractor will be added to combobox with all extractors