Writing CSV files

Start by importing scalaql:

import scalaql._

// Docs classes
import scalaql.docs.Hogwarts._
import scalaql.docs.DocUtils._

// Imports for examples
import java.nio.file.Paths

Basic write

In this example, we'll read an existing CSV file, process it and write the result into a new CSV file.
Input file:

val studentsPath = Paths.get("docs/src/main/resources/students.csv")
// studentsPath: java.nio.file.Path = docs/src/main/resources/students.csv

printFile(studentsPath)
// "name","age","faculty","grade","specialization","birthDay"
// "Harry","19","Gryffindor","85.1","learning","1980-07-31"
// "Ron","18","Gryffindor","66.2","eating","1980-05-01"
// "Hermione","18","Gryffindor","99.6","learning","1979-09-17"
// "Draco","18","Slytherin","85.1","trolling","1980-06-05"
// "Cedric","17","Hufflepuff","90.1","young dying","1977-10-01"

First, define an aggregation Query:

case class FacultyInfo(
  name: String, 
  avgAge: Double, 
  totalGrade: Double)

val aggregation: Query[From[Student], FacultyInfo] =
  select[Student]
    .groupBy(_.faculty)
    .aggregate((faculty, students) =>
      (
        students.avgBy(_.age.toDouble) &&
        students.sumBy(_.grade)
      ).map{ case (avgAge, totalGrade) => FacultyInfo(faculty, avgAge, totalGrade) }
    )
// aggregation: Query[From[Student], FacultyInfo] = FROM(Hogwarts::Student) -> GROUP BY(String) -> AGGREGATE(MdocSession::App::FacultyInfo)

Then you could write the result into a CSV file as follows:

val outPath = Paths.get("docs/target/stats.csv")
// outPath: java.nio.file.Path = docs/target/stats.csv

aggregation
  .foreach(
    csv.write[FacultyInfo].file(outPath)
  )
  .run(
    from(
      csv.read[Student].file(studentsPath)
    )
  )

It will generate a CSV file with the following content:

printFile(outPath)
// name,avgAge,totalGrade
// Gryffindor,18.333333333333332,250.9
// Slytherin,18.0,85.1
// Hufflepuff,17.0,90.1
//

Naming

You could also customize naming style for CSV header.
By default, headers have the same names as case class fields.

Start with the following imports:

import scalaql._
import scalaql.csv.CsvWriteConfig

// Docs classes
import scalaql.docs.Hogwarts._
import scalaql.docs.DocUtils._

// Imports for examples
import java.nio.file.Paths

With the same aggregation query:

case class FacultyInfo(
  name: String, 
  avgAge: Double, 
  totalGrade: Double)

val aggregation: Query[From[Student], FacultyInfo] =
  select[Student]
    .groupBy(_.faculty)
    .aggregate((faculty, students) =>
      (
        students.avgBy(_.age.toDouble) &&
        students.sumBy(_.grade)
      ).map{ case (avgAge, totalGrade) => FacultyInfo(faculty, avgAge, totalGrade) }
    )
// aggregation: Query[From[Student], FacultyInfo] = FROM(Hogwarts::Student) -> GROUP BY(String) -> AGGREGATE(MdocSession::App3::FacultyInfo)

This is how to produce a CSV file with snake_case headers:

val studentsPath = Paths.get("docs/src/main/resources/students.csv")
// studentsPath: java.nio.file.Path = docs/src/main/resources/students.csv
val outPathSnakeCase = Paths.get("docs/target/stats_snake_case.csv")
// outPathSnakeCase: java.nio.file.Path = docs/target/stats_snake_case.csv

aggregation
  .foreach(
    csv
      .write[FacultyInfo]
      .option(Naming.SnakeCase)
      .file(outPathSnakeCase)
  )
  .run(
    from(
      csv.read[Student].file(studentsPath)
    )
  )

It will produce the following CSV file:

printFile(outPathSnakeCase)
// name,avg_age,total_grade
// Gryffindor,18.333333333333332,250.9
// Slytherin,18.0,85.1
// Hufflepuff,17.0,90.1
//