A simple script to export data to Jira tabular format from Datagrip

datagrip-jira-export.md

Jira Export for Datagrip

I love working with Datagrip and I try to use and learn new functionalities whenever is possible. Plugins and Live Templates made my life much easier (especially with repetitive tasks). One annoying task I was not able to automate was to convert the result of a query to the Jira format (the one with | and ||), but like some bumper stickers say be the change you want to see: so I wrote a script for that.

A new export format

I am not an expert here, but many things happening in Datagrip can be controlled via Groovy or Closure scripts, so I had just to see if there was already an export script somewhere and try to modify it.

Luckily there are already multiple export formats as groovy scripts and it is quite easy to modify them. I went with the CSV-Groovy.csv.groovy one, I copied it and started modify it:

The script (below) is quite simple and works quite well. The only thing I was not able to achieve was to export the row headers having transposed data.

You can also download my script and copy it into the script folder. Where is the script folder? Just click on "Go to Scripts Directory" on the export menu and you are there.

Once you have the script in your folder, just select the export format, select on the data and copy them. Your data are now in the Jira tabular format.

An sample export

A stupid query:

select 1 a_number, 'a' a_letter
union all
select 2 a_number, 'b' a_letter
union all
select 3 a_number, 'c' a_letter
union all
select 4 a_number, 'd' a_letter
;

My export:

||A_NUMBER||A_LETTER||
|1|a|
|2|b|
|3|c|
|4|d|

And the transposed export:

|1|2|3|4|
|a|b|c|d|

Final words

I hope this script can serve you well like it served me in the past couple of years. Feel free to ping me for any feedback.

export-formats.png

Jira.groovy

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

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

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

if (!TRANSPOSED) {
  OUT.append(HEADER_SEPARATOR)
  COLUMNS.each { col ->
                        OUT.append(col.name())
                        .append(HEADER_SEPARATOR)
               }
               OUT.append(NEWLINE)
  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 }) }
}