Exporting a MySQL table into a CSV file

Question

I have a MySQL table which has to be taken out as a CSV file. The query I used is

SELECT "ID","NAME","SALARY","SAL1","SAL2","SAL3","SAL4","SAL5","SAL6","SAL7","SAL8","SAL9","SAL10","SAL11","SAL12","SAL13","SAL14","SAL15","SAL16","SAL17","SAL18","SAL19","SAL20","SAL21","SAL22","SAL23","SAL24","SAL25","SAL26"
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE "E:\\JOSE DATA\\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This query works, but what if I have 200 column names? Is there a way to do it without manually typing it?

possible duplicate of [MySQL dump into CSV text files with column names at the top?](http://stackoverflow.com/questions/4589891/mysql-dump-into-csv-text-files-with-column-names-at-the-top) — Syed Aslam, Apr 01 '14 at 11:12
mysqldump with --tab should be the easy way here with no additional scripting requirements. I wrote [3 ways to convert MySQL to CSV](http://kedar.nitty-witty.com/blog/export-mysql-database-table-to-csv-delimited-excel-file) in detail. — mysql_user, Jan 30 '18 at 12:15

score 73 · Answer 1 · edited Jun 09 '20 at 14:09

73

This command almost gives you what you want, and it even works with a remote server. The only caveat is that it generates a TSV file (fields are separated by a tab).

mysql mydb -e "select * from mytable" -B > mytable.tsv

But you could convert it to CSV using sed, as suggested in this answer:

mysql mydb -e "select * from mytable" -B | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > mytable.csv

edited Jun 09 '20 at 14:09

Peter Mortensen

30,738
21
105
131

answered Mar 06 '15 at 10:57

Rems

4,837
3
27
24

Can i use where clause? – Lnux Jul 09 '16 at 04:50
@Lnux yes you can use a where clause to narrow down the search, everything in the first quoted area with the select, is a normal database query. – Caperneoignis Jul 21 '16 at 18:59
1

mysql -u root -p mydb -e "select * from mytable" -B | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > mytable.csv - This worked fine for me, thank you – Thara Perera Sep 23 '16 at 07:50
Can you help me modify this to use multiple tables in a bash script to append to the output file? – Tony-Caffe Dec 13 '16 at 18:43
can we import this exported tsv ? – Lakshmikant Deshpande Nov 01 '17 at 10:47

score 5 · Answer 2 · edited Jun 09 '20 at 11:43

DESCRIBE addstock25;

Strip the first column and the first three entries of that column (it depends on your usage). You will get the list of fields in addstock25.

This will bring only field names using virtual tables derived in core... called information schema.

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_NAME`='foo';

Let’s say the name of this query would be sq_fieldnamelist.

So, the above table has one column and it has the field names of the "foo" table.

If directly writing like

SELECT (sq_fieldnamelist)
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE "E:\\JOSE DATA\\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n

MySQL will give an error. "subquery returns multiple rows"

We must edit sq_fieldnamelist to concatenate all entries back to back, separated with commas.

Select GROUP_CONCAT(COLUMN_NAME)
FROM
(SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_NAME`='ffd_companies'
LIMIT 3,100
) AS fafa
GROUP BY 'COLUMN_NAME' // This group by is just to make group concatenation work

Let's say this is sq_fieldnamelist2.

If we edit sq_fieldnamelist like this, it will return only one value which is all field names separated with commas. So now we can put this subquery in your select statement to acquire the needed fields.

SELECT (sq_fieldnamelist2)
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE "E:\\JOSE DATA\\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n

You need to edit LIMIT 3,100 in sq_fieldnamelist2 for you own purpose.

Let's say your table is like fil1, fil2...filN, sal1, sal2, sal3...., salI. To see the only salary fields, you should use LIMIT N, x > I+N. If you want to see all, use LIMIT 0, x > N+I.

I could'nt understand what you are explaining .... What i want is the csv file exported from MySQL with column names in first line . — user3304713, Apr 01 '14 at 10:51

score 4 · Answer 3 · edited Dec 12 '19 at 17:13

4

I'm not seeing why you can't do

SELECT *
FROM addstock25
INTO OUTFILE "E:\\JOSE DATA\\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

edited Dec 12 '19 at 17:13

Giacomo1968

25,759
11
71
103

answered Apr 01 '14 at 10:44

pacifist

712
4
13

5

If i do so, I dont get the column names... i have 200 columns so how can i export it with column names ? – user3304713 Apr 01 '14 at 10:47

score 1 · Answer 4 · edited Jun 09 '20 at 11:44

1

I think you are looking for something like this.

SET @sql = NULL;
SELECT GROUP_CONCAT("'",COLUMN_NAME,"'")
 FROM
(SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'yourdatabasename'
and `TABLE_NAME`='ffd_companies'
) AS colnames
GROUP BY 'COLUMN_NAME'
into @sql;

SET @sql = concat ("SELECT", @sql, " from dual
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE 'E:\\JOSE DATA\\addstock7.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '", '"',"'
)"
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Note that I added the WHERE clause TABLE_SCHEMA = 'yourdatabasename'.

edited Jun 09 '20 at 11:44

Peter Mortensen

30,738
21
105
131

answered Apr 02 '14 at 02:45

Oscar Rovira

46
5

Its giving out an error message.... I changed the database name .... Any other changes to be made ? – user3304713 Apr 02 '14 at 03:53
Please tray this and tel me if its working. SET @sql = NULL; SELECT GROUP_CONCAT("'", COLUMN_NAME, "'") FROM( SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = 'yourdatabase' AND `TABLE_NAME` = 'ffd_companies') AS colnames GROUP BY 'COLUMN_NAME' INTO @sql; SET @sql = concat ("SELECT", @sql, " from dual union all( SELECT * from ffd_companies )" ); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; – Oscar Rovira Apr 02 '14 at 13:30

score 0 · Answer 5 · answered Jun 10 '21 at 07:39

0

Can use 'Table Data Export Wizard' and from there can copy the select SQL;

answered Jun 10 '21 at 07:39

Bhabani

1
2

Exporting a MySQL table into a CSV file

5 Answers5

Linked