How to output CSV created by Rails to S3

I implemented the function to upload CSV created by Rails to S3 the other day. I had more consideration than I expected, so I will summarize the contents.

environment

Rails 5.2.3 Ruby 2.6.3

Premise

I do not expect to check CSV in Excel

• This premise is important. If you check the csv file output by the method summarized this time in Excel, the characters will be garbled and you will not be able to display it as expected. Separate consideration is required to be able to check in Excel.

Introduction of gem

You need to install the gem to access AWS from Rails. Add the following to your Gemfile:

Gemfile

gem 'aws-sdk', '~> 3'

Creating a common class

I wanted to use the CSV output function created this time as common logic, so create a file called ʻoutput_csv.rb in / app / lib`. Define the class in output_csv.rb as follows. All methods of this class should be class methods.

app/lib/output_csv.rb

require 'csv'
require 'nkf'

class OutputCsv
  class << self
    #Add methods here
  end
end

• The required csv and nkf will be used later.

Output method implementation on S3

Create a method to output to S3. The CSV character string received as a parameter is output to S3. The CSV character string creation part will be described later.

app/lib/output_csv.rb

#CSV output method-- ①
def save_csv(data)
  #The file name is'Random string_Time stamp.csv'To
  file_name = "#{SecureRandom.urlsafe_base64(9)}_#{Time.current.strftime('%Y%m%d%H%M%S')}.csv"
  #Output to a folder named csv directly under the bucket
  file_full_path = "csv/#{file_name}"

  #CSV output locally in the development environment and to S3 in the production environment-- ②
  if Rails.env.development?
    File.open(Rails.root.join('tmp', file_name), 'w') do |f|
      #Convert character code using NKF-- ③
      f.puts(NKF.nkf('-x -w', data))
    end
  else
    #Instantiate an S3 client-- ④
    s3 = Aws::S3::Client.new
    #Output CSV to S3-- ⑤
    s3.put_object(bucket: ENV['AWS_S3_BUCKET'],
                  key: file_full_path,
                  body: NKF.nkf('-x -w', data),
                  content_type: 'text/csv')
  end
end

(1) Definition of CSV output method. Receives the CSV character string to be output as a parameter (data).

def save_csv(data)

(2) This time, I am trying to output locally in the development environment and to S3 in the production environment. If you want to output to S3 even in the development environment, this branch is unnecessary.

③ NKF is used to convert the character string.

f.puts(NKF.nkf('-x -w', data))

The first arguments -x and -w specify the following conversions. -x: Output half-width katakana without converting to full-width katakana -w: Output with utf-8

(4) Creating an instance of S3 client. Use this to access S3.

s3 = Aws::S3::Client.new

Note that we haven't passed any parameters to the constructor here. The reason is that the role of the EC2 instance on which this Rails application runs has permission to write to S3, so there is no need to consider the permission.

If you want to access S3 with IAM user privileges instead of roles, you need to specify the access key and secret key as follows.

s3 = Aws::S3::Client.new(
  access_key_id: 'your_access_key_id',
  secret_access_key: 'your_secret_access_key'
)

⑤ Create a CSV file on S3.

s3.put_object(bucket: ENV['AWS_S3_BUCKET'],
              key: file_full_path,
              body: NKF.nkf('-x -w', data),
              content_type: 'text/csv')

I'm using a method called put_object. The contents of each parameter are as follows.

-Bucket: The name of the bucket to output. Here, the description is based on the assumption that the environment variable ʻAWS_S3_BUCKET` is set. -Key: Specify the output directory name + file name. -Body: Outputs the character string converted by NKF explained above. -Content_type: The file format is specified. If you do not explicitly specify that it is a csv file here, it will not be recognized as a CSV file on S3.

Creating a CSV string

Create a CSV character string, pass the created character string to the save_csv method created above, and create a method to output a CSV file.

In the method to be created, the header item and data item are received as parameters, converted to CSV character string, and output to a file.

app/lib/output_csv.rb

#CSV string creation method-- ①
def execute(headers, values)
  output = CSV.generate do |csv|
    #Header output-- ②
    csv << headers
    #Output of data items-- ③
    values.each do |value|
      csv << value
    end
  end

  #CSV file output-- ④
  save_csv(output)
end

(1) Definition of CSV character string creation method. Receives an array of headers and data items as parameters. For example, if you want to create the following CSV

id,name,age
1,hoge,20
2,fuga,31
3,foo,43

Create an array like the one below and pass it to the parameters.

#header
headers = ['id', 'name','age']
#data item
values = []
values.push([1, 'hoge', 20])
values.push([2, 'fuga', 31])
values.push([3, 'foo', 43])

OutputCsv.execute(headers, values)

(2) The value of the header received by the parameter is set in CSV.

(3) The data item received by the parameter is set in CSV. Since it is an array, it is taken out and set line by line.

(4) Pass the created CSV character string to the save_csv method created above and output it to S3.

All chords

The whole code is below. The save_csv method is not supposed to be called from the outside, so it is made private.

app/lib/output_csv.rb

require 'csv'
require 'nkf'

class OutputCsv
  class << self
    def execute(headers, values)
      output = CSV.generate do |csv|
        #Header output
        csv << headers
        #Output of data items
        values.each do |value|
          csv << value
        end
      end

      #CSV file output
      save_csv(output)
    end

    private

    def save_csv(data)
      #The file name is'Random string_Time stamp.csv'To
      file_name = "#{SecureRandom.urlsafe_base64(9)}_#{Time.current.strftime('%Y%m%d%H%M%S')}.csv"
      #Output to a folder named csv directly under the bucket
      file_full_path = "csv/#{file_name}"

      #CSV output locally in the development environment and to S3 in the production environment
      if Rails.env.development?
        File.open(Rails.root.join('tmp', file_name), 'w') do |f|
          #Convert character code using NKF
          f.puts(NKF.nkf('-x -w', data))
        end
      else
        #Instantiate an S3 client
        s3 = Aws::S3::Client.new
        #Output CSV to S3
        s3.put_object(bucket: ENV['AWS_S3_BUCKET'],
                      key: file_full_path,
                      body: NKF.nkf('-x -w', data),
                      content_type: 'text/csv')
      end
    end
  end
end

how to use

CSV file output

Output the file to S3.

headers = ['id', 'name','age']
values = []
values.push([1, 'AIUEO', 20])
values.push([2, 'Kakikukeko', 31])
values.push([3, 'SA Shi Su Se So', 43])

OutputCsv.execute(headers, values)

Check the file output to S3

The CSV file will be output to the specified bucket and directory as shown below.

Check the contents of the file

If you download the file output to S3 and check the contents, it will be as follows.