#396 Importing CSV and Excel
- Download:
- source codeProject Files in Zip (90.8 KB)
- mp4Full Size H.264 Video (24.6 MB)
- m4vSmaller H.264 Video (12 MB)
- webmFull Size VP8 Video (14.8 MB)
- ogvFull Size Theora Video (29.5 MB)
エピソード362で、データベースのレコードをCSVやExcelファイルにエクスポートする方法を紹介しました。それ以来、レコードをインポートする方法も紹介してほしいという多くのリクエストを受けました。そこで今回はCSVとExcel形式のファイルからレコードをインポートする方法を紹介します。
方法としては、このページの一番下にフォームを追加し、レコードを含んだファイルをユーザにアップロードさせます。フォームが送信されると、ファイルが解析されてレコードがデータベースに追加されます。ビューテンプレートの最後にフォームを追加します。
<h2>Import Products</h2> <%= form_tag import_products_path, multipart: true do %> <%= file_field_tag :file %> <%= submit_tag "Import" %> <% end %>
インポートを処理するためのオブジェクトがまだないので、 form_for
ではなくform_tag
を使用しました。フォームは、ProductsController
内に新しく作るimport
アクションに送信されます。multipart
オプションを設定することで、ファイルのアップロードに対応させています。routesファイルに新しいパスを設定する必要があるので、ここで行ないます。
Store::Application.routes.draw do resources :products do collection { post :import } end root to: 'products#index' end
次にProductsController
にimport
アクションを追加します。これはアップロードされたファイルを取得して、データをデータベースにインポートします。ファイルはfile
パラメータにアップロードされて、Railsがアップロードされたファイルが処理されるまで一時的にファイルシステムに保存します。つまり、アップロードされたファイルの処理にCarrierWaveやPaperclipを使用しなくてもいいということです。このアクションではアップロードされたファイルを、Product
モデル内に新しく作成したimport
メソッドに渡して、トップページにリダイレクトします。
def import Product.import(params[:file]) redirect_to root_url, notice: "Products imported." end
CSVデータをインポートする
次はモデルとインポート処理に注力します。このクラスにはすでにCSVデータをエクスポートするためのコードがあるので、まずCSVデータをインポートすることに注力し、その後でExcelファイルの処理に取り組みます。アプリケーションの設定ファイルにはすでにrequire 'csv'
の行があるので、Rubyに最初から組み込まれているCSVライブラリを利用することができます。
def self.import(file) CSV.foreach(file.path, headers: true) do |row| Product.create! row.to_hash end end
import
メソッドを上に示しました。ここではCSV.foreach
を呼び出し、ファイルへのパスを渡します。これが、見つかったデータの各行ごとにブロックに渡されます。headers
オプションを使用したのは、データの先頭行に各列の名称が入っているのを想定して、これをデータの名前をつけるのに使用するからです。そしてrow.to_hash
を渡して商品データを作成します。列名がProduct
の属性と一致した場合、各行に対応した新規レコードが生成されます。単純なCSVファイルを使用してこれを試してみましょう。
name,price,released_on Christmas Music Album,12.99,2012-12-06 Unicorn Action Figure,5.85,2012-12-06
新しく作成したフォームからこのファイルをアップロードして送信すると、リストに新しい商品が表示されます。
既存のレコードを修正する
データ中にid
列があれば、新規レコードを追加する代わりに、その列を使って既存のレコードを更新することができれば便利でしょう。これを利用すれば、CSVファイルをダウンロードして、そのファイル上で商品情報を修正し、再度アップロードするという方法で、複数の商品情報を一度に修正することができます。既存の商品情報をダウンロードすると、以下のようなCSVデータを取得できます。
id,name,released_on,price,created_at,updated_at 4,Acoustic Guitar,2012-12-26,1025.0,2012-12-29 18:23:40 UTC,2012-12-29 18:23:40 UTC 5,Agricola,2012-10-31,45.99,2012-12-29 18:23:40 UTC,2012-12-29 18:23:40 UTC 6,Christmas Music Album,2012-12-06,12.99,2012-12-29 20:55:29 UTC,2012-12-29 20:55:29 UTC 2,Red Shirt,2012-10-04,12.49,2012-12-29 18:23:40 UTC,2012-12-29 18:23:40 UTC 1,Settlers of Catan,2012-10-01,34.95,2012-12-29 18:23:40 UTC,2012-12-29 18:23:40 UTC 3,Technodrome,2012-12-22,27.99,2012-12-29 18:23:40 UTC,2012-12-29 18:23:40 UTC 7,Unicorn Action Figure,2012-12-06,5.85,2012-12-29 20:55:29 UTC,2012-12-29 20:55:29 UTC
これを利用しやすい形にするためにインポート方法を変更します。データの各行ごとに商品情報を作成するのではなく、id
列の値に基づいて該当データを検索します。find_by_id
を使用して、一致するレコードが見つからなかった場合にはnilが返されるようにして、その場合は新規レコードを作成します。 次に該当行のデータに基づいて商品の属性を設定しますが、その中には例えばid
のように変更したくない属性も含まれるので、モデルのattr_accessible
リストに含まれる属性だけを更新します。
def self.import(file) CSV.foreach(file.path, headers: true) do |row| product = find_by_id(row["id"]) || new product.attributes = row.to_hash.slice(*accessible_attributes) product.save! end end
ここでCSVファイルを編集して、いくつかの商品の名前を変更しました。このファイルをアップロードしたら、これらの変更が反映されて、新規レコードは追加されないはずです。
うまくいきました。2つの商品の名前を変更したのが反映されていて、新規レコードは追加されていません。
Excelスプレッドシートをインポートする
CSVファイルの処理はうまくいきましたが、Excelファイルのインポートはどのようにすればいいでしょうか? Excelからのインポートを処理するgemはいくつかあります。今回のエピソードで使用するRoo gemはExcelやCSVを含む多くのスプレッドシートの形式に対応した標準的なインターフェイスを提供します。このgemは通常の方法でインストールします。アプリケーションのgemfileにgemを追加してbundle
コマンドを実行します。
gem 'roo'
それに加えてアプリケーションの設定ファイルを修正して、iconv
ライブラリをrequireします。残念ながら現在はRailsアプリケーションを起動するごとに警告が出てしまうので、gemがこれを使わない方式に移行してくれることを期待したいところです。
require 'iconv'
Rooがインストールできたので、これでスプレッドシートから商品レコードをインポートすることができます。まず最初にRooからスプレッドシートを取得します。この操作は少し複雑なので、独立したopen_spreadsheet
というメソッドにして、後で書くことにします。Rooスプレッドシートにはrowメソッドがあり、その行の値の配列を返します。第1行目にはヘッダ情報が含まれているのでまずそれを取得します。その後で残りの行をループして各行のデータを取得し、スプレッドシートオブジェクトのlast_row
を呼び出して行の総数を取得します。
その次が厄介な部分です。各行を取得すると値の配列が返されますが、ヘッダ列をkeyに設定しながらそれをハッシュに変換する必要があります。そのためにはヘッダとカレント行で配列を作成してそれに対してtranspose
を呼び出して配列の配列を作成します。その各々に、ヘッダ名とカレント行の対応する値が含まれています。最後にこれをハッシュに変換することにより、CSVライブラリから得られるのに似たオブジェクトになります。
def self.import(file) spreadsheet = open_spreadsheet(file) header = spreadsheet.row(1) (2..spreadsheet.last_row).each do |i| row = Hash[[header, spreadsheet.row(i)].transpose] product = find_by_id(row["id"]) || new product.attributes = row.to_hash.slice(*accessible_attributes) product.save! end end
次にopen_spreadsheet
メソッドを定義します。これは、ファイルの拡張子に基いてそれに対応したRooスプレッドシートを組み立てます。アップロードされたファイルは拡張子を持たない一時ファイルに保存されているので、original_filename
を使用します。現在のRooのマスターブランチではRoo
名前空間の下にクラス名があるので、新しいバージョンが公開されたら、例えば単にExcel
とするのではなくRoo::Excel
とします。3つ目のオプションの:ignore
を指定することで、ファイルの拡張子がタイプに一致しなくても例外を発生させないようにします。
def self.open_spreadsheet(file) case File.extname(file.original_filename) when '.csv' then Csv.new(file.path, nil, :ignore) when '.xls' then Excel.new(file.path, nil, :ignore) when '.xlsx' then Excelx.new(file.path, nil, :ignore) else raise "Unknown file type: #{file.original_filename}" end end
正しい列名といくつかの商品レコードが入ったxlsx
形式のExcelファイルを準備して、フォームからアップロードしてうまくいくか見てみます。
うまくいきました。リストには、Excelファイルからの新規の商品情報が入っています。この方法の問題の一つとして、アプリケーション自身からエキスポートしたファイルをインポートできません。これを試すと例外が発生します。Excelで作成したファイルは、問題なくインポートできるようです。
データを検証する
この問題を除いては作成したインポートスクリプトはうまく動作しているようですが、まだインポートしたデータの検証ができていません。例えばprice
フィールドが空欄でないことを検証することにして、1件のレコードでこのフィールドが空欄であったとします。この状況にはどのように対応すればいいでしょうか? 今回の方法では検証機能を付加するのは少し難しいので、検証をおこなうことが重要な場合には別のアプローチを採用するのがいいでしょう。
一つの対応案を紹介します。商品一覧のページ内に直接表示させるのではなく、別のページにユーザに対する指示(どの列が必須入力でデータ型が何であるべきかなど)を表示させます。このページでデータを検証して、エラーになった行を表示して修正させてから再度ファイルをアップロードできるようにします。
時間がないのでこの機能を今回のエピソードの中で作成することはできませんが、仕組みの概要を簡単に紹介します。商品一覧ページに、商品をアップロードするためのリンクがあり、new_product_import_path
に関連づけられています。これが新規に作成したProductImportsController
によって処理され、 もう一つ新規に作成したProductImport
モデルによって商品データのインポートが処理されます。これを独立したモデルとすることによって、新しいテンプレートの中で
class ProductImportsController < ApplicationController def new @product_import = ProductImport.new end def create @product_import = ProductImport.new(params[:product_import]) if @product_import.save redirect_to root_url, notice: "Imported products successfully." else render :new end end end
ファイルをアップロードするための、form_for
を使った新しいテンプレートがあるので、他のモデルのときと同じように検証エラーを簡単に表示させることができます。このモデルはデータベースに保存されているものではなく、シンプルなRubyクラスです。ここではActiveModel
を使ってActiveRecord
をシミュレートします。このモデルを保存しようとすると、商品データをインポートしてデータが正しいかどうかをチェックします。正しくない場合は対応した数のエラーメッセージが表示されます。インポートの処理自体は、前のコードと同じように動作します。処理の結果として、商品データをインポートしようはhaとしたときに発生した検証エラーがすべて表示されます。この方法の詳細については、Github上の完成版アプリケーションのソースコードを参照してください。