Put CSV files containing "'" and "" "in MySQL in Ruby 2.3


--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.


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)

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}


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

Recommended Posts

Put CSV files containing "'" and "" "in MySQL in Ruby 2.3
How to handle TSV files and CSV files in Ruby
Just put `clang 12.0.0` in macOS` Catalina` and put `ruby 3.0.0`
Converting TSV files to CSV files (with BOM) in Ruby
[Ruby] then keyword and case in
Write keys and values in Ruby
Make bubble sort and selection sort in Ruby
Summary of hashes and symbols in Ruby
[Ruby] Classification and usage of loops in Ruby
Difference between "|| =" and "instance_variable_defined?" In Ruby memoization
Reading and writing gzip files in Java
Publish MySQL externally and log in on Ubuntu
[Understanding] Differences between hashes and arrays in Ruby
Put nginx 1.18 in CentOS6 and set reverse proxy
[Ruby] What if you put each statement in each statement? ??
Class in Ruby
Heavy in Ruby! ??
Stumble in MySQL 5.5-> 5.7
Ruby and Gem
Differences between Ruby syntax error statements in Ruby and binary
About the difference between classes and instances in Ruby
In fact, Ruby distinguishes between line breaks and whitespace.
[Ruby] Difficulty in refactoring logical operators (accuracy and readability)
Install rbenv with apt on ubuntu and put ruby
Handling of date and time in Ruby. Use Date and Time properly.