#396 Importing CSV and Excel

362번 연재에서 데이터베이스 레코드를 CSV 또는 엑셀 파일로 저장(export)하는 방법에 대해서 소개한 바 있습니다. 그 이후로 이런 종류의 파일로부터 레코드를 불러들이는 방법에 대한 연재요청이 많아서 이번 연재에서 다루어 보도록 하겠습니다.

위의 웹페이지 하단에 레코드를 포함하고 있는 파일을 업로드하는 폼을 두도록 하겠습니다. 그래서 폼을 서밋할 때 해당 파일이 파싱되어 데이터베이스에 추가되도록 할 것입니다. 이제 뷰 템플릿 파일의 하단에 이 폼을 추가해 보겠습니다.

<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 옵션을 설정해서 폼이 파일 업로드를 처리할 수 있도록 합니다. 이제 아래와 같이 라우트 파일 상에 새로운 경로를 정의해야 할 필요가 있습니다.

Store::Application.routes.draw do
  resources :products do
    collection { post :import }
  end
  root to: 'products#index'
end

이제 ProductsController에서 import 액션을 추가하겠습니다. 이 액션은 업로드된 파일을 가지고 파일내의 데이터를 데이터베이스로 불러들이는 작업을 하게 됩니다. 파일은 file 매개변수로 업로드될 것이고 레일스는 이 업로드된 파일을 임시로 파일시스템에 저장해 놓은 상태에서 작업을 할 것입니다. 이 말은 Carrierwave나 Paperclip과 같은 파일 업로드를 위한 젬을 사용할 필요가 없다는 의미입니다. 이 액션에서 업로드된 파일은 Product 모델의 import 메소드로 넘겨주고 다시 홈페이지로 돌아올 것입니다.

def import
  Product.import(params[:file])
  redirect_to root_url, notice: "Products imported."
end

CSV 데이터 불러들이기

이제 이 모델과 import 기능에 대해서 초점을 맞춰보겠습니다. 이미 이 클래스에는 CSV 데이터를 내려받기 위한 코드가 있습니다. 그래서 엑셀 파일을 다루기 전에 CSV 데이터를 불러들이는데 초점을 맞추도록 할 것입니다. 어플리케이션 설정파일에는 이미 require 'csv' 코드라인이 있기 때문에 루비의 내장 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 컬럼 값을 이용하여 해당 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

이제 CVS 파일을 수정해서 제품명을 몇개 변경합니다. 이 파일을 업로드하면 새로운 레코드의 추가없이 이전 값들이 변경된 것을 확인하게 될 것입니다.

제대로 작업이 되었다면, 제품명을 변경한 두개의 제품의 이름이 변경되었고 새로 추가된 레코드는 볼 수 없어야 합니다.

엑셀 스프레드시트 불러들이기

지금까지 CSV 파일을 가지고 작업을 했는데, 엑셀 파일을 불러들이려면 어떻게 해야 할까요. 엑셀 파일을 불러들이기 위해 사용할 수 있는 젬들이 여러가지가 있는데, 이 연재에서는, 엑셀과 CSV를 포함해서 다양한 종류의 스프레드시트 포맷들에 대해서 작업하기 위한 표준화된 인터페이스를 제공해 주는 Roo젬을 사용할 것입니다. 어플리케이션의 Gemfile에 이 젬을 추가하여 일반적인 방법으로 젬을 설치하고 bundle 명령을 실행합니다.

gem 'roo'

또한 어플리케이션 설정파일에서 iconv을 require 하도록 수정할 필요가 있습니다. 그러나, 불행히도 이렇게 하면 레일스 어플리케이션을 구동할 때마다 경고 메시지가 나타나는데, 빠른 시일내에 이러한 경고문이 보이지 않기를 바라는 바입니다.

require 'iconv'

자 이제 Roo 젬을 설치했기 때문에 스프레드시트 파일로부터 제품 레코드를 불러들일 수 있을 것입니다. 가장 먼저 해야할 일은 Roo 모듈로부터 스프레드시트 하나는 얻는 일입니다. 이러한 과정을 약간 복잡해서 open_spreadsheet라는 별도의 메소드로 코드를 작성할 것입니다. Roo 스프레트시트는 row 메소드를 제공하는데 이를 이용하면 해당 라인의 값들을 배열로 얻을 수 있게 됩니다. 첫번째 라인은 컬럼헤드 정보를 포함하기 때문에 제일 먼저 불러올 것입니다. 그 다음부터는 스프레드시트 객체에 대해서 last_row를 호출하여 라인 총수를 얻어서 그 수만큼의 루프를 돌리면서 라인을 하나씩 가져오게 될 것입니다.

다음은 약간의 꽁수가 필요합니다. 각 라인을 가져와 컬럼값들을 가지는 배열형태를 반환해 주지만 헤더컬럼명을 키로해서 해시로 변환할 필요가 있습니다. 이를 위해서, 우선 배열의 배열(헤더명과 해당 라인의 적절한 값)을 얻기 위해, 헤더와 현재의 라인을 배열로 만든 후에 이에 대해서 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 젬의 현재 master 브랜치는 Roo 네임스페이스 아래에 클래스명을 두고 있기 때문에 새로운 버전이 릴리스되면, 예를 들면, Excel 대신에 Roo::Excel과 같이 사용해야 할 것입니다. 세번째 옵션인 :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포맷의 엑셀파일을 보여 줍니다. 이 파일을 폼을 이용하여 업로드하면 제대로 동작하는 것을 보게 될 것입니다.

제대로 동작해야 합니다. 이제 목록은 엑셀 문서로부터 새로운 제품정보들을 포함할 것입니다. 이 방법의 한가지 문제는, 어플리케이션에서 외부로 내보낸 파일을 다시 불러올 때 예외가 발생한다는 것입니다. 그러나, 엑셀에서 생성되는 파일들은 아무런 문제없이 불러올 수 있습니다.

데이터 유효성검증하기

위에서와 같이 파일을 불러들이는 코드는 잘 동작하지만, 불러들인 데이터에 대한 유효성 검증을 하지 않았습니다. 만약 price필드가 반드시 존재해야 하는데, 스프레드시트에 있는 레코드 중의 하나가 해당 필드가 누락되어 있다면 어떻게 처리해야 할까요. 지금까지 작업해 온 스크립트에서는 유효성검증을 처리하기가 조금 어렵습니다. 그래서 이러한 유효성검증이 중요한 상황이라면 조금 다른 방법으로 해결하는 것이 좋습니다.

아래에 이것에 대한 예가 있습니다. 제품정보 목록 페이지에 폼을 두는 대신에, 별도의 페이지에 폼을 두고 관련된 안내문을 보여주어, 어떤 컬럼이 필요하고 어떤 데이터형을 가져야 하는지를 사용자에게 알려주도록 합니다. 이 페이지에서 데이터의 유효성검증을 시행해서 유효하지 않은 라인을 보여주어 해당 라인을 수정한 후 다시 해당 문서를 업로드할 수 있도록 할 수 있습니다.

이 연재에서 이러한 내용을 구현할 시간적 여유가 없지만, 구현내용을 하나하나 알아보도록 하겠습니다. 제품정보 페이지에 제품정보를 업로드하는 링크를 두어 new_product_import_path 경로를 연결하고 ProductImport 모델을 새로 만들어 제품정보를 불러들이는 작업을 처리하도록 합니다. 이와 같이 별도의 모델을 만들게 되면 새로운 뷰 템플릿에서 form_for 메소드를 사용할 수 있게 됩니다.

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 메소드를 이용하게 되면, 일반적으로 모델에서 사용하는 것처럼 유효성검증시 발생하는 에러를 쉽게 보여 줄 수 있게 됩니다. 그러나 이 모델은 데이터베이스로 저장하지 않을 것이기 때문에, 그저 간단한 형태의 루비 클래스이면 충분합니다. 여기서는 ActiveMode을 이용해서 ActiveRecord를 시뮬레이션할 것입니다. 이 모델에 대해서 저장을 시도할 경우, 제품정보를 불러들여 데이터가 모두 유효한지를 점검하게 될 것입니다. 그렇지 않다면 하나 또는 그 이상의 에러 메시지가 보이게 될 것입니다. 파일을 불러들이는 작업 자체는 이전에 언급되었던 코드와 동일하게 동작할 것입니다. 최종결과는 제품정보를 불러들일 때 발생하는 유효성검증 에러가 보여지게 됩니다. 이러한 방법에 대한 더 자세한 정보는 Github에 있는 어플리케이션 전체 소스를 살펴 보기 바랍니다.