FasterCSV tutorial to import data to database?

Question

Is anyone aware of any tutorials that demonstrate how to import data in a Ruby app with FasterCSV and saving it to a SQLite or MySQL database?

Here are the specific steps involved:

Reading a file line by line (the .foreach method does this according to documentation)
Mapping header names in file to database column names
Creating entries in database for CSV data (seems doable with .new and .save within a .foreach block)

This is a basic usage scenario but I haven't been able to find any tutorials for it, so any resources would be helpful.

Thanks!

possible duplicate of Best way to read CSV in Ruby. FasterCSV? — the Tin Man
– the Tin Man, Commented Jun 4, 2011 at 18:27
Thanks, already saw that. Unfortunately it doesn't address steps 2 or 3 I've mentioned above. I also don't need just a tool to do a one-time import, but to build it into the app to let users import data. — Jim Connors
– Jim Connors, Commented Jun 4, 2011 at 18:31

Jim Connors · Accepted Answer · 2011-06-05 15:29:58Z

So it looks like FasterCSV is now part of the Ruby core as of Ruby 1.9, so this is what I ended up doing, to achieve the goals in my question above:

@importedfile = Import.find(params[:id])
filename = @importedfile.csv.path
CSV.foreach(filename, {:headers => true}) do |row|
  @post = Post.find_or_create_by_email(
    :content          =>  row[0],
    :name             =>  row[1],
    :blog_url         =>  row[2],
    :email            =>  row[3]
  )
end
flash[:notice] = "New posts were successfully processed."
redirect_to posts_path

Inside the find_or_create_by_email function is the mapping from the database columns to the columns of the CSV file: row[0], row[1], row[2], row[3].

Since it is a find_or_create function I don't need to explicitly call @post.save to save the entry to the database.

If there's a better way please update or add your own answer.

Community · Accepted Answer · 2017-05-23 10:00:07Z

0

First, start with other Stack Overflow answers: Best way to read CSV in Ruby. FasterCSV?

Before jumping into writing the code, I check whether there is an existing tool to do the import. You might want to look at mysqlimport.

This is a simple example showing how to map the CSV headers to a database's columns:

require "csv"

data = <<EOT
header1, header2, header 3
1, 2, 3
2, 2, 3
3, 2, 3
EOT

header_to_table_columns = {
  'header1'  => 'col1',
  'header2'  => 'col2',
  'header 3' => 'col3'
}

arr_of_arrs = CSV.parse(data)
headers = arr_of_arrs.shift.map{ |i| i.strip }
db_cols = header_to_table_columns.values_at(*headers)
arr_of_arrs.each do |ary|
  # insert into the database using an ORM or by creating insert statements
end

edited May 23, 2017 at 10:00

CommunityBot

11 silver badge

answered Jun 4, 2011 at 18:20

the Tin Man

161k44 gold badges222 silver badges308 bronze badges

Comments

seph · Accepted Answer · 2011-06-05 16:57:48Z

0

Ruby is great for rolling your own import routines.

Reading a file(handy block structure to ensure that the file handle is closed properly):

File.open( filepath ) do |f|
  f.each_line do |line|
    do something with the line...
  end
end

Mapping header names to columns(you might want to check for matching array lengths):
```
Hash[header_array.zip( line_array )]
```

Creating entries in the database using activerecord:

SomeModel.create( Hash[header_array.zip( line_array )] )

It sounds like you are planning to let users upload csv files and import them into the database. This is asking for trouble unless they are savvy about data. You might want to look into a nosql solution to simplify things on the import front.

answered Jun 5, 2011 at 16:57

seph

6,0963 gold badges23 silver badges19 bronze badges

1 Comment

Jim Connors Over a year ago

I'll have to give this a shot. You're right, it is asking for trouble with at-large users, but my users are few and are developers themselves, so it's designed with that in mind.

0x4a6f4672 · Accepted Answer · 2012-07-13 13:56:31Z

0

This seems to be the shortest way, if you can use the ID to identify the records and if no mapping of column names is necessary:

CSV.foreach(filename, {:headers => true}) do |row|
  post = Post.find_or_create_by_id row["id"]
  post.update_attributes row.to_hash
end

answered Jul 13, 2012 at 13:56

0x4a6f4672

28.4k19 gold badges110 silver badges144 bronze badges

Collectives™ on Stack Overflow

FasterCSV tutorial to import data to database?

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related