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

csv, ruby, sqlite, sqlite3-ruby

Solution

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]]

Problem

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?

Original source