Skip to content

Exporting data to CSV in rails console

I’ve had a similar post to this in the past where I exported an entire table to CSV, but what if you want to export only a subset of that data? Well, that’s what this post is for :)

  1. Start with a query that narrows things down as much as possible
  2. Then loop through those results to apply any additional filtering you need
  3. Finally generate the CSV file with a header
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
date = Date.new(2016,12,15)
l = Table.where("created_at > ? AND column != 'variable'", date)
 
ActiveRecord::Base.logger = nil
results = []
l.each do |i|
 u = User.where(:email => i.email)
 if u.present?
   if Table.where(:email => u[0].email).blank?
     results << {:email=>i.email,:member=>'y'}
   end
 else
   results << {:email=>i.email,:member=>'n'}
 end
end;0
 
 
require 'csv'
 
file = "#{Rails.root}/public/data.csv"
 
CSV.open( file, 'w' ) do |writer|
 writer << results.first.map { |a,v| a }
 results.each do |s|
 writer << s.map { |a,v| v }
 end
end
 
 

Global Replace Content in RoR

I think it’s fairly typical to have to make a global replace on your database at some time or another – updating old links, old code, ect.  And today this was something I was tasked with!  It was pretty straight forward, and these two stackoverflow questions helped me do it.  To get this working you should just need to follow the commands below, while replacing the following:

  • TABLE – The database table you’re replacing content in
  • COLUMN – The column in that table you’re replacing content in
  • BAD – The old string you want to replace
  • GOOD – The new string you want to replace BAD with
1
2
3
4
5
6
7
8
9
10
rails c
 
ActiveRecord::Base.connection.execute(%q{
    update TABLE
    set COLUMN = replace(
        COLUMN,
        'BAD',
        'GOOD'
    )
})

Checking for a string

You could also use the following code to check whether or not the string exists in any entries in that model in the first place. Just swap out MODEL for the model, SEARCH for the string you want to check, and COLUMN for the column you want to search. It will output the total number of entries containing that string, and then loop through their ID numbers.

1
2
3
4
5
6
7
8
9
10
11
rails c
 
a = MODEL.where("COLUMN like ?", "%SEARCH%");0
 
puts a.length
 
puts " "
 
a.all.each do |a|
  puts a.id
end;0

Export table in rails console

This is just a small twist on the very nice snippet I found from this post.  I just changed it slightly, so you can get all the attributes automatically so you can easily swap out any model.  And it will also list the column names in the first row.

1
2
3
4
5
6
7
8
9
10
11
12
require 'csv'
 
file = "#{Rails.root}/public/data.csv"
 
table = User.all;0
 
CSV.open( file, 'w' ) do |writer|
  writer << table.first.attributes.map { |a,v| a }
  table.each do |s|
    writer << s.attributes.map { |a,v| v }
  end
end

simple-zoom

Just finished pushing a very simple zoom app in GitHub, I couldn’t find a nice script that would allow the image to take up the full size of it’s parent element when zoomed in, and would handle updating the image dynamically.  So I wrote one for myself.

Get the files on GitHub.

See a preview:

Add tracking tokens to WordPress

Just had to do a search and replace to add in tracking tokens site-wide to a wordpress site.  I tried some search & replace plugins, but I found them all a bit buggy, so I opened up PHP My Admin to handle it. I had a list of links so I just ran them all like:

1
UPDATE wp_posts SET post_content = REPLACE (post_content,'http://example.com/page.html','http://example.com/page.html?UTM=token');

then ran the following, because I knew a few links already had the tracking token added:

1
UPDATE wp_posts SET post_content = REPLACE (post_content,'?UTM=token?UTM=token','?UTM=token');

And, last but not least I verified that all links where updated by running:

1
SELECT post_content FROM wp_posts WHERE (CONVERT(`post_content` USING utf8) LIKE '%%http://example.com%%' AND CONVERT(`post_content` USING utf8) NOT LIKE '%%UTM%%')

So, I’m basically just checking that every post with that domain also has the token code in it somewhere.

You can be a bit more thorough by using regex, but that should do the trick if you only have a few links to update.

UPDATE 6/2016

I just needed to do a similar task, but I needed to add a tracking token to all links to a domain, so I needed a bit of regex.  This time I found a nice search and replace plugin that supports regex. And I used these commands to first add in the token:

1
2
Search pattern: ((<a href="(http://)?(www.)?example.com/(.*?))")
Replace pattern: $1?UTM=token

This will pick up all links, weather they have http or not, and weather they have www. or not.  And again I ran this kind of replace to fix any links that already had tracking added to them:

1
2
Search pattern: ?UTM=token?UTM=token
Replace pattern: ?UTM=token