Re: Exporting the result of a Query into excel
ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
You can use the --xml option to the mysql command to get xnl output. You can also get a tab-delimited output file that can be read into Excel (this is what I usually do). To get a tab-delimited file, you can use the --tab option of mysqldump or start up the mysql program with the -sss option and do a select on the data you want, redirecting it into an output file. On Tue, Jan 5, 2010 at 7:17 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Exporting the result of a Query into excel
Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
ishaq gbola wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server If you don't specify the absolute location it can be find in DATADIR/DatabaseName/. And after you located the file you have a multitude of choice how to transfer the file. scp,ftp,http,mail all depending on what's installed on the server and what access you got to it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
Hi What is the OS you are using on the remote and local server. Is there a mysql client on local server? Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:31:59 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
Am running windows on my local host and Redhat linux on the remote server, I have no mysql client on local host. TAC for the NMS using the database adviced me not to use a msqlclient for it, that it could break the database??? --- On Tue, 5/1/10, sureshkumar...@gmail.com sureshkumar...@gmail.com wrote: From: sureshkumar...@gmail.com sureshkumar...@gmail.com Subject: Re: Exporting the result of a Query into excel To: ishaq gbola ishaq...@yahoo.co.uk, mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:37 Hi What is the OS you are using on the remote and local server. Is there a mysql client on local server? Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:31:59 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
As test i tried this and I got the result below mysql show tables into outfile trial.txt - ; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'into outfile trial.txt' at line 1 mysql --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: Cc: mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:35 ishaq gbola wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server If you don't specify the absolute location it can be find in DATADIR/DatabaseName/. And after you located the file you have a multitude of choice how to transfer the file. scp,ftp,http,mail all depending on what's installed on the server and what access you got to it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
Ok. In windows, Use winscp tool, connect to the server and copy the file to local machine and import it in excel. Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:42:20 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Am running windows on my local host and Redhat linux on the remote server, I have no mysql client on local host. TAC for the NMS using the database adviced me not to use a msqlclient for it, that it could break the database??? --- On Tue, 5/1/10, sureshkumar...@gmail.com sureshkumar...@gmail.com wrote: From: sureshkumar...@gmail.com sureshkumar...@gmail.com Subject: Re: Exporting the result of a Query into excel To: ishaq gbola ishaq...@yahoo.co.uk, mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:37 Hi What is the OS you are using on the remote and local server. Is there a mysql client on local server? Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:31:59 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
We have to use outfile with select stmt, not with show. Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:45:44 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel As test i tried this and I got the result below mysql show tables into outfile trial.txt - ; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'into outfile trial.txt' at line 1 mysql --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: Cc: mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:35 ishaq gbola wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server If you don't specify the absolute location it can be find in DATADIR/DatabaseName/. And after you located the file you have a multitude of choice how to transfer the file. scp,ftp,http,mail all depending on what's installed on the server and what access you got to it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk
Re: Exporting the result of a Query into excel
A command to convert the table mytab in database mydb into a tab-delimited file mytab.txt might be: mysql -e'select * from mydb.mytab' -sss mytab.txt The -sss is necessary to remove all the formatting stuff that you normally have in the output of a select statement. An alternative, if you have a directory *** that mysql can write to ***: mysqldump --tab=/home/mysql/temp mydb mytab This will create 2 files in /home/mysql/temp: mytab.txt and mytab.sql. The one you'll want is in mytab.txt. Then use what transfer tool you have, ftp, scp, winscp, rsync to transfer the file to another server. On Tue, Jan 5, 2010 at 7:31 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Exporting the result of a Query into excel
TAC for the NMS using the database adviced me not to use a msqlclient for it, that it could break the database??? You are being given a false argument. If the application which connects to MySQL is running on that windows box then there already is a MySQL client on that machine, albeit a specialized one. Putting a plain old client console on that same box does not hurt anything, not even security unless you leave a text file full of passwords laying around. Having that console on the same host as the application is invaluable not only for data-dumping jobs like this one, but for all kinds of trouble shooting in general. - michael dykman On Tue, Jan 5, 2010 at 8:42 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Am running windows on my local host and Redhat linux on the remote server, I have no mysql client on local host. TAC for the NMS using the database adviced me not to use a msqlclient for it, that it could break the database??? --- On Tue, 5/1/10, sureshkumar...@gmail.com sureshkumar...@gmail.com wrote: From: sureshkumar...@gmail.com sureshkumar...@gmail.com Subject: Re: Exporting the result of a Query into excel To: ishaq gbola ishaq...@yahoo.co.uk, mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:37 Hi What is the OS you are using on the remote and local server. Is there a mysql client on local server? Thanks Suresh Kuna Sent from BlackBerry® on Airtel -Original Message- From: ishaq gbola ishaq...@yahoo.co.uk Date: Tue, 5 Jan 2010 13:31:59 To: mysql@lists.mysql.com Subject: Re: Exporting the result of a Query into excel Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
Is there any particular reason not to use the MySQL ODBC driver to import the data directly into Excel? / Carsten Jim Lyons skrev: A command to convert the table mytab in database mydb into a tab-delimited file mytab.txt might be: mysql -e'select * from mydb.mytab' -sss mytab.txt The -sss is necessary to remove all the formatting stuff that you normally have in the output of a select statement. An alternative, if you have a directory *** that mysql can write to ***: mysqldump --tab=/home/mysql/temp mydb mytab This will create 2 files in /home/mysql/temp: mytab.txt and mytab.sql. The one you'll want is in mytab.txt. Then use what transfer tool you have, ftp, scp, winscp, rsync to transfer the file to another server. On Tue, Jan 5, 2010 at 7:31 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
Carsten: Can you demonstrate this? ~Lawrence Carsten Pedersen wrote: Is there any particular reason not to use the MySQL ODBC driver to import the data directly into Excel? / Carsten Jim Lyons skrev: A command to convert the table mytab in database mydb into a tab-delimited file mytab.txt might be: mysql -e'select * from mydb.mytab' -sss mytab.txt The -sss is necessary to remove all the formatting stuff that you normally have in the output of a select statement. An alternative, if you have a directory *** that mysql can write to ***: mysqldump --tab=/home/mysql/temp mydb mytab This will create 2 files in /home/mysql/temp: mytab.txt and mytab.sql. The one you'll want is in mytab.txt. Then use what transfer tool you have, ftp, scp, winscp, rsync to transfer the file to another server. On Tue, Jan 5, 2010 at 7:31 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Exporting the result of a Query into excel
Doesn't that depend on how often you want to do it? If it's a one-time thing or an occasional thing, it's easier to just dump the table into a tab-delimited file. If it's something you want to do on a regular basis, ODBC is a way you might prefer to go. But it also would require you to have the authority on a given box to set up an ODBC connection (which might involve downloading software). You would also have to do it on every machine you want to load the data on. A tab-delimited file can be transferred to any machine, even stored on a flash-drive and walked somewhere. On Tue, Jan 5, 2010 at 1:50 PM, Carsten Pedersen cars...@bitbybit.dkwrote: Is there any particular reason not to use the MySQL ODBC driver to import the data directly into Excel? / Carsten Jim Lyons skrev: A command to convert the table mytab in database mydb into a tab-delimited file mytab.txt might be: mysql -e'select * from mydb.mytab' -sss mytab.txt The -sss is necessary to remove all the formatting stuff that you normally have in the output of a select statement. An alternative, if you have a directory *** that mysql can write to ***: mysqldump --tab=/home/mysql/temp mydb mytab This will create 2 files in /home/mysql/temp: mytab.txt and mytab.sql. The one you'll want is in mytab.txt. Then use what transfer tool you have, ftp, scp, winscp, rsync to transfer the file to another server. On Tue, Jan 5, 2010 at 7:31 AM, ishaq gbola ishaq...@yahoo.co.uk wrote: Thanks a lot for that, but where does this file get saved in and how can i copy it to my local host if the database is on a remote server --- On Tue, 5/1/10, Jay Ess li...@netrogenic.com wrote: From: Jay Ess li...@netrogenic.com Subject: Re: Exporting the result of a Query into excel To: mysql@lists.mysql.com Mysql mysql@lists.mysql.com Date: Tuesday, 5 January, 2010, 13:22 ishaq gbola wrote: Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart select * from table into outfile thefile.txt; That can be imported into excel using CSV and using TAB as separator. http://code.anjanesh.net/2007/12/export-mysql-data-to-excel-compatible.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ishaq...@yahoo.co.uk -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
RE: Exporting the result of a Query into excel
-Original Message- From: ishaq gbola [mailto:ishaq...@yahoo.co.uk] Sent: Tuesday, January 05, 2010 7:18 AM To: mysql@lists.mysql.com Subject: Exporting the result of a Query into excel Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart If'n you can download and install Toad for MySQL, then the steps might go like this: 1. Write the query 2. Run the query 3. Click the Export data to an Excel file button/icon (if you have Excel 2007 installed, it might throw a warning at you) 4. Note where the file was created to. 5. ? 6. Profit! The only caveat to the above is to be aware of how many rows you want to export and the corresponding column/row limitations of whatever version of Excel you have installed. As an additional caveat, you could export to html or csv formats as well which would allow you to work around any size limitations imposed by Excel. Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
