Convert a single .csv file to .xlsx file. Convert a folder of .csv files into a folder of .xlsx files.

csv_to_xlsx_r.rb

#
# 1. Convert a given .csv to .xslx
# 2. Convert *.csv, from a given directory (and sub-directories), to .xlsx preserving the structure of the directory
#
#  Example:
#
# 1. csv_to_xlsx(path_to_csv_file)
# 2. csv_to_xlsx_r(path_to_directory)
#

require 'csv'
require 'caxlsx'
require 'byebug'

def csv_file?(file)
  File.extname(file) == '.csv'
end

def csv_to_xlsx(csv_file, xlsx_file)
  return :not_csv unless csv_file?(csv_file)

  csv = CSV.open(csv_file)

  Axlsx::Package.new do |p|
    p.workbook.add_worksheet do |sheet|
      csv.each_with_index do |row, i|
        utf8 = Encoding.find('UTF-8')
        utf8_opts = { invalid: :replace, undef: :replace, replace: '' }
        utf8_row = row.map { |val| val.to_s.encode(utf8, utf8_opts) }
        sheet.add_row(utf8_row)
      end
    end

    p.serialize(xlsx_file)
  end

  puts "Converted #{csv_file} ===> #{xlsx_file}"
end

def csv_to_xlsx_r(csv_folder, xlsx_folder = nil)
  return :not_a_folder unless Dir.exist?(csv_folder)

  # Create folder for the .xlsx files
  csv_folder_basename = File.basename(csv_folder)
  xlsx_folder =
    if xlsx_folder.nil?
      csv_folder.sub(csv_folder_basename, "#{csv_folder_basename}_xlsx")
    else
      "#{xlsx_folder}/#{csv_folder_basename}"
    end

  FileUtils.mkdir_p(xlsx_folder) unless Dir.exist?(xlsx_folder)

  # Convert all .csv files to .xlsx
  Dir.each_child(csv_folder) do |child|
    file_or_dir = "#{csv_folder}/#{child}"

    if Dir.exist?(file_or_dir)
      csv_to_xlsx_r(file_or_dir, xlsx_folder)
    else
      #
      # Inputs:
      #   1. CSV File
      #   2. xlsx Folder
      #
      # Outputs:
      #  -  xlsx file in the given folder
      #
      csv_file = file_or_dir

      next unless csv_file?(csv_file)

      csv_file_basename = File.basename(csv_file, '.*')
      xlsx_file_basename = "#{csv_file_basename}.xlsx"
      xlsx_file = "#{xlsx_folder}/#{xlsx_file_basename}"

      csv_to_xlsx(csv_file, xlsx_file)
    end
  end
end