Export table of 50 columns

Shell Programming and Scripting
1

Dear all,

I have a big table of 50 columns and more then 100, 000 rows in mysql. Could you please help me that how I can export it as .csv or . txt that I can open it in MS excel?????
OR how can I export the specific columns of the table???

Thanks

AAWT

2

One of the first hits when entering "mysql export csv" in Google brought this:
MySQL :: Re: Export to CSV

When importing to Excel, you will have to specify the same file delimeter that you have chosen when exporting.
Additionally it would be good to read up on the SELECT -statement.

3 
mysql -u username -ppassword instancename -e "select id,name from table;" |awk '{print$1","$2}'

The above will export those two columns from that table and then put a comma between each one. You can modify it to fit your column names or you can do a

select *
1 Like
4

:wink: oh I am getting the problem with these
from google link,,,,,,

after opening in excel one row is coming in two rows,,,,,,,means last three columns data is in 2nd row,

and Brianjb,,,,
I modify and use select * and it export only 2 columns,,,,then I add the $1----$26 and I got the error

awk: {print$1","$2,"$3,"$4,"$5,"$6,"$7, "$8, "$9,"$10,"11$,"$12,"$13,"$14,"$15,"$16,"$17, "$18$,"$19,"$20,"$21,"$22,"$23,"$24,"$25,"$26}
awk:                                                                                           ^ syntax error

So,,,,,looking for help

5

Add this to the end:

awk '{print$1","$2","$3","$4","$5","$6","$7","$8","$9","$10","$11","$12","$13","$14","$15","$16","$17","$18$","$19","$20","$21","$22","$23","$24","$25","$26}'

Notice that there are double quotes around each comma. That is telling awk to print what is in the double quotes. Then the $ means to print that number of field.

---------- Post updated at 07:37 AM ---------- Previous update was at 07:34 AM ----------

aawt,

If you do this:

I modify and use select * and it export only 2 columns

That should export every column in that table.

Do this command:

mysql -u username -ppassword instancename -e "show columns from tablename"

where the tablename is the name of the table that you only got two columns exported. It is possible that you are trying to export the wrong table.

1 Like
6

I'm not sure about the switch for CSV, but it was there and most probably it is still there to export in CSV format...no need to do a piped processing..

---------- Post updated at 07:24 PM ---------- Previous update was at 07:21 PM ----------

SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products

for your reference... please use
Export data to CSV from MySQL