1

I need to export only subset of columns from a very large table containing large number of columns.Also this table contains million of rows so i want to export only specific rows from this table.

I have recently started using Mysql earlier i was working on Oracle.

Algorithmist
  • 6,657
  • 7
  • 35
  • 49

2 Answers2

1

What format do you need the data in? You could get a CSV using a query. For example

SELECT column1,column2,column3,... FROM table WHERE column1=criteria1,....
INTO OUTFILE '/tmp/output.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

An administration tool like phpMyAdmin (http://www.phpmyadmin.net/) could also be used to run the query and then export the results in a variety of formats.

scotru
  • 2,563
  • 20
  • 37
  • I am getting this message while running above query Error Code: 1045. Access denied for user 'XXXXX'@'%' (using password: YES) – Algorithmist May 14 '13 at 07:08
  • MySQL permissions problem--the MySQL user doesn't have rights to execute the query against the database. You may need to grant rights for the user coming from a particular IP or from '%' which is the wildcard that means any IP. – scotru May 15 '13 at 08:36
1

This worked for me:

mysql -u USERNAME --password=PASSWORD --database=DATABASE \
--execute='SELECT `field_1`, `field_2` FROM `table_name`' -X > file.xml

And then importing the file, using command:

LOAD XML LOCAL INFILE '/pathtofile/file.xml' 
INTO TABLE table_name(field_1, field_2, ...);
user4035
  • 22,508
  • 11
  • 59
  • 94
  • This is very helpful to import specific column from a table.But in mysql by default load did not enable.So we have to enable this to use properly. To enable load in mysql please use this : http://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile – Ashish mittal Dec 15 '15 at 13:51