CODEWITHSUNDEEP
rubysqlitecsvsqlite3-ruby
Username
Username

Reputation: 3663

How do I import a CSV file into an SQLite database with the sqlite3 gem for Ruby

Here's an example of my .CSV file:

column1,column2
data,moreData
foo,bar
cats,dogs

Using Ruby and the sqlite3 gem, is there a way to make an SQLite database and import the .CSV data into it?

Upvotes: 4

Views: 4808

Answers (3)

Adobe
Adobe

Reputation: 13477

You can call sqlite3 binary from ruby:

%x(
sqlite3 "#{pathToDatabase}" << EOF
.mode csv
.separator ',' "\\\\n"
.import "#{pathToCsvFile}" "#{csvTableName}"
EOF
)

Upvotes: 2

Joshua Cheek
Joshua Cheek

Reputation: 31726

Parse CSV with the csv lib, then just insert it like normal.

require "sqlite3"
require 'csv'

db = SQLite3::Database.new ":memory:"

# Create a database
rows = db.execute <<-SQL
  create table users (
    name varchar(30),
    age int
  );
SQL

csv = <<CSV
name,age
ben,12
sally,39
CSV

CSV.parse(csv, headers: true) do |row|
  db.execute "insert into users values ( ?, ? )", row.fields # equivalent to: [row['name'], row['age']]
end

db.execute( "select * from users" ) # => [["ben", 12], ["sally", 39]]

Upvotes: 7

jefflunt
jefflunt

Reputation: 33954

I don't believe import/export of data via CSV is something that is done via the sqlite3 gem itself. You get data into/out of Ruby objects via a CSV library, and then it's just a matter of reading/writing the data to the DB via ActiveRecord, or whatever ORM it is that you're using.

FasterCSV should do it for you in Ruby. If you happen to also be using Rails 3.2, FasterCSV is, I believe, the default implementation behind the scenes of Rails' CSV lib.

Upvotes: 5

Related Questions