How to copy 2D array from console into a CSV/Excel file and properly handle newlines and separators

Question

Here is the idea: I have a 2D array with non-escaped strings with newlines \n and occasionally even characters that also act as CSV separators like ;.

I need to easily be able to copy and paste such data from the output of a remote Ruby console into a local Excel file or an online Google spreadsheet.

It used to be working fine until I encountered data with newlines and semicolons using:

class Array 
  def puts_csv
    map { |row| row.join(';') }.each(&method(:puts))
  end
end

[["A1", "B1"], ["A2", "B2"], ["A3", "B3"]].puts_csv
# =>
A1;B1;
A2;B2;
A3;B3;

That outputs a list on the console that I could very easily copy/paste into my spreadsheet application and select ; as the input delimiter.

But now I have to deal with string inputs containing characters that should be escaped, like commas, semicolons or newlines. This completely breaks my copy/pasting of data.

Sample data:

array2D = [
  ["name", "school", "comment"],
  ["Jean François", "ENS", "Lorem ipsum dolor sit amet; consectetur,\nadipiscing elit, sed do eiusmod tempor" 
]

My current method gives three lines, because it breaks lines using a semicolon delimiter:

name;school;comment
Jean François;ENS;Lorem ipsum dolor sit amet;consectetur,
adipiscing elit, sed do eiusmod tempor

The target could be something like the following:

name;school;comment
Jean François;ENS;"Lorem ipsum dolor sit amet; consectetur,
adipiscing elit, sed do eiusmod tempor"

Thanks to "How to copy 2D array from console into a CSV/Excel file and properly handle newlines and separators" I realized there was a trick to deal with newlines by copying first to a Sublime Text file with escaped quotes and then opening it in Excel.

It is still difficult to copy such data directly into a spreadsheet because the newline is still considered a new row.

This code is generated from a remote server, whereas I am required to load the data in a local spreadsheet. It is for several reasons not convenient to exchange files via FTP or SMTP. This is only something that seldom happens so it's not worth adding code to the codebase to handle such exports, therefore I rely mostly on console patches to easily export some data.

For regular exports we have some code in place in other appslications.

How do I generate console output that I can easily paste into a spreadsheet?

How about just generating a CSV file directly, and importing the file into your spreadsheet? Then you can use something like the Ruby CSV class which will handle all this escaping for you. — Casper, Aug 17 '18 at 08:07
...Or even, why not just write to the spreadsheet file directly? You're asking us for a fix to a bad solution -- why not use a good solution instead? — Tom Lord, Aug 17 '18 at 08:32
Please use English punctuation, not French punctuation, when writing in English. — sawa, Aug 17 '18 at 09:08
@Casper Even if I generate some CSV file, I then need to find some way to either email it or download it by FTP and this is somewhat not convenient (I'm generating this data from a remote server and letting other people, not necessarily dev, use the commands, I'll edit my question to reflect this) — Cyril Duchon-Doris, Aug 17 '18 at 11:52

Amadan · Accepted Answer · 2018-08-17T09:03:50.167

5

Use Ruby CSV:

array2D = [
  ["name", "school", "comment"],
  ["Jean François", "ENS", "Lorem ipsum dolor sit amet, consectetur,\nadipiscing elit, sed do eiusmod tempor"]
]

csv = CSV.generate(col_sep: ';') do |csv|
  array2D.each do |row|
    csv << row
  end
end

puts csv

The code changes very little, and CSV does all the heavy lifting for you (including correct escaping and quoting). However, pasting this might give you something wrong in Excel - the details might depend on your current code page, on the locale of Excel, etc. Save it to a file (with BOM at the start: File.write('foo.csv', "\ufeff" + csv, encoding: 'utf-8')) and importing it from Excel should be better.

edited Aug 17 '18 at 09:03

answered Aug 17 '18 at 08:56

Amadan

191,408
23
240
301

When I execute your code and try to copy paste the output from the rails console to some excel sheet, it generates 3 lines. However, your suggestion of saving first to some file worked : I copied the `puts csv` output from the remote console to a local empty file (through Sublime Text), and then opening the file with excel did the trick and the content was imported in 2 lines only. Your other suggestion of saving the said file with encoding utf-8 (with BOM) helped solve the problem of encoding on macOS. Thank you. – Cyril Duchon-Doris Aug 17 '18 at 12:15

score 1 · Answer 2 · edited Feb 04 '20 at 01:32

Using the Spreadsheet gem:

#!/usr/bin/ruby
require 'spreadsheet'

array2D = [
  ["name", "school", "comment"],
  ["Jean François", "ENS", "Lorem ipsum dolor sit amet, consectetur,\nadipiscing elit, sed do eiusmod tempor" ]
]

book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet(:name => 'AAA')
fmt = Spreadsheet::Format.new :text_wrap => true

i=0
j=0
array2D.each do |row|
  row.each do |cell|
    sheet1[i,j] = cell
    if cell.include? "\n"
      sheet1.row(i).set_format(j, fmt)
    end
    j = j+1
  end
  j=0
  i=i+1
end

book.write 'test2.xls'

See "How can I create new spreadsheet worksheets in Ruby using the Spreadsheet gem?" for more information.

How to copy 2D array from console into a CSV/Excel file and properly handle newlines and separators

2 Answers2