Ruby on Rails: Allow Import CSV File with Double Quote (") Character

In one of the project that we're working on, we allow the application to import a CSV file that is using tab character to separate the columns. We can read a CSV file using the CSV class or Ruby gem like Roo gem.

But there is a problem when we are importing a CSV file contain double quote character ("), say there is a column in a CSV file with string like Ruler 12" (12 Inch ruler). In that string there is a double quote (") character. If we importing that file, we will encountered an error like this:

CSV::MalformedCSVError - Illegal quoting in line ...  

It says, you have an illegal character quote. To solve this problem we can simple change the quote_char value on CSV options. And to use tab as columns separator, we can set col_sep to \t

If you are using a CSV class to read a CSV file, the code will looks like this:

CSV.foreach(filename, col_sep: "\t", quote_char: '|') do |row|  
  # do something
end  

If you are using a Roo gem (you can find it here, the code will looks like this:

csv = Roo::CSV.new(file.path, csv_options: { col_sep: "\t", quote_char: '|' })  

You can see the quote_char contain a | value, however you can change it with another character, except double quote character ", since we want CSV file to be imported even if it contains invalid quoting (quote doesn't surrounds column).

The default value for quote_char is ". You can find another default value for CSV class options in here. Hope this help for you.

Happy Coding!