blocks|key|852520|text|require+'csv'++++

csv_text+=+File.read('...')
csv+=+CSV.parse(csv_text,+:headers+=>+true)
csv.each+do+%7Crow%7C
++Moulding.create!(row.to_hash)
end|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|852521|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>require 'csv' 

csv_text = File.read('...')
csv = CSV.parse(csv_text, :headers =&gt; true)
csv.each do |row|
 Moulding.create!(row.to_hash)
end
</code></pre>

blocks|key|1067085|text|yfeldblum答案的更简单版本，它更简单，也可以很好地处理大文件：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1067086|require+'csv'++++

CSV.foreach(filename,+headers:+true)+do+%7Crow%7C
++Moulding.create!(row.to_hash)
end|code-block|syntax|javascript|1067087|不需要with_indifferent_access或symbolize_keys，也不需要先将文件读入字符串。|offset|length|style|CODE|1067088|它不会立即将整个文件保存在内存中，而是逐行读取并为每行创建一个Moulding。|1067089|entityMap^0|0|0|3|N|R|E|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

Simpler version of yfeldblum's answer, that is simpler and works well also with large files:
<pre><code>require 'csv' 

CSV.foreach(filename, headers: true) do |row|
 Moulding.create!(row.to_hash)
end
</code></pre>
No need for <code>with_indifferent_access</code> or <code>symbolize_keys</code>, and no need to read in the file to a string first.
It doesnt't keep the whole file in memory at once, but reads in line by line and creates a Moulding per line.

blocks|key|1067218|text|CSV+gem是专门为这个用例创建的:从smarter_csv文件中读取数据并快速创建数据库条目。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1067219|++require+'smarter_csv'
++options+=+{}
++SmarterCSV.process('input_file.csv',+options)+do+%7Cchunk%7C
++++chunk.each+do+%7Cdata_hash%7C
++++++Moulding.create!(+data_hash+)
++++end
++end|code-block|syntax|javascript|1067220|您可以使用选项chunk_size一次读取N个csv-row，然后在内部循环中使用Resque来生成将创建新记录的作业，而不是立即创建它们-这样您就可以将生成条目的负载分散到多个workers。|1067221|另请参阅：https://github.com/tilo/smarter_csv|1067222|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/tilo/smarter_csv^0|K|B|0|0|7|A|0|5|Z|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|13|8|@]|D|@$9|14|A|15|1|16]]|E|$]]|$1|O|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

The <code>smarter_csv</code> gem was specifically created for this use-case: to read data from CSV file and quickly create database entries.

<pre><code> require 'smarter_csv'
 options = {}
 SmarterCSV.process('input_file.csv', options) do |chunk|
 chunk.each do |data_hash|
 Moulding.create!( data_hash )
 end
 end
</code></pre>

You can use the option <code>chunk_size</code> to read N csv-rows at a time, and then use Resque in the inner loop to generate jobs which will create the new records, rather than creating them right away - this way you can spread the load of generating entries to multiple workers.

See also: 
 <a href="https://github.com/tilo/smarter_csv" rel="noreferrer">https://github.com/tilo/smarter_csv</a>

blocks|key|852640|text|你可以试试Upsert|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|852641|require+'upsert'+#+add+this+to+your+Gemfile
require+'csv'++++

u+=+Upsert.new+Moulding.connection,+Moulding.table_name
CSV.foreach(file,+headers:+true)+do+%7Crow%7C
++selector+=+{+name:+row['name']+}+#+this+treats+"name"+as+the+primary+key+and+prevents+the+creation+of+duplicates+by+name
++setter+=+row.to_hash
++u.row+selector,+setter
end|code-block|syntax|javascript|852642|如果这是您想要的，您还可以考虑去掉表中的自动增量主键，并将主键设置为name。或者，如果存在构成主键的某些属性组合，则将其用作选择器。没有索引是必要的，它只会让它更快。|852643|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/seamusabshere/upsert^0|5|6|5|6|0|0|0|Y|4|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|F|3|G|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|N|$O|$5|P|Q|R|E|$S|T]]]]

You might try <a href="https://github.com/seamusabshere/upsert" rel="noreferrer"><code>Upsert</code></a>:

<pre><code>require 'upsert' # add this to your Gemfile
require 'csv' 

u = Upsert.new Moulding.connection, Moulding.table_name
CSV.foreach(file, headers: true) do |row|
 selector = { name: row['name'] } # this treats "name" as the primary key and prevents the creation of duplicates by name
 setter = row.to_hash
 u.row selector, setter
end
</code></pre>

If this is what you want, you might also consider getting rid of the auto-increment primary key from the table and setting the primary key to <code>name</code>. Alternatively, if there is some combination of attributes that form a primary key, use that as the selector. No index is necessary, it will just make it faster.

blocks|key|1067015|text|这可能会有所帮助。它也有代码示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1067016|http://csv-mapper.rubyforge.org/|offset|length|1067017|或者用于执行相同操作的rake任务：|1067018|http://erikonrails.snowedin.net/?p=212|1067019|entityMap|0|LINK|mutability|MUTABLE|url|1^0|0|0|W|0|0|0|0|12|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@$D|T|E|U|1|V]]|A|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@$D|Y|E|Z|1|10]]|A|$]]|$1|J|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|C]]|Q|$5|M|N|O|A|$P|I]]]]

This can help. It has code examples too: 

<a href="http://csv-mapper.rubyforge.org/" rel="nofollow">http://csv-mapper.rubyforge.org/</a>

Or for a rake task for doing the same: 

<a href="http://erikonrails.snowedin.net/?p=212" rel="nofollow">http://erikonrails.snowedin.net/?p=212</a>

blocks|key|1067333|text|最好将与数据库相关的进程包装在transaction块中。代码片段打击是将一组语言播种到语言模型的完整过程，|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1067334|require+'csv'

namespace+:lan+do
++desc+'Seed+initial+languages+data+with+language+&+code'
++task+init_data:+:environment+do
++++puts+'>>>+Initializing+Languages+Data+Table'
++++ActiveRecord::Base.transaction+do
++++++csv_path+=+File.expand_path('languages.csv',+File.dirname(__FILE__))
++++++csv_str+=+File.read(csv_path)
++++++csv+=+CSV.new(csv_str).to_a
++++++csv.each+do+%7Clan_set%7C
++++++++lan_code+=+lan_set[0]
++++++++lan_str+=+lan_set[1]
++++++++Language.create!(language:+lan_str,+code:+lan_code)
++++++++print+'.'
++++++end
++++end
++++puts+''
++++puts+'>>>+Languages+Database+Table+Initialization+Completed'
++end
end|code-block|syntax|javascript|1067335|下面的代码片段是languages.csv文件的一部分，|1067336|aa,Afar
ab,Abkhazian
af,Afrikaans
ak,Akan
am,Amharic
ar,Arabic
as,Assamese
ay,Aymara
az,Azerbaijani
ba,Bashkir
...|1067337|entityMap^0|F|B|0|0|8|D|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

It is better to wrap the database related process inside a <code>transaction</code> block. Code snippet blow is a full process of seeding a set of languages to Language model,

<pre><code>require 'csv'

namespace :lan do
 desc 'Seed initial languages data with language &amp; code'
 task init_data: :environment do
 puts '&gt;&gt;&gt; Initializing Languages Data Table'
 ActiveRecord::Base.transaction do
 csv_path = File.expand_path('languages.csv', File.dirname(__FILE__))
 csv_str = File.read(csv_path)
 csv = CSV.new(csv_str).to_a
 csv.each do |lan_set|
 lan_code = lan_set[0]
 lan_str = lan_set[1]
 Language.create!(language: lan_str, code: lan_code)
 print '.'
 end
 end
 puts ''
 puts '&gt;&gt;&gt; Languages Database Table Initialization Completed'
 end
end
</code></pre>

Snippet below is a partial of <code>languages.csv</code> file,

<pre><code>aa,Afar
ab,Abkhazian
af,Afrikaans
ak,Akan
am,Amharic
ar,Arabic
as,Assamese
ay,Aymara
az,Azerbaijani
ba,Bashkir
...
</code></pre>

blocks|key|852935|text|我知道这是一个老问题，但它仍然在谷歌的前10个链接中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|852936|逐个保存行效率不高，因为这会导致循环中的数据库调用，您最好避免这样做，特别是当您需要插入大量数据时。|852937|使用batch+insert更好(而且更快)。|852938|INSERT+INTO+`mouldings`+(suppliers_code,+name,+cost)
VALUES
++++('s1',+'supplier1',+1.111),+
++++('s2',+'supplier2',+'2.222')|code-block|syntax|javascript|852939|您可以手动构建这样的查询，然后使用Model.connection.execute(RAW+SQL+STRING)+(不推荐)或使用gem+activerecord-import+(它于2010年8月11日首次发布)，在这种情况下，只需将数据放入数组rows并调用Model.import+rows|offset|length|style|CODE|852940|refer+to+gem+docs+for+details|852941|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/zdennis/activerecord-import/wiki^0|0|0|0|0|H|14|1Y|J|3H|4|3O|H|0|0|T|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|13|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|14|8|@$M|15|N|16|O|P]|$M|17|N|18|O|P]|$M|19|N|1A|O|P]|$M|1B|N|1C|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1D|8|@]|9|@$M|1E|N|1F|1|1G]]|A|$]]|$1|S|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

I know it's old question but it still in first 10 links in google.

It is not very efficient to save rows one-by-one because it cause database call in the loop and you better avoid that, especially when you need to insert huge portions of data.

It's better (and significantly faster) to use batch insert.

<pre><code>INSERT INTO `mouldings` (suppliers_code, name, cost)
VALUES
 ('s1', 'supplier1', 1.111), 
 ('s2', 'supplier2', '2.222')
</code></pre>

You can build such a query manually and than do <code>Model.connection.execute(RAW SQL STRING)</code> (not recomended)
or use gem <code>activerecord-import</code> (it was first released on 11 Aug 2010) in this case just put data in array <code>rows</code> and call <code>Model.import rows</code>

<a href="https://github.com/zdennis/activerecord-import/wiki" rel="nofollow noreferrer">refer to gem docs for details</a>

blocks|key|1067357|text|使用这个gem：https://rubygems.org/gems/active_record_importer|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1067358|class+Moulding+<+ActiveRecord::Base
++acts_as_importable
end|code-block|syntax|javascript|1067359|然后，您现在可以使用：|1067360|Moulding.import!(file:+File.open(PATH_TO_FILE))|1067361|只需确保标题与表的列名匹配即可|1067362|entityMap|0|LINK|mutability|MUTABLE|url|https://rubygems.org/gems/active_record_importer^0|8|1C|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|C|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

Use this gem:
<a href="https://rubygems.org/gems/active_record_importer" rel="nofollow noreferrer">https://rubygems.org/gems/active_record_importer</a>

<pre><code>class Moulding &lt; ActiveRecord::Base
 acts_as_importable
end
</code></pre>

Then you may now use:

<pre><code>Moulding.import!(file: File.open(PATH_TO_FILE))
</code></pre>

Just be sure to that your headers match the column names of your table

blocks|key|1067418|text|更好的方法是将其包含在rake任务中。在/lib/+import.rake+/中创建任务文件，并将以下代码放入该文件中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1067419|desc+"Imports+a+CSV+file+into+an+ActiveRecord+table"
task+:csv_model_import,+[:filename,+:model]+=>+[:environment]+do+%7Ctask,args%7C
++lines+=+File.new(args[:filename],+"r:ISO-8859-1").readlines
++header+=+lines.shift.strip
++keys+=+header.split(',')
++lines.each+do+%7Cline%7C
++++values+=+line.strip.split(',')
++++attributes+=+Hash[keys.zip+values]
++++Module.const_get(args[:model]).create(attributes)
++end
end|code-block|syntax|javascript|1067420|之后，在终端rake+csv_model_import[file.csv,Name_of_the_Model]中运行以下命令|offset|length|style|CODE|1067421|entityMap^0|0|0|6|1D|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

The better way is to include it in a rake task. Create import.rake file inside /lib/tasks/ and put this code to that file.

<pre><code>desc "Imports a CSV file into an ActiveRecord table"
task :csv_model_import, [:filename, :model] =&gt; [:environment] do |task,args|
 lines = File.new(args[:filename], "r:ISO-8859-1").readlines
 header = lines.shift.strip
 keys = header.split(',')
 lines.each do |line|
 values = line.strip.split(',')
 attributes = Hash[keys.zip values]
 Module.const_get(args[:model]).create(attributes)
 end
end
</code></pre>

After that run this command in your terminal <code>rake csv_model_import[file.csv,Name_of_the_Model]</code>

blocks|key|1067281|text|最好使用CSV::Table并使用String.encode(universal_newline:+true)。它将CRLF和CR转换为LF|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1067282|entityMap^0|H|12|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

It's better to use CSV::Table and use <code>String.encode(universal_newline: true)</code>. It converting CRLF and CR to LF

blocks|key|852735|text|如果您想使用SmartCSV|type|unstyled|depth|inlineStyleRanges|entityRanges|data|852736|all_data+=+SmarterCSV.process(
+++++++++++++params[:file].tempfile,+
+++++++++++++{+
+++++++++++++++:col_sep+=>+"\t",+
+++++++++++++++:row_sep+=>+"\n"+
+++++++++++++}
+++++++++++)|code-block|syntax|javascript|852737|这表示每行"\t"中以制表符分隔的数据，其中各行由新行"\n"分隔|offset|length|style|CODE|852738|entityMap^0|0|0|5|4|R|4|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

If you want to Use SmartCSV

<pre><code>all_data = SmarterCSV.process(
 params[:file].tempfile, 
 { 
 :col_sep =&gt; "\t", 
 :row_sep =&gt; "\n" 
 }
 )
</code></pre>

This represents tab delimited data in each row <code>"\t"</code> with rows separated by new lines <code>"\n"</code>

I would like to import data from a CSV file into an existing database table. I do not want to save the CSV file, just take the data from it and put it into the existing table. I am using Ruby 1.9.2 and Rails 3.

This is my table:

<pre><code>create_table "mouldings", :force =&gt; true do |t|
 t.string "suppliers_code"
 t.datetime "created_at"
 t.datetime "updated_at"
 t.string "name"
 t.integer "supplier_id"
 t.decimal "length", :precision =&gt; 3, :scale =&gt; 2
 t.decimal "cost", :precision =&gt; 4, :scale =&gt; 2
 t.integer "width"
 t.integer "depth"
end
</code></pre>

Can you give me some code to show me the best way to do this, thanks.

Ruby on Rails - Import Data from a CSV file

数据库

我想将数据从CSV文件导入到现有的数据库表中。我不想保存CSV文件，只需要从其中获取数据并将其放入现有的表中。我使用的是Ruby 1.9.2和Rails 3。这是我的表格：create_table "mouldings", :force => true do |t|  t.string   "suppliers_code"  t.datetime "created_at"  t.datetime 

问Ruby on Rails -从CSV文件导入数据
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Ruby on Rails -从CSV文件导入数据EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Ruby on Rails -从CSV文件导入数据
EN