Export one column from a database table?

  • Go_on
  • Newbie
  • Newbie
  • Go_on
  • Posts: 14

Post 3+ Months Ago

Hello
I have a question about how i could export only one column from a database table?
I have this table called 'ibf_members' and i want to export the column called 'email'.
It should be possible to export this table as an xls. and open it with Excel and select the column and copy, the problem is that the .xls file would be 56,7mb :wink: and that's too much for excel and it crashes, opening with notepad should be possible, but with notepad i can't select a single column. :\

So can someone help me setting up a command to export the column? or another way to separate it from 'ibf_members'?! :)

Greetings,
Matthieu
  • iliya428
  • Beginner
  • Beginner
  • User avatar
  • Posts: 59

Post 3+ Months Ago

Simple sql query:
select email from ibf_members;

If you use phpMyAdmin, go to SQL tab and execute it. Then choose to download to Excel, otherwise specify what software do you use (I assumed MySQL+phpMyAdmin - as this is default for many people here)
  • Go_on
  • Newbie
  • Newbie
  • Go_on
  • Posts: 14

Post 3+ Months Ago

Sweet! It works, thank you very much! :)
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6229
  • Loc: South-Africa

Post 3+ Months Ago

If you want to insert that data directly into another table you could also use insert select:
Code: [ Select ]
INSERT INTO newtable (newcolumn) SELECT email FROM ibf_members
  • Go_on
  • Newbie
  • Newbie
  • Go_on
  • Posts: 14

Post 3+ Months Ago

Okay great, thank you for the info! :)

Enjoy,
Matthieu
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6229
  • Loc: South-Africa

Post 3+ Months Ago

Did you find what you were looking for exactly? If not, please don't hesitate to ask more questions.
  • Go_on
  • Newbie
  • Newbie
  • Go_on
  • Posts: 14

Post 3+ Months Ago

No it all worked out. :) I managed to export the emails and pasted it into a simpel .txt file.
  • jillbalkus
  • Born
  • Born
  • jillbalkus
  • Posts: 3

Post 3+ Months Ago

Hi! I have a question that sounds similar to Go_On's question:

I have a table (products_description) with a column (products_head_title_tag).

I want to replace its contents with that of another table (products) and column (products_header).

Can this be done?

Thanks!
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6229
  • Loc: South-Africa

Post 3+ Months Ago

Code: [ Select ]
INSERT INTO products_description (products_head_title_tag) SELECT products_header FROM products

Please just make a backup before you do this though, just now something goes wrong and then you can't go back ...
  • jillbalkus
  • Born
  • Born
  • jillbalkus
  • Posts: 3

Post 3+ Months Ago

Thanks! This added new rows to the table. What I want to do is copy over the column in the existing rows. Does that make sense?

Both tables have the the same column (products_id) so that everything should match up. Do I need to reference that?
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6229
  • Loc: South-Africa

Post 3+ Months Ago

actually I am not quite sure ... If they are both ordered by product_id that would make sense ... but I am not sure how MySql's default ordering works and I am on my way out now so I can't really help you out right now ...
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

If you want to play it safe, create a new table and use the INSERT SELECT syntax to fill the new table using what would normally be an SQL query that would join the two tables using that product_id KEY/INDEX.

Code: [ Select ]
INSERT INTO new_table (product_id, products_head_title_tag, ...)
SELECT a.product_id, b.products_header, ...
FROM products_description a, products b
WHERE a.product_id = b.product_id
  1. INSERT INTO new_table (product_id, products_head_title_tag, ...)
  2. SELECT a.product_id, b.products_header, ...
  3. FROM products_description a, products b
  4. WHERE a.product_id = b.product_id
  • jillbalkus
  • Born
  • Born
  • jillbalkus
  • Posts: 3

Post 3+ Months Ago

No problem Battlecruiser. Thanks for looking into it for me.

Long story short, I ended up asking a friend who knows sql, and he came up with the following query which did the trick.

update products_description pd

set

pd.products_head_title_tag =

(select p.products_header

from products p

where p.products_id = pd.products_id)
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6229
  • Loc: South-Africa

Post 3+ Months Ago

[off-topic]I think these golden names are a bit hard to read because this is now the second person who has called me by my title instead of my Nick ...[/off-topic]
  • cardeo
  • Born
  • Born
  • cardeo
  • Posts: 1

Post 3+ Months Ago

I'm having the same problem as Matthieu was. I was able to run the query and isolate my email address column in phpmyadmin. However, once I export and open the .sql file, it has exported my entire database. I only want to export the email list.

Post Information

  • Total Posts in this topic: 15 posts
  • Users browsing this forum: Bill_Thompson and 69 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.