sqltocsv

README

sqltocsv

A library designed to let you easily turn any arbitrary sql.Rows result from a query into a CSV file with a minimum of fuss. Remember to handle your errors and close your rows (not demonstrated in every example).

Usage

Importing the package

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql" // or the driver of your choice
    "github.com/joho/sqltocsv"
)

Dumping a query to a file

// we're assuming you've setup your sql.DB etc elsewhere
rows, _ := db.Query("SELECT * FROM users WHERE something=72")

err := sqltocsv.WriteFile("~/important_user_report.csv", rows)
if err != nil {
    panic(err)
}

Return a query as a CSV download on the world wide web

http.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) {
    rows, err := db.Query("SELECT * FROM users WHERE something=72")
    if err != nil {
        http.Error(w, err, http.StatusInternalServerError)
        return
    }
    defer rows.Close()

    w.Header().Set("Content-type", "text/csv")
    w.Header().Set("Content-Disposition", "attachment; filename=\"report.csv\"")

    sqltocsv.Write(w, rows)
})
http.ListenAndServe(":8080", nil)

Write and WriteFile should do cover the common cases by the power of Sensible Defaults™ but if you need more flexibility you can get an instance of a Converter and fiddle with a few settings.

rows, _ := db.Query("SELECT * FROM users WHERE something=72")

csvConverter := sqltocsv.New(rows)

csvConverter.TimeFormat = time.RFC822
csvConverter.Headers = append(rows.Columns(), "extra_column_one", "extra_column_two")

csvConverter.SetRowPreProcessor(func (columns []string) (bool, []string) {
    // exclude admins from report
    // NOTE: this is a dumb example because "where role != 'admin'" is better
    // but every now and then you need to exclude stuff because of calculations
    // that are a pain in sql and this is a contrived README
    if columns[3] == "admin" {
      return false, []string{}
    }

    extra_column_one = generateSomethingHypotheticalFromColumn(columns[2])
    extra_column_two = lookupSomeApiThingForColumn(columns[4])

    return append(columns, extra_column_one, extra_column_two)
})

csvConverter.WriteFile("~/important_user_report.csv")

For more details on what else you can do to the Converter see the sqltocsv godocs

License

© John Barton but under MIT (see LICENSE) except for fakedb_test.go which I lifted from the Go standard library and is under BSD and I am unsure what that means legally.

Documentation

Overview

sqltocsv is a package to make it dead easy to turn arbitrary database query results (in the form of database/sql Rows) into CSV output.

Source and README at https://github.com/joho/sqltocsv

Index

• func Write(writer io.Writer, rows *sql.Rows) (int, error)
• func WriteFile(csvFileName string, rows *sql.Rows) (int, error)
• func WriteString(rows *sql.Rows) (int, string, error)
• type Converter
  • func New(rows *sql.Rows) *Converter
  • func (c *Converter) SetRowPreProcessor(processor CsvPreProcessorFunc)
  • func (c Converter) String() string
  • func (c Converter) Write(writer io.Writer) (int, error)
  • func (c Converter) WriteFile(csvFileName string) (int, error)
  • func (c Converter) WriteString() (int, string, error)
• type CsvPreProcessorFunc

Functions

func Write

func Write(writer io.Writer, rows *sql.Rows) (int, error)

Write will write a CSV file to the writer passed in (with headers) based on whatever is in the sql.Rows you pass in.

func WriteFile

func WriteFile(csvFileName string, rows *sql.Rows) (int, error)

WriteFile will write a CSV file to the file name specified (with headers) based on whatever is in the sql.Rows you pass in. It calls WriteCsvToWriter under the hood.

func WriteString

func WriteString(rows *sql.Rows) (int, string, error)

WriteString will return a string of the CSV. Don't use this unless you've got a small data set or a lot of memory

Types

type Converter

type Converter struct {
	Headers      []string // Column headers to use (default is rows.Columns())
	WriteHeaders bool     // Flag to output headers in your CSV (default is true)
	TimeFormat   string   // Format string for any time.Time values (default is time's default)
	FloatFormat  string   // Format string for any float64 and float32 values (default is %v)
	Delimiter    rune     // Delimiter to use in your CSV (default is comma)
	// contains filtered or unexported fields
}

Converter does the actual work of converting the rows to CSV. There are a few settings you can override if you want to do some fancy stuff to your CSV.

func New

func New(rows *sql.Rows) *Converter

New will return a Converter which will write your CSV however you like but will allow you to set a bunch of non-default behaivour like overriding headers or injecting a pre-processing step into your conversion

func (*Converter) SetRowPreProcessor

func (c *Converter) SetRowPreProcessor(processor CsvPreProcessorFunc)

SetRowPreProcessor lets you specify a CsvPreprocessorFunc for this conversion

func (Converter) String

func (c Converter) String() string

String returns the CSV as a string in an fmt package friendly way

func (Converter) Write

func (c Converter) Write(writer io.Writer) (int, error)

Write writes the CSV to the Writer provided

func (Converter) WriteFile

func (c Converter) WriteFile(csvFileName string) (int, error)

WriteFile writes the CSV to the filename specified, return an error if problem

func (Converter) WriteString

func (c Converter) WriteString() (int, string, error)

WriteString returns the CSV as a string and an error if something goes wrong

type CsvPreProcessorFunc

type CsvPreProcessorFunc func(row []string, columnNames []string) (outputRow bool, processedRow []string)

CsvPreprocessorFunc is a function type for preprocessing your CSV. It takes the columns after they've been munged into strings but before they've been passed into the CSV writer.

Return an outputRow of false if you want the row skipped otherwise return the processed Row slice as you want it written to the CSV.