Importing CSV file into Sqlite3 - easiest way to deal with created_at, updated_at columns?

Question

I have a CSV file I'm trying to import into Sqlite3 using the .import command. The db table has updated_at and a created_at columns, but my CSV file does not, so when I run the import I get an error message: "expected 18 columns, only found 16"

What is the easiest way to import this file?

I tried adding created_at, updated_at columns to the CSV file but got an error.

Several forums suggest using FasterCSV, but I'm unclear on how to use the gem to address this issue specifically.

I solved this problem by re-creating the table without the created_at updated_at timestamp. Then imported the files to temp tables using the sqlite3 console and then imported the info from temp tables to the target table. — william tell, Feb 13 '13 at 01:54

score 2 · Accepted Answer · answered Feb 10 '13 at 05:02

Here, William, give this code a try. It's Ruby code to import a CSV into a database, ignoring created_at and updated_at.

To run it:

Paste the code into a new file. Let's call it import.rb
Edit the file. Change the word "mytablename" in self.table_name = "mytablename" to the actual name of the table you are importing into.
Near the end of the file, replace mydb.db with the actual name of your Sqlite3 file. It should be a relative path to the file (relative to import.rb).

On the command line, type:

gem install active_record sqlite3
ruby import.rb path/to/csvfile.csv

(If you're on a Mac, use "sudo gem install instead of just "gem install".)

Ok, here's the code:

require 'rubygems'
require 'csv'
require 'active_record'
require 'sqlite3'

ActiveRecord::Base.configurations = YAML::load(DATA)
ActiveRecord::Base.establish_connection('development')

class Record < ActiveRecord::Base
  self.table_name = "mytablename" # <=== REPLACE THIS with your table name
end

CSV.foreach ARGV[0], :headers => true do |row|
  print "."
  hash = Hash[row]
  hash.delete("updated_at")
  hash.delete("created_at")
  Record.create! hash
end

puts "\nDone."

__END__
development:
  adapter: sqlite3
  database: mydb.db  # <=== REPLACE THIS with a relative path to your sqlite3 DB
  pool: 5
  timeout: 5000

score 0 · Answer 2 · edited May 23 '17 at 12:07

If the values for created_at and updated_at are added as follows in the CSV, .import xyz.csv xyz should import the data correctly.

... | "2013-02-10 15:17:17" | "2013-02-10 15:17:17"

What error are you getting while importing this way?

When you use a library to import data, the import happens through the ActiveRecord interface which automatically adds the created_at and updated_at fields, just like when you add a record from a GUI.

Look at the answers for Ruby on Rails - Import Data from a CSV file for code examples.

And refer to Ruby's csv standard library (which used to be FasterCSV previously) for information on using the library.

Importing CSV file into Sqlite3 - easiest way to deal with created_at, updated_at columns?

2 Answers2