Read CSV Scala

Read a CSV.scala

#!/bin/sh
exec scala "$0" "$@"
!#

/*
 This script creates a SQL output with a format:

 INSERT INTO table (columns, ...)
 VALUES
  (values, ...),
  (values, ...),
    . . . . .
  (values, ...);
  
  It is built for a very very very specific case, but it should e good for other cases
 */

object Main {

  def main(args: Array[String]): Unit = {

    val startModelId = 775
    val startPriceId = 471

    val rows = General.readLines("input.csv", startModelId, startPriceId)

    // UP
    val startOfModelQuery =
      """
        | -- UP
        |
        |INSERT INTO risk_object_models(
        |	id, risk_object_brand_id, model_name, status, created_at, expiration_date, created_by, priority, year)
        | VALUES
      """.stripMargin
    val maxModelId = rows.map(r => r("modelIndex").toInt).max + 1

    val endOfUpModelQuery =
      s"""
         |;
         |ALTER SEQUENCE risk_object_models_id_seq RESTART WITH $maxModelId;
        """.stripMargin

    val queriesForModels = ModelQuery(startOfModelQuery, rows, endOfUpModelQuery)

    println(queriesForModels)

    val upForPriceStart =
      """
        |INSERT INTO public.prices(
        |	id, risk_object_model_id, min_price, max_price, start_date, end_date)
        |	VALUES
      """.stripMargin

    val maxPriceId = rows.map(r => r("priceIndex").toInt).max + 1

    val upForPriceEnd =
      s"""
         |;
         |ALTER SEQUENCE prices_id_seq RESTART WITH $maxPriceId;
      """.stripMargin

    val query = PriceQuery(upForPriceStart, rows, upForPriceEnd)

    println(query)


    // DOWN
    val downQueryForModels =
      s"""
         |DELETE FROM risk_object_models WHERE id BETWEEN $startModelId AND $maxModelId;
         |
         |ALTER SEQUENCE risk_object_models_id_seq RESTART WITH $startModelId;
        """.stripMargin

    println(downQueryForModels)

    // DOWN
    val downQueryForPrices =
      s"""
         |DELETE FROM prices WHERE id BETWEEN $startPriceId AND $maxPriceId;
         |
         |ALTER SEQUENCE prices_id_seq RESTART WITH $startPriceId;
        """.stripMargin

    println(downQueryForPrices)

  }

  trait Query {
    def buildRow(row: Map[String, String]): String

    final def apply(start: String, rows: Iterable[Map[String, String]], end: String) = {
      rows.map(buildRow).mkString(start, ",\n", end)
    }
  }

  object General {
    def readLines(fileName: String, startModelId: Int, startPriceId: Int) = {
      val lines = scala.io.Source.fromFile("input.csv")
        .mkString
        .split("\n")
        .map(
          _.split(";")
        )

      val titles = lines.head
      val years = (2013 to 2018)
        .map(_.toString)
        .toList
      lines
        .tail
        .map { row =>
          titles.zip(row).toMap
        }
        .sortBy(r => r("Marca") + r("Referencia1"))
        .flatMap { row =>
          years
            .map(y => y -> row(y))
            .filter(_._2 != "0")
            .map(r => row ++ Map("year" -> r._1, "price" -> r._2))
        }
        .zipWithIndex
        .map { row =>
          val modelIndex = startModelId + row._2
          val priceIndex = startPriceId + row._2
          row._1 ++ Map("modelIndex" -> modelIndex.toString, "priceIndex" -> priceIndex.toString)
        }
    }
  }

  object ModelQuery extends Query {
    override def buildRow(row: Map[String, String]) = {
      val id = row("modelIndex")
      val brandId = row("Brand id")
      val referencia = s"${row("Referencia1")} ${row("Referencia2")}"
      val year = row("year")
      s"\t($id, \t$brandId, \t'$referencia', \t'Active', \tnow(), \tnull, \t'[email protected]', \tnull, $year)"
    }
  }

  object PriceQuery extends Query {
    override def buildRow(row: Map[String, String]) = {
      val id = row("priceIndex")
      val modelId = row("modelIndex")
      val price = row("price")
      s"\t($id, \t$modelId, \t$price, \t$price, \tnow(), \tnull)"
    }
  }

}