joho/sqltocsv

Go package to turn arbitrary database/sql query results into CSV files as easily as possible

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.

sqltocsv's Issues

inverse

CSV for postgres !Except the file is too big, have you done this before?

exporting individual date and time columns

I recently had to export a table from MS SQL Server that had individual date and time columns. sqltocsv exports both columns as time stamps (date + time). The resulting date column in CSV has 2018-05-02 00:00:00 +0000 UTC and time column is 0001-01-01 13:24:02 +0000 UTC.

The attached sqltocsv.go file has a crude hack that formats the results as time if the date is null (0001-01-01) and as date if otherwise.

There are definitely better ways to do this, but it worked for me. Hope this is useful to someone.

sqltocsv.go.txt

doc: state whether or not rows need to be closed

The documentation includes an example of using query rows without defer rows.Close() -- glancing at the code, this doesn't seem correct, but perhaps I'm wrong. Either way, the doc should say.

Thanks for the package!

runtime: out of memory: cannot allocate

I tried to export 1 million row of my table and return:

runtime: out of memory: cannot allocate 1073741824-byte block (1576697856 in use)
fatal error: out of memory

how to use batch or something like that to reduce memory usage?

Csv delimiter and encoding.

...

The converter returns "<nil>" string instead of an empty string when converting a nil value

can't get row count

There doesn't seem to be a facility for getting the number of rows converted?