Solved

Requesting assistance to export MySQL table to excel csv via the CLI

Posted on 2013-12-26
5
382 Views
Last Modified: 2013-12-26
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
Optionally enclosed by '"'

Open in new window

is what is giving me issues.  How can this be fixed?

mysql -u root -ppassword ChangeTemplate -e "SELECT * INTO OUTFILE 'Changes.xls'  FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n' Optionally enclosed by '"' FROM ChangeTemplate.ChangeRecord;"

Open in new window


Thanks very much.
0
Comment
Question by:rleyba828
  • 3
  • 2
5 Comments
 
LVL 84

Expert Comment

by:ozo
ID: 39740999
mysql -u root -ppassword ChangeTemplate -e "SELECT * INTO OUTFILE 'Changes.xls'  FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n' Optionally enclosed by '"'"'"' FROM ChangeTemplate.ChangeRecord;"
0
 

Author Comment

by:rleyba828
ID: 39741091
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:

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

Open in new window


It seems to be really complaining about the Optionally enclosed by clause since if I remove this clause, then the command works fine.
0
 

Author Comment

by:rleyba828
ID: 39741125
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;'

Open in new window

0
 
LVL 84

Accepted Solution

by:
ozo earned 500 total points
ID: 39741129
Shouldn't the "enclosed by" be part of the FIELDS clause rather than the LINES clause?
0
 

Author Comment

by:rleyba828
ID: 39741209
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:

mysql -u root -ppassword ChangeTemplate -e "SELECT * INTO OUTFILE  'Changes.csv'  FIELDS TERMINATED BY  ','  ENCLOSED by   '"'"'"'  LINES TERMINATED BY  '\n'  FROM ChangeTemplate.ChangeRecord;"

Open in new window


Thanks very much for all your help.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now