Exporting table data to csv file using mysqldump
I want to export table data to csv file using mysqldump.
I want to do something like:
mysqldump --compact --no_create_info --tab=testing --fields-enclosed-by=\" --fields-terminated-by=, -uroot -proot mydatabase mytable
but i keep getting this error:
(Errcode: 13) when executing 'SELECT INTO OUTFILE'
I made my test folder writable (I'm using Ubuntu as an enviornment). Can anyone explain how to export the table to a CSV file or how to change my command shell to work? Thank!
source to share
The problem with all of these answers INTO OUTFILE
or --tab=tmpfile
is that it requires running mysqldump on the same server as the MySQL server.
My solution was to simply use mysql
(NOT mysqldump
) with the parameter -B
, include the SELECT statement with -e
, and then mass the ASCII output with sed
and exit with the CSV including the header field line:
Example:
mysql -B -u username -ppassword database -h dbhost -e "SELECT * FROM accounts;" |sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"
"ID", "login", "password", "folder", "e-mail" "8", "Mariana", "57d40c8a954bc9e65f401592062019b1457be359", "Mariana", "" 3 "," squaredesign "," b01134fac01dab0765afcb955 squaredesign "," mkobylecki@squaredesign.com "" 4 "," miedziak "," 601f1889667efaebb33b8c12572835da3f027f78 "," miedziak "," miedziak@mail.com"" 5 "" Sarko "," 480225f1ac707031bae300f3f5b06dbf79ed390e "," Sarko "," "6", "Logitrans Poland", "9033b6b3c4acbb27418d8b0b26f4c880ea6dea22", "LogitransPoland ", "" 7 "" Amos "," 50f5604164db2d5ff0e984f973d2202d5358b6a6 "," Amos "," "9", "Annabelle", "07e832cb64e66fa03c13498a26a5f8e3bdebddf1", "Annabelle", "" 11 "," Brandfathers and Sons "," f08b194668249e4cb81fbb92df846e, "Imagine," "Brandf5cathers," Imagine " "e86f1645504c7d5a45ed41d16ecc39ed19181773", "ImagineGroup", "" 13 "," EduSquare.pl ","80c3c099f4ecc043a19f1527400d63588567a0ad "," EduSquare.pl "," "101", "TMM", "b01134fac01dab765bcb55ab0ca33f9ec2885a7b", "_", " WOBC-14.squareamadesign.atlassian.com "
Add > outfile.csv
at the end of this one line file to get the CSV file.
source to share
Check out this link. It shows an alternative for exporting your table to a csv file.
Export CSV directly from MySQL
I hope this is helpful.
source to share