DataGrip extractors

CSV-ToOneRow-Groovy.csv.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() }
 */

SEPARATOR = ","
QUOTE     = "'"

first = true
ROWS.each { row ->
  COLUMNS.each { column ->
    value = FORMATTER.format(row, column)
    def q = !value.isNumber()
    OUT.append(first ? "" : SEPARATOR)
       .append(q ? QUOTE : "")
       .append(value.replace(QUOTE, QUOTE + QUOTE))
       .append(q ? QUOTE : "")
    first = false
  }
}

JSON-Groovy-WithNumbersFix.json.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() }
 */

import static com.intellij.openapi.util.text.StringUtil.escapeStringCharacters as escapeStr

NEWLINE = System.getProperty("line.separator")
INDENT = "  "

def printJSON(level, col, o) {
  switch (o) {
    case null: OUT.append("null"); break
    case Number: OUT.append(FORMATTER.formatValue(o, col)); break
    case Boolean: OUT.append("$o"); break
    case String: OUT.append("\"${escapeStr(o)}\""); break
    case Tuple: printJSON(level, o[0], o[1]); break
    case Map:
      OUT.append("{")
      o.entrySet().eachWithIndex { entry, i ->
        OUT.append("${i > 0 ? "," : ""}$NEWLINE${INDENT * (level + 1)}")
        OUT.append("\"${escapeStr(entry.getKey().toString())}\"")
        OUT.append(": ")
        printJSON(level + 1, null, entry.getValue())
      }
      OUT.append("$NEWLINE${INDENT * level}}")
      break
    case Object[]:
    case Iterable:
      OUT.append("[")
      def plain = true
      o.eachWithIndex { item, i ->
        plain = item == null || item instanceof Number || item instanceof Boolean || item instanceof String
        if (plain) OUT.append(i > 0 ? ", " : "")
        else OUT.append("${i > 0 ? "," : ""}$NEWLINE${INDENT * (level + 1)}")
        printJSON(level + 1, null, item)
      }
      if (plain) OUT.append("]") else OUT.append("$NEWLINE${INDENT * level}]")
      break
    default:
      if (col != null) printJSON(level, null, FORMATTER.formatValue(o, col))
      else OUT.append("$o")
      break
  }
}

printJSON(0, null, ROWS.transform { row ->
  def map = new LinkedHashMap<String, String>()
  COLUMNS.each { col ->
    def val = row.value(col)
    map.put(col.name(), new Tuple(col, val))
  }
  map
})

SQL-Inserts-MultirowSynthax.sql.groovy

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

begin = true

def record(columns, dataRow) {

    if (begin) {
        OUT.append("INSERT INTO ")
        if (TABLE == null) OUT.append("MY_TABLE")
        else OUT.append(TABLE.getParent().getName()).append(".").append(TABLE.getName())
        OUT.append(" (")

        columns.eachWithIndex { column, idx ->
            OUT.append(column.name()).append(idx != columns.size() - 1 ? SEP : "")
        }

        OUT.append(")").append(NEWLINE)
        OUT.append("VALUES").append("  (")
        begin = false
    }
    else {
        OUT.append(",").append(NEWLINE)
        OUT.append("        (")
    }

    columns.eachWithIndex { column, idx ->
        def skipQuote = dataRow.value(column).toString().isNumber() || dataRow.value(column) == null
        def stringValue = FORMATTER.format(dataRow, column)
        if (DIALECT.getFamilyId().isMysql()) stringValue = stringValue.replace("\\", "\\\\")
        OUT.append(skipQuote ? "": QUOTE).append(stringValue.replace(QUOTE, QUOTE + QUOTE))
           .append(skipQuote ? "": QUOTE).append(idx != columns.size() - 1 ? SEP : "")
    }
    OUT.append(")")
}

ROWS.each { row -> record(COLUMNS, row) }

Hello, thanks a lot for the scripts, but the second script does not work out of the box. I had to fix the quotes. Just saying, if someone else has the same problem.

There are a bit mangled double quotes in the second script. Just replace them with usual ones. Also don't forget to replace the single one in the line 2.

Or grab it here: https://gist.github.com/leo-from-spb/ec438f1fe9995b910ad816aabe47dd4f

Fixed, thanks!