ruby - Export content of a SQLite3 table in CSV

Question

I have a Ruby script that generates a SQLite3 database.

I want to be able to generate an "output.csv" file containing one of the database tables.

Is there a way to handle that in Ruby?

Answer 1

It is easy with Sequel and to_csv:

require 'sequel'
DB = Sequel.sqlite
# since Sequel 3.48.0 to_csv is deprecated, 
# we must load the to_csv feature via a extension
DB.extension(:sequel_3_dataset_methods) #define to_csv 
DB.create_table(:test){
  Fixnum :one
  Fixnum :two
  Fixnum :three
}
#Prepare some test data
5.times{|i|
  DB[:test].insert(i,i*2,i*3)
}

File.open('test.csv', 'w'){|f|
  f << DB[:test].to_csv
}

The result is:

one, two, three
0, 0, 0
1, 2, 3
2, 4, 6
3, 6, 9
4, 8, 12

In my test I had problems with line ends, so I needed an additional gsub:

File.open('test.csv', 'w'){|f|
  f << DB[:test].to_csv.gsub("
","
")
}

If you want the export without the header line, use to_csv(false)

Remarks:

• .to_csv is deprecated since Sequel 3.48.0 (2013-06-01). You may use an old version with gem 'sequel', '< 3.48.0' or load the extension sequel_3_dataset_methods).

---

To get support for other seperators and other CSV-features you may use a combination of Sequel and CSV:

require 'sequel'
require 'csv'
#Build test data
DB = Sequel.sqlite
DB.create_table(:test){
  Fixnum :one
  Fixnum :two
  Fixnum :three
  String  :four
}
#Prepare some test data
5.times{|i|
  DB[:test].insert(i,i*2,i*3, '<a href="www.test.com">test, no %i</a>' % i)
}

#Build csv-file
File.open('test.csv', 'w'){|f|
  DB[:test].each{|data| 
    f << data.values.to_csv(:col_sep=>';')
  }
}

Result:

0;0;0;"<a href=""www.test.com"">test, no 0</a>"
1;2;3;"<a href=""www.test.com"">test, no 1</a>"
2;4;6;"<a href=""www.test.com"">test, no 2</a>"
3;6;9;"<a href=""www.test.com"">test, no 3</a>"
4;8;12;"<a href=""www.test.com"">test, no 4</a>"

As an alternative you may patch Sequel::Dataset (modified code from a post of marcalc at Github):

class Sequel::Dataset
    require 'csv'
    #
    #Options: 
    #* include_column_titles: true/false. default true
    #* Other options are forwarded to CSV.generate
    def to_csv(options={})
      include_column_titles = options.delete(:include_column_titles){true}  #default: true
      n = naked
      cols = n.columns
      csv_string = CSV.generate(options) do |csv|
        csv << cols if include_column_titles
        n.each{|r| csv << cols.collect{|c| r[c] } }
      end
      csv_string
    end 
end