Hi there,

I've watched your CSV export episode and I'm using the exact same code to export the records to a CSV.

I've got two tables: Orders and Sales. 1 Order has_many Sales and 1 Sale belongs_to an Order. The Orders table is the "parent" of the Sales table. So the 'id' of the table Orders is the same as the 'order_id' in the Sales table. This is how I match a Sale to an Order. Just like a blog article with comments.

Here is an example of the sales csv export code:

def self.to_csv
    attributes = %w{id product_group quantity product price setup discount order_id}
    CSV.generate(headers: true) do |csv|
      csv << attributes
      all.each do |sale|
        csv << sale.attributes.values_at(*attributes)
      end
    end
  end

Here is an example of the orders csv export code:

def self.to_csv
    attributes = %w{id user_id user_name brand customer_id}
    CSV.generate(headers: true) do |csv|
      csv << attributes
      all.each do |order|
        csv << order.attributes.values_at(*attributes)
      end
    end
  end

Both exports are perfectly fine but it's a mess to connect the two tables again in excel. As the 'id' of the orders table is the same as the 'order_id' in the sales table I thought there should be a way to export all columns of both tables combined in just one csv file. Is that possible?

MANY MANY thanks in advance!

No Ideas? :(

Couple questions and suggestions that might help speed up you getting an answer:

you can wrap code in triple-backticks to make it nice and readable. and you can also specify what language so it highlights syntax nicely. simply do

'''ruby

code here

'''

except instead of triple quotes, use the backtick (like when you press tilde without shift): `

def self.to_csv
  attributes = %w{id product_group quantity product price setup discount order_id}
  CSV.generate(headers: true) do |csv|
    csv << attributes
    all.each do |sale|
      csv << sale.attributes.values_at(*attributes)
    end
   end
end

Second thing is that, your question seems to me more like an excel question than a ruby or rails one. Totally possible it might be super easy to export both csv's in one excel file, but I'm unfamiliar with what that would actually look like. Not at all sure what kind of output you desire so not really sure how to begin helping.

Do you mean than when you open up the excel file, there are two sheets in there with the association already linked up? That quickly veers into, well what spreadsheet programming are you using, how do they store their data (XML?), is there a gem? Perhaps this gem could be helpful to you: https://github.com/zdavatz/spreadsheet

The final thing I'd have to say here is that, I've done quite a bit of ruby/rails/spreadsheet related things. I find they tend to be extremely error-prone and time consuming the more spreadsheet-like things you begin doing in your code. If setting up the file the way you like it is a matter of copy + paste and a couple clicks to set that relationship up, its usually way more efficient just to do that part manually - especially if you don't do it too often.

Hi Chris,

thank you for your reply. I wasn't aware of the markdown possibility. Thanks for the tip.

Actually I just want to export some fields of the 2 seperate tables (Sales and Orders) in one file - without spreadsheets.

Just like that I have

id, user_id, user_name, brand, customer_id, product_group, product, quantity, price, setup, discount

as the column header. The first part (id, user_id, user_name, brand, customer_id) will come from the Orders table and the second part (product_group, product, quantity, price, setup, discount) from the Sales table. I know that then the first part from the orders table gets duplicated each time when one order has more than 1 Sale, but that's ok. So the result should look like this (first 2 got the same order_id, as one customer has bought 2 shoes within one order):

id, user_id, user_name, brand, customer_id, product_group, product, quantity, price, setup, discount
1, 10, Sascha, Adidas, 35678, Shoes, Shoe No. 1, 1, 49.99, 0, 0
1, 10, Sascha, Adidas, 35678, Shoes, Shoe No. 2, 1, 69.99, 0, 0
2, 10, Sascha, Adidas, 35699, Shoes, Shoe No. 3, 1, 79.99, 0, 0

Hope that my questions is clearer now! :)

Many thanks,
Sascha

You could totally do that. Just need to wrap your mind around it a little more. Really all you're doing is:

csv_string = CSV.generate do |csv|
  csv << ["row", "of", "CSV", "data"]
  csv << ["another", "row"]
  # ...
end

Now that you know you want:

id, user_id, user_name, brand, customer_id, product_group, product, quantity, price, setup, discount 

Then the question is, how will you set up your code to do that? Well, you'll need to grab each sale, but it would probably be nice to do that by each order. So loop through each of your orders, then grab each of its sales.

so to pseudocode this out, would probably look something like:

CSV.generate(headers: true) do |csv|
  csv << ["id", "user_id", "user_name", "brand", "customer_id", "product_group", "product", "quantity", "price", "setup", "discount"]
  Order.all.each do |order|
    order.sales.each do |sale|
      csv << [order.id, order.user_id, order.user_name, order.brand, order.customer_id, sale.product group, #....etc as you listed above
    end
  end
end

that way you'll get one row per sale, initially grouped by order. probably wanna clean up your headers to be more specific though

Hi Chris,

many many thanks! I'll try this out.