Exporting Specific Columns,Specific Rows from a Specific Table of a Specific Database in Mysql

Question

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.

user4035 · Accepted Answer · 2013-05-14 07:01:15Z

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, ...);

answered May 14, 2013 at 7:01

user4035

24k11 gold badges69 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ashish mittal Over a year ago

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 : stackoverflow.com/questions/10762239/…

scotru · Accepted Answer · 2013-05-14 07:05:42Z

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.

edited May 14, 2013 at 7:05

answered May 14, 2013 at 6:53

scotru

2,60621 silver badges39 bronze badges

2 Comments

Algorithmist Over a year ago

I am getting this message while running above query Error Code: 1045. Access denied for user 'XXXXX'@'%' (using password: YES)

scotru Over a year ago

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.

Collectives™ on Stack Overflow

Exporting Specific Columns,Specific Rows from a Specific Table of a Specific Database in Mysql

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related