Overview

--When inserting a CSV file containing "' "or" " into MySQL using a CSV module in Ruby 2.3, the following two points need to be noted. --If the CSV file contains "" ", if" "" is included when reading the file, a Illegal Quoting error will occur, so specifyquote_char: "\ x00". --Since Ruby 2.4, you can use the liberal_parsing option for CSV modules, but not in 2.3 or earlier. --It is a mistake to specify " \ x00 " which should not normally exist in the file. --"' "needs to be escaped to" \\' "when you put it in MySQL, so replace it with gsub.

environment

ruby 2.3.8p459 (2018-10-18 revision 65136) [x86_64-linux]

Sample code (for TSV files)


require "csv"

summary = {}
CSV.foreach("myfile.tsv", col_sep: "\t", quote_char: "\x00", encoding: "UTF-8", headers: :first_row) do |row|
    summary = {
        :col1 => row[0].to_s,
        :col2 => row[1].to_s.gsub("'", "\\\\'"),
        :col3 => row[2].to_f,
    }
    puts summary
    # MyModel.replace(summary)
end

Test data (TSV format)

col1	col2	col3
homemade cookies	Aunt Stella's cookies	123
nickname	Louis "Satchmo" Armstrong	456
yard-pound system	4' 10"	147.32
yard-pound system 2	4′ 10″	147.32

Execute and check the display

{:col1=>"homemade cookies", :col2=>"Aunt Stella\\'s cookies", :col3=>123.0}
{:col1=>"nickname", :col2=>"Louis \"Satchmo\" Armstrong", :col3=>456.0}
{:col1=>"yard-pound system", :col2=>"4\\' 10\"", :col3=>147.32}
{:col1=>"yard-pound system 2", :col2=>"4′ 10″", :col3=>147.32}

Supplement

--It is confusing that \\\\ is replaced with \\ and is replaced with \.