Posts tagged "unicode"

Since moving to Mac, I always find it surprising that the default Mac OS X Ruby and IRB doesn’t allow inputs with accented characters. So, you cannot do

name = "George Guimarães"

This is annoying for brazilians and anyone who uses non-ASCII characters. The problem is that the default Ruby in Mac OS X isn’t linked against readline. A simple solution is to compile readline on your system, and then compile your own ruby binaries. A better one is to use tools that automate this process.

This post by Christopher Sexton uses homebrew to compile readline-0.6 but compiles ruby by hand. Since we love RVM, we’ll use it too.

If you use Homebrew (and you *should*), just do

brew install readline
brew link readline

Beware: linking readline into your system may break other tools that depends on readline source to compile. It was harmless on my system.

Ok, so now we want to build a new ruby and irb binaries. I wanted to use ruby 1.8.7-p248, so:

rvm install 1.8.7-p248 -C --enable-shared,--with-readline-dir=/usr/local

This tells the configure script to enable shared library linking (it is the default), and to search for readline in /usr/local (homebrew has just linked readline there). You may have to use --force if you already have this ruby version compiled. Newer versions of RVM do not need --force.

Now you can use accented and unicode characters on keyboard input in IRB using ruby 1.8.7.

And you? Do you have any tricks with IRB that you may want to share? Do you use Wirble, Utility Belt or others?

Sometimes users want to slice and dice data as they wish. In such scenarios, it’s usual to export the data in a tabular format so your users can use any spreadsheet editor and do whatever they want.

Usually, we do that using CSV, right? OpenOffice and other editors can open CSV files flawlessly. Just double-click an CSV file and voilà.

However, Excel has some gotchas. If you double-click a CSV file, Excel will open your data with everything in just one column. Of course you can go to Tools > Import, browse to the file, set up comma as separator and then after some clicks you get your data as you wish. This is not user-friendly at all and this is not something we want to explain to our clients. Our clients must be able to simply double-click the my_data.csv file and see the data well structured.

So, here’s what we do at Plataforma to deal with Excel formats.

It’s TSV, not CSV, dude!

First of all, Excel expects your data with tabulations as fields separator. So, what you need is actually a TSV (tab-separated values).

If you are using FasterCSV, you just need to do:

tsv_str = FasterCSV.generate(:col_sep => "\t") do |tsv|
  tsv << headers
  # append your beautiful data here
end

Keep this in mind. Excel demands tabulations, not commas! But there are worse things to come…

No newline in fields

Excel doesnt’ like when you put “\n” inside fields. Although fields are separated by tabulations, it appears Excel can’t cope with extra newlines.

So, if you have some text fields in your model, beware that it may contain newlines and you need to strip those before exporting your data to Excel.

Forget about UTF-8. Use UTF-16!

One of the most hidden specifications of Excel is that it expects our TSV files to be encoded using UTF-16 Little Endian. Did you know that? Well, we didn’t!

Some sources even say that this is the only Unicode format supported in Excel.

What’s the difference between UTF-8 and UTF-16? UTF-8 is a variable byte encoding in which characters may use up to 4 bytes, however for western languages it usually uses one or two bytes. UTF-16 characters always uses at least 2 bytes (remember that characters and bytes are two different things in Unicode “slang”). Generally speaking, UTF-16 takes more space in the filesystem (most of the time).

There’s also the Little Endian part. UTF-16 always uses a pair of bytes to represent a character, however we need to know what’s the correct order of those bytes. We won’t get into details here, but the order is indicated by Byte-order Mark (BOM). In practice, the BOM in UTF-16 will add two bytes in the beginning of a file (you can see that in a hexeditor).

One way to convert your TSV string (generated by FasterCSV) is with Iconv, a tool written for the GNU C Library. Luckly, Iconv is well wrapped in pure Ruby joy. It’s inside Ruby’s standard library and you just need to require it.

However, if you convert an string to UTF-16 Little Endian, Iconv will NOT put the BOM in the beginning. This is compatible with the Unicode FAQ. But since Excel is way out of the standards, you must manually insert the BOM.

You can use Iconv just before sending the file to the user in your controller. It will look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
require 'iconv'
 
class ProjectsController < ActionController::Base
  BOM = "\377\376" #Byte Order Mark
 
  def index
    @projects = Project.all
    respond_to do |format|
      format.html
      format.csv { export_csv(@projects) }
    end
  end
 
protected
 
  def export_csv(projects)
    filename = I18n.l(Time.now, :format => :short) + "- Projects.csv"
    content = Project.to_csv(projects)
    content = BOM + Iconv.conv("utf-16le", "utf-8", content)
    send_data content, :filename => filename
  end
end

You can see that we are still using CSV as an extension to the file. This is because TSV files are not usually associated with Excel. This excerpt uses a timestamp as the filename, which is generally a good practice.

Wrapping up

So, these are the three rules for dealing with Excel-friendly-CSV:

  1. Use tabulations, not commas.
  2. Fields must NOT contain newlines.
  3. Use UTF-16 Little Endian to send the file to the user. And include a Little Endian BOM manually.

Keep this in mind and you’ll never have to explain to your clients how to open the data export in Excel.

There is one last issue: OpenOffice will NOT open files in the Excel “specification” easily. Google Analytics solves this by showing two links to the user: “Export to CSV” and “Export to Excel”. The first is a regular CSV file and the second is the specially crafted Excel-friendly TSV file.

And blame Microsoft for this odd behaviour.

UPDATE: @danielvlopes pointed us an already existing solution which encapsulates the process described in this post, called csv_builder. You just need to set the @output_encoding to use the “utf-16″ (beware of the BOM).

@jncoward also provided a link to the spreadsheet gem, which handles directly Excel formats. TSV is a much simpler and faster format, but the spreadsheet gem might be useful to you in more complex cases.

Finally, Kieran pointed in the comments that we can have XML spreadsheets, while Niko and Chris told us that we can you can also work with HTML tables in Excel, which is simple as well and even allows formatting.