asked on Requesting assistance to export MySQL table to excel csv via the CLI
Hi Team,
I am trying to do an export of one of my mysql tables to excel compatible csv and it works great from phpmyadmin but I want to script it via the command line. Most of the table fields are text and may contain commas, and possibly even quotes and single quote, etc. For some reason the phpmyadmin utility handles this well. I just hope that the CLI will to it correctly as well. One of the first stumbling blocks I am encountering now is that I need to include the doublequote character inside the single quote below, but I tried various variations, including escaping the double quote via '\"' but the command still won't run properly. That text below
Thanks very much.
I am trying to do an export of one of my mysql tables to excel compatible csv and it works great from phpmyadmin but I want to script it via the command line. Most of the table fields are text and may contain commas, and possibly even quotes and single quote, etc. For some reason the phpmyadmin utility handles this well. I just hope that the CLI will to it correctly as well. One of the first stumbling blocks I am encountering now is that I need to include the doublequote character inside the single quote below, but I tried various variations, including escaping the double quote via '\"' but the command still won't run properly. That text below
Optionally enclosed by '"'
mysql -u root -ppassword ChangeTemplate -e "SELECT * INTO OUTFILE 'Changes.xls' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' Optionally enclosed by '"' FROM ChangeTemplate.ChangeRecord;"
Thanks very much.
MySQL ServerLinuxShell Scripting
Last Comment
rleyba828
mysql -u root -ppassword ChangeTemplate -e "SELECT * INTO OUTFILE 'Changes.xls' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' Optionally enclosed by '"'"'"' FROM ChangeTemplate.ChangeRecor
ASKER
Hi Ozo,
Thanks for your help above. I literally did a copy and paste of your syntax and pasted it in...but I just got this error:
It seems to be really complaining about the Optionally enclosed by clause since if I remove this clause, then the command works fine.
Thanks for your help above. I literally did a copy and paste of your syntax and pasted it in...but I just got this error:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Optionally enclosed by '"' FROM ChangeTemplate.ChangeRecord' at line 1
It seems to be really complaining about the Optionally enclosed by clause since if I remove this clause, then the command works fine.
ASKER
I even tried this....but didn't work too:
mysql -u root -ppassword ChangeTemplate -e 'SELECT * INTO OUTFILE "Changes.xls" FIELDS TERMINATED BY "," LINES TERMINATED BY "\n" Optionally enclosed by """ FROM ChangeTemplate.ChangeRecord;'
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Ozo,
You are right. I rearranged the clauses so it now reads as below, and following your original suggestion, the new command below now works fine:
Thanks very much for all your help.
You are right. I rearranged the clauses so it now reads as below, and following your original suggestion, the new command below now works fine:
mysql -u root -ppassword ChangeTemplate -e "SELECT * INTO OUTFILE 'Changes.csv' FIELDS TERMINATED BY ',' ENCLOSED by '"'"'"' LINES TERMINATED BY '\n' FROM ChangeTemplate.ChangeRecord;"
Thanks very much for all your help.