No commit activity in last 3 years
No release in over 3 years
This gem provides MySQL's LOAD DATA INFILE for Mysql2 and ActiveRecord
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
 Dependencies

Development

Runtime

~> 0.4
 Project Readme

LoadDataInfile2

Gem Version Build Status Code Climate Test Coverage Dependency Status

Import the data at a high speed to the table from a text file, using the MySQL LOAD DATA INFILE statement.

This gem is dependent on mysql2.

By using mysql2, as well as plugin of ActiveRecord, it is possible to use in pure Ruby script.

Installation

Add to your Gemfile:

gem 'load_data_infile2'

And bundle.

Examples

Basic Usage

Database configuration:

db_config = {
  host: 'localhost'
  database: 'ldi_test'
  username: 'root'
}

Create client:

ldi_client = LoadDataInfile2::Client.new(db_config)

Import from CSV file:

ldi_client.import('/path/to/data.csv')

Default options are CSV format:

module LoadDataInfile2
  class << self
    def default_import_options
      @default_import_options ||= {
        fields_terminated_by: ',',          # CSV
        fields_optionally_enclosed_by: '"', # standard format of CSV
        fields_escaped_by: '"',             # standard format of CSV
        lines_terminated_by: "\\n",
        ignore_lines: 0
      }
    end
  end
end

TSV format

If you are using TSV format:

opts = {
  fileds_terminated_by: "\\t",
  fields_optionally_enclosed_by: "",
  fields_escaped_by: "\\"
}
ldi_client = LoadDataInfile2::Client.new(db_config, opts)
ldi_client.import('/path/to/data.tsv')

LOAD DATA LOCAL INFILE

If you use LOCAL option:

opts = { local_infile: true }
ldi_client = LoadDataInfile2::Client.new(db_config, opts)
ldi_client.import('/path/to/data.csv')
# => Execute "LOAD DATA LOCAL INFILE '/path/to/data.csv' INTO TABLE `ldi_test`.`data`;"

SQL Options

Support all options of LOAD DATA INFILE statement on MySQL 5.7 .

see: http://dev.mysql.com/doc/refman/5.7/en/load-data.html

For examples:

opts = { local_infile: true }
sql_opts = { table: 'special_users', ignore_lines: 1 }
ldi_client = LoadDataInfile2::Client.new(db_config, opts)
ldi_client.import('/path/to/users.csv', sql_opts)

Mappings

MySQL LoadDataInfile2
LOW_PRIORITY low_priority_or_concurrent: :low_priority
CONCURRENT low_priority_or_concurrent: :concurrent
LOCAL local_infile: true
REPLACE replace_or_ignore: :replace
IGNORE replace_or_ignore: :ignore
tbl_name table: 'special_table_name'
PARTITION partition: 'p0' / ['p0', 'p1', ...]
CHARCTER SET charset: 'utf8'
FIELDS TERMINATED BY fields_terminated_by: ','
FIELDS ENCLOSED BY fields_enclosed_by: '"'
FIELDS OPTIONALLY ENCLOSED BY fields_optionally_enclosed_by: '"'
FIELDS ESCAPED BY fields_escaped_by: '"'
LINES STARTING BY lines_starting_by: '***'
LINES TERMINATED BY lines_terminated_by: '\n'
IGNORE LINES ignore_lines: 1
col_name_or_user_var columns: ['col1', 'col2', '@var3', ...]
SET col_name = expr set: { col1: "'specific value'", col2: '@var', col3: 'NOW()' }

In Rails

Subclass of ActiveRecord is added .load_data_infile.

For example, in the case of User model, you can call the class method named load_data_infile from the User model.

User.load_data_infile('/path/to/data.csv')

If you want to pass options to the initialization of LoadDataInfile2::ActiveRecord, you can use the accessor of class variable named .default_load_data_infile_options.

User.default_load_data_infile_options = { ignore_lines: 1 }
User.load_data_infile('/path/to/data.csv')

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/nalabjp/load_data_infile2.

License

MIT License

The gem is available as open source under the terms of the MIT License.