Generating reports/exporting data?
Can anyone recommend an easy, free way to generate reports from a MySQL database in a nicely readable form? e.g. in plain text, HTML, PDF, or even visualised in the form of graphs and charts. For example, if I have constructed several queries and want to run them and export all of their results to a file. Or I have a query that runs over one row of a table by specifying a specific primary key value in the query and I want to run it on each row of the table by incrementing the primary key and then compute totals and averages on the results. In general I want to be able to run lots of queries, do simple calculations on their results, and display all these results alongside each other in one file. I'm aware that the mysql client can run in batch mode and has HTML and XML output options, but this is a little too simple for what I want. Currently I'm using Python's MySQLdb module and rolling my own scripts, but I was hoping there would be something a little faster. I started writing Python scripts to take user input from the keyboard and pass it to mysql, but then I discovered mysqlimport which was much quicker, so I'm hoping something similar exists for outputting reports. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Generating reports/exporting data?
It's not free in that you have to buy the product, but it is free in that you make ONE report and get multiple export modules that you don't have to write yourself. That alone is awesome! http://www.jaspersoft.com :) -Original Message- From: chombee [mailto:[EMAIL PROTECTED] Sent: Monday, September 17, 2007 8:29 AM To: mysql@lists.mysql.com Subject: Generating reports/exporting data? Can anyone recommend an easy, free way to generate reports from a MySQL database in a nicely readable form? e.g. in plain text, HTML, PDF, or even visualised in the form of graphs and charts. For example, if I have constructed several queries and want to run them and export all of their results to a file. Or I have a query that runs over one row of a table by specifying a specific primary key value in the query and I want to run it on each row of the table by incrementing the primary key and then compute totals and averages on the results. In general I want to be able to run lots of queries, do simple calculations on their results, and display all these results alongside each other in one file. I'm aware that the mysql client can run in batch mode and has HTML and XML output options, but this is a little too simple for what I want. Currently I'm using Python's MySQLdb module and rolling my own scripts, but I was hoping there would be something a little faster. I started writing Python scripts to take user input from the keyboard and pass it to mysql, but then I discovered mysqlimport which was much quicker, so I'm hoping something similar exists for outputting reports. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Generating reports/exporting data?
Jasper does have a commercial offering, but it is still an open source project.. (readers of this list may be familiar with the arrangement) I'm not sure what license they are using these days (used to be GPL) but the web site is a little counter-intuitive. - michael On 9/17/07, Daevid Vincent [EMAIL PROTECTED] wrote: It's not free in that you have to buy the product, but it is free in that you make ONE report and get multiple export modules that you don't have to write yourself. That alone is awesome! http://www.jaspersoft.com :) -Original Message- From: chombee [mailto:[EMAIL PROTECTED] Sent: Monday, September 17, 2007 8:29 AM To: mysql@lists.mysql.com Subject: Generating reports/exporting data? Can anyone recommend an easy, free way to generate reports from a MySQL database in a nicely readable form? e.g. in plain text, HTML, PDF, or even visualised in the form of graphs and charts. For example, if I have constructed several queries and want to run them and export all of their results to a file. Or I have a query that runs over one row of a table by specifying a specific primary key value in the query and I want to run it on each row of the table by incrementing the primary key and then compute totals and averages on the results. In general I want to be able to run lots of queries, do simple calculations on their results, and display all these results alongside each other in one file. I'm aware that the mysql client can run in batch mode and has HTML and XML output options, but this is a little too simple for what I want. Currently I'm using Python's MySQLdb module and rolling my own scripts, but I was hoping there would be something a little faster. I started writing Python scripts to take user input from the keyboard and pass it to mysql, but then I discovered mysqlimport which was much quicker, so I'm hoping something similar exists for outputting reports. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exporting Data To File and Can't Create/Write to File
I am doing the following: echo 'SELECT * FROM jobs WHERE id = 1 INTO OUTFILE /Users/jkopanas/Sites/test_this_out.csv;' | mysql -uroot backlog_development And I get the following error: ERROR 1 (HY000) at line 1: Can't create/write to file '/Users/jkopanas/Sites/test_this_out.csv' (Errcode: 13) I found this on mysql: http://dev.mysql.com/doc/refman/5.0/en/cannot-create.html I checked to see if root had File_priv and he/she does. I am developing this on Mac OS X but I am deploying on a linux box. Any input? :-) -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting Data To File and Can't Create/Write to File
Hi, John Kopanas wrote: I am doing the following: echo 'SELECT * FROM jobs WHERE id = 1 INTO OUTFILE /Users/jkopanas/Sites/test_this_out.csv;' | mysql -uroot backlog_development And I get the following error: ERROR 1 (HY000) at line 1: Can't create/write to file '/Users/jkopanas/Sites/test_this_out.csv' (Errcode: 13) I found this on mysql: http://dev.mysql.com/doc/refman/5.0/en/cannot-create.html I checked to see if root had File_priv and he/she does. I am developing this on Mac OS X but I am deploying on a linux box. Does /Users/jkopanas/Sites/ exist? Does the user MySQL runs as have permission to write to it? Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting Data To File and Can't Create/Write to File
Directory existed but I did not have the right permissions on the directory for the mysql user. Thanks my friend. :-) On 5/28/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, John Kopanas wrote: I am doing the following: echo 'SELECT * FROM jobs WHERE id = 1 INTO OUTFILE /Users/jkopanas/Sites/test_this_out.csv;' | mysql -uroot backlog_development And I get the following error: ERROR 1 (HY000) at line 1: Can't create/write to file '/Users/jkopanas/Sites/test_this_out.csv' (Errcode: 13) I found this on mysql: http://dev.mysql.com/doc/refman/5.0/en/cannot-create.html I checked to see if root had File_priv and he/she does. I am developing this on Mac OS X but I am deploying on a linux box. Does /Users/jkopanas/Sites/ exist? Does the user MySQL runs as have permission to write to it? Baron -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
importing, exporting data in IIF format (for example quickbooks)
Dear friends, one of our customer is asking to integrate a mysql application with an application called Quickbooks (accounting package, www.quickbooks.co.uk). Apparrently the application can only import export files in the format IIF. Has anybody any expereince in importing, exporting to this format with Mysql? Any help would be much appreciated. Best regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Exporting Data via Command Line
Hello Guys, I am a newbie to mySQL and im trying to export my database via the mySQL Command line but i cant seem to find any good documentation on how to do this. More articles are telling me to use phpAdmin or some other gui tool. I would like to learn the commands anyone has a good doc on this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting Data via Command Line
http://www.mysql.com would be a nice place to start. On Fri, 04 Mar 2005 13:12:02 -0500, MrExecutive [EMAIL PROTECTED] wrote: Hello Guys, I am a newbie to mySQL and im trying to export my database via the mySQL Command line but i cant seem to find any good documentation on how to do this. More articles are telling me to use phpAdmin or some other gui tool. I would like to learn the commands anyone has a good doc on this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Power to people, Linux is here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting Data via Command Line
run: mysqldump --help On Fri, 04 Mar 2005 13:12:02 -0500, MrExecutive [EMAIL PROTECTED] wrote: Hello Guys, I am a newbie to mySQL and im trying to export my database via the mySQL Command line but i cant seem to find any good documentation on how to do this. More articles are telling me to use phpAdmin or some other gui tool. I would like to learn the commands anyone has a good doc on this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting Data via Command Line
i'll usually pipe mysqldump into gzip if it's a large database, or gpg if there's sensitive data in it. -jacob On Fri, 4 Mar 2005 12:22:08 -0600, jacob martinson [EMAIL PROTECTED] wrote: run: mysqldump --help On Fri, 04 Mar 2005 13:12:02 -0500, MrExecutive [EMAIL PROTECTED] wrote: Hello Guys, I am a newbie to mySQL and im trying to export my database via the mySQL Command line but i cant seem to find any good documentation on how to do this. More articles are telling me to use phpAdmin or some other gui tool. I would like to learn the commands anyone has a good doc on this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Exporting Data via Command Line
MrExecutive [EMAIL PROTECTED] wrote on 03/04/2005 01:12:02 PM: Hello Guys, I am a newbie to mySQL and im trying to export my database via the mySQL Command line but i cant seem to find any good documentation on how to do this. More articles are telling me to use phpAdmin or some other gui tool. I would like to learn the commands anyone has a good doc on this? Thanks Try reading up on these commands: (from a command shell prompt) mysqldump http://dev.mysql.com/doc/mysql/en/mysqldump.html (from within a MySQL client) SELECT ... INTO OUTFILE http://dev.mysql.com/doc/mysql/en/select.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Exporting Data via Command Line
Lets not forget about mysqlhotcopy. -Eric On Fri, 4 Mar 2005 13:42:48 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: MrExecutive [EMAIL PROTECTED] wrote on 03/04/2005 01:12:02 PM: Hello Guys, I am a newbie to mySQL and im trying to export my database via the mySQL Command line but i cant seem to find any good documentation on how to do this. More articles are telling me to use phpAdmin or some other gui tool. I would like to learn the commands anyone has a good doc on this? Thanks Try reading up on these commands: (from a command shell prompt) mysqldump http://dev.mysql.com/doc/mysql/en/mysqldump.html (from within a MySQL client) SELECT ... INTO OUTFILE http://dev.mysql.com/doc/mysql/en/select.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exporting data
I have an order taking system where the tables are store in a MySql database. I need to develop a select statement to output all new orders to a .csv formatted file. Is this possible to do in MySql. I would try looking this up, however, I am not even sure what to look for in the documentation. Any help that I could get to lead me in the right direction would be appreciated. Thanks. Mark Roberts
Re: Exporting data
On Mon, 15 Dec 2003, Roberts, Mark (Tulsa) wrote: I have an order taking system where the tables are store in a MySql database. I need to develop a select statement to output all new orders to a .csv formatted file. Is this possible to do in MySql. I would try looking this up, however, I am not even sure what to look for in the documentation. Any help that I could get to lead me in the right direction would be appreciated. Thanks. Mark Roberts http://www.mysql.com/doc/en/SELECT.html Check out the bit about INTO OUTFILE. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exporting Data
How would you export your data from the mysql tables to a tab or coma delimiter. Thank you, Stephen A. Lewis [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Exporting Data
How would you export your data from the mysql tables to a tab or coma delimiter. There are lots of ways, here are my favorite two: mysqldump: http://www.mysql.com/doc/en/mysqldump.html phpMyAdmin : http://phpmyadmin.sourceforge.net/ -- Greg Donald http://destiney.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
exporting data
Hi guys I know that you can import data into a table by using 'load data infile' or 'mysql' import. however, is there something that allows you to export the create statemtns for the table schema and the data of a database for backup. SO that if we reinstall the whole thing from scratch all we need are the exported files that will recreate the whole database from the time we exported? Thanks Desmond _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: exporting data
On Fri, Aug 02, 2002 at 03:55:52PM -0700, Desmond Lee wrote: Hi guys I know that you can import data into a table by using 'load data infile' or 'mysql' import. however, is there something that allows you to export the create statemtns for the table schema and the data of a database for backup. SO that if we reinstall the whole thing from scratch all we need are the exported files that will recreate the whole database from the time we exported? mysqldump will do it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 64 days, processed 1,352,277,811 queries (240/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: exporting data
Do you mean like 'mysqldump'? You can use mysqldump to dump tables +/- data and then upload it to a database later--or 'import' it, e.g., mysqldump -u user_name database all the tables from that database. mysqldump takes a variety of arguments. see 'man mysqldump' for more info. -mj Desmond Lee wrote: Hi guys I know that you can import data into a table by using 'load data infile' or 'mysql' import. however, is there something that allows you to export the create statemtns for the table schema and the data of a database for backup. SO that if we reinstall the whole thing from scratch all we need are the exported files that will recreate the whole database from the time we exported? Thanks Desmond _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: exporting data
Thanks Jeremy :) From: Jeremy Zawodny [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Desmond Lee [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: exporting data Date: Fri, 2 Aug 2002 15:59:01 -0700 On Fri, Aug 02, 2002 at 03:55:52PM -0700, Desmond Lee wrote: Hi guys I know that you can import data into a table by using 'load data infile' or 'mysql' import. however, is there something that allows you to export the create statemtns for the table schema and the data of a database for backup. SO that if we reinstall the whole thing from scratch all we need are the exported files that will recreate the whole database from the time we exported? mysqldump will do it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 64 days, processed 1,352,277,811 queries (240/sec. avg) _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Exporting Data from web page
In simple terms I need to be able to click a link on a webpage which runs some SQL to produce a result set and then prompts the user to save this as a pipe delimited file onto their HDD. Or which does the saving to HDD without the prompt but it still needs to be triggered from the webpage. Can't find any info online, in lists or in my MYSQL book. Regards Dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Exporting Data from web page
Hi Dave, This won't have purely SQL only solution , you can use SELECT .. INTO OUTFILE .. syntax to create a file and you can zip the file with a random name and link to that zip file.. This will force the explorer to save the file. If you just let it stay as a text file, the browser won't download it, it will just display, but there might be a way to force the browser download the file by defining a MIME TYPE even if the file is in text format. You may want to make a search on MIME TYPES and see what you come up with.. For the SELECT .. INTO OUTFILE.. syntax, see: http://www.mysql.com/doc/S/E/SELECT.html Gurhan -Original Message- From: Dave [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 07, 2002 7:38 AM To: [EMAIL PROTECTED] Subject: Exporting Data from web page In simple terms I need to be able to click a link on a webpage which runs some SQL to produce a result set and then prompts the user to save this as a pipe delimited file onto their HDD. Or which does the saving to HDD without the prompt but it still needs to be triggered from the webpage. Can't find any info online, in lists or in my MYSQL book. Regards Dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: exporting data to a new DB
Hello everyone, I need to export some data from one DB to another. Suppose I'm connected to DB1 and my trying to get stuff from there and insert into DB2. I'm using Insert Select statements. Once I'm connected to DB1, do I have to establish separate connection for DB2? An example Insert Select : Insert into db2.Pad_Analysis_Data (column names) Select * from db1.Pad_Analysis_Data; Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 1:41 PM To: Demirchyan Oganes-AOD098 Subject: Re: exporting data to a new DB Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: database,sql,query,table If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Hello everyone, I need to export some data from one DB to another. Suppose I'm connected to DB1 and my trying to get stuff from there and insert into DB2. I'm using Insert Select statements. Once I'm connected to DB1, do I have to establish separate connection for DB2? An example Insert Select : Insert into db2.Pad_Analysis_Data (column names) Select * from db1.Pad_Analysis_Data; Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: exporting data to a new DB
At 15:48 -0600 1/29/02, Demirchyan Oganes-AOD098 wrote: Hello everyone, I need to export some data from one DB to another. Suppose I'm connected to DB1 and my trying to get stuff from there and insert into DB2. I'm using Insert Select statements. Once I'm connected to DB1, do I have to establish separate connection for DB2? Not if they're both hosted by the same MySQL server. And if they're not, you can't do this anyway. :-) An example Insert Select : Insert into db2.Pad_Analysis_Data (column names) Select * from db1.Pad_Analysis_Data; Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Exporting Data
Hi. On Mon, Nov 19, 2001 at 11:32:58PM -0600, [EMAIL PROTECTED] wrote: Hello, Sorry about the OT, but I need to export data from various MySQL tables and import into Excell spread sheets. Was hoping someone could suggest the best format, TAB, CSV..etc delimited, which is the preferred format? Since Excel can read handle the listed formats and MySQL can export all of them, I see nor preference. I would use CSV because that is IMHO most common for transferring database content to non-SQL applications (and will also work if you need it in another app than Excel). mysqldump will do the work. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Exporting Data
You can export using SELECT fieldlist INTO OUTFILE filename FROM table; This will create a file with tab separated fields. It is Excel default separator. Within Excel, you can open the file, by giving filetype text, and a wizard will guide you through the translation. You can create a CSV file by adding , at the end of the SQL statement, FIELDS TERMINATED BY ',' ENCLOSED BY '' Giuseppe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Exporting Data
Cancel this, I found the problem. Had to grant FILE privileges to this user! total forgot about that! TIA, On Tue, 20 Nov 2001 10:14:52 +0100, Giuseppe Maxia [EMAIL PROTECTED] wrote: You can export using SELECT fieldlist INTO OUTFILE filename FROM table; This will create a file with tab separated fields. It is Excel default separator. Within Excel, you can open the file, by giving filetype text, and a wizard will guide you through the translation. You can create a CSV file by adding , at the end of the SQL statement, FIELDS TERMINATED BY ',' ENCLOSED BY '' thanks, that this should work perfectly. one other question regarding the select .. into outfile.. this is the query being used: SELECT * INTO OUTFILE /home/tracerta/tmp/producd_id.txt FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n' FROM product_id; Now if I log into mysql under the client's username password, which has full privilegs on the database being used here: 'tracers' it will not allow this query to be executed, I keep getting the error, access denied for tracerta@localhost using password (yes). But if I log into mysql under 'root', then I can execute the query with no problems. The OUTFILE is in the client's account on the server, the 'tmp' folder is chmoded 0777 and owned by the client 'tracerta' Is there a special privilege setting for selecting into outfile ?? Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Exporting Data
Hello, Sorry about the OT, but I need to export data from various MySQL tables and import into Excell spread sheets. Was hoping someone could suggest the best format, TAB, CSV..etc delimited, which is the preferred format? thanks, mysql database Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
EXPORTING DATA: OK, this might be a bit general but..
I need some advice on how to export a tables data to import later into Access. This export HAS to be automated (and I'm using VC++ API for MySQL) The problems I'm faced with are (1) Before I can import this data into access I have to create the table in access; I'm familiar with ADO and creating both tables and databases automatically in a C++ proggie, but how can I find out the tables structure - I suppose I can issue a query - DESCRIBE thistable; (2) Adding records to access (ADO will have methods to do this thought I would have thought). (3) The biggest problem I can see is getting the data from a mysql server (where-ever it may be) to reside locally (the computer issuing the command) so that I can then reference it and add it to an access database through ADO. How can I get this data (effectively a select * from to table) to reside on the local machine. Ideally what I want is export * from table to file.txt - I know I'm wishing on a star here. What's the easiest way to do this thought? How can I use a SELECT to send the output to a file?? (4) I will need to insert data into MySQL. I've learnt that I can do this with a LOAD DATA ... But is this issued in the same way as a query is sent to the server. It is essential to add data through the mysql server not an add-on program shipped with MySQL. I have a problem that I don't want the overhead of ODBC, or the reliance on other programs bundled with MySQL because the host I'm connecting to could be Windows, Linux, Unix Sparc etc... Have a think and I'll appreciate any input you have to offer. Please bear in mind that all operations MUST BE AUTOMATED. Thank you for your time. --- Thanks lots David Ayliffe ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: EXPORTING DATA: OK, this might be a bit general but..
I'm by no means an authority on this kind of stuff but you could write a portable C program that generates a big ol' SQL script containing INSERT INTO statements :) I can't really think of a fast way to do it since you want to go between MySQL and Access without ODBC and other utilities (I'm not even sure if there is a bulk copy that would work between the two?). That's my two cents worth anyway, Erik David Ayliffe wrote: I need some advice on how to export a tables data to import later into Access. This export HAS to be automated (and I'm using VC++ API for MySQL) The problems I'm faced with are (1) Before I can import this data into access I have to create the table in access; I'm familiar with ADO and creating both tables and databases automatically in a C++ proggie, but how can I find out the tables structure - I suppose I can issue a query - DESCRIBE thistable; (2) Adding records to access (ADO will have methods to do this thought I would have thought). (3) The biggest problem I can see is getting the data from a mysql server (where-ever it may be) to reside locally (the computer issuing the command) so that I can then reference it and add it to an access database through ADO. How can I get this data (effectively a select * from to table) to reside on the local machine. Ideally what I want is export * from table to file.txt - I know I'm wishing on a star here. What's the easiest way to do this thought? How can I use a SELECT to send the output to a file?? (4) I will need to insert data into MySQL. I've learnt that I can do this with a LOAD DATA ... But is this issued in the same way as a query is sent to the server. It is essential to add data through the mysql server not an add-on program shipped with MySQL. I have a problem that I don't want the overhead of ODBC, or the reliance on other programs bundled with MySQL because the host I'm connecting to could be Windows, Linux, Unix Sparc etc... Have a think and I'll appreciate any input you have to offer. Please bear in mind that all operations MUST BE AUTOMATED. Thank you for your time. --- Thanks lots David Ayliffe ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Exporting data from a MySQL database to an oracle database
Hi, I have a MySql database with customer information which is to be exported to an oracle database. I was wondering if someone could tell me the easiest way of doing this. Is there any such applications which look after the exporting of the data from the MySql database and importing to the new oracle database. Or even how to export the data from the MySql database to a flat file. Kind Regards Joe O'Toole - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Exporting data from a MySQL database to an oracle database
Joe.O'[EMAIL PROTECTED] (Joe.O'[EMAIL PROTECTED]) writes: I have a MySql database with customer information which is to be exported to an oracle database. I was wondering if someone could tell me the easiest way of doing this. Is there any such applications which look after the exporting of the data from the MySql database and importing to the new oracle database. Or even how to export the data from the MySql database to a flat file. Did you even bother to search the manual before you posted? Before posting, please check: http://www.mysql.com/manual.php (the manual) Search the manual for "mysqldump". -- Ed Carp, N7EKG [EMAIL PROTECTED] 940/367-2744 cell phone http://www.pobox.com/~erc [EMAIL PROTECTED] - text pager I sometimes wonder if the American people deserve to be free - they seem so unwilling to fight to preserve the few freedoms they have left. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: Exporting data from MySQL to Oracle
You could use mysqldump which makes CREATE/INSERT statements for mysql, and then you can change the mysql-spesific things like auto_icrement into making an sequence, etc. Oracle is SQL92 compliant and it would work i think. try it :o) mvh: P?l Wester, programmerer never.no as, stortingsgt 30, 0161 Oslo tlf: 22 01 66 20, fax: 22 01 66 21 direkte: 22 01 66 34, 906 900 62 http://never.no - icq:103476059 ~ f u cn rd ts u prb a gk ~ -Opprinnelig melding- Fra: Denis Mettler [mailto:[EMAIL PROTECTED]] Sendt: 13. februar 2001 22:02 Til: Noor Dawod Kopi: [EMAIL PROTECTED] Emne: AW: Exporting data from MySQL to Oracle Hi there, please check the oracle migration bench at the oracle technet. http://technet.oracle.com kind regards denis mettler -Urspr?ngliche Nachricht- Von: Noor Dawod [mailto:[EMAIL PROTECTED]] Gesendet: Dienstag, 13. Februar 2001 17:23 An: Cal Evans; MySQL List Betreff: RE: Exporting data from MySQL to Oracle I couldn't find such product on Oracle's website. Noor -Original Message- From: Cal Evans [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 13, 2001 5:51 PM To: Noor Dawod; MySQL List Subject: RE: Exporting data from MySQL to Oracle Didn't Oracle just release a MySQL to Oracle migration kit? Check the Oracle website. Cal http://www.calevans.com -Original Message- From: Noor Dawod [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 13, 2001 8:41 AM To: MySQL List Subject: Exporting data from MySQL to Oracle Hello, I'm having a problem exporting MySQL BLOB's to Oracle, especially those records that has "Carriage Return" or "Line Feed" characters in them. Has anyone done this before, and how did they do it? I need an immediate help in this subject. I'd appreciate you help very much. Thanks Noor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Exporting data from MySQL to Oracle
Hello, I'm having a problem exporting MySQL BLOB's to Oracle, especially those records that has "Carriage Return" or "Line Feed" characters in them. Has anyone done this before, and how did they do it? I need an immediate help in this subject. I'd appreciate you help very much. Thanks Noor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Exporting data from MySQL to Oracle
Didn't Oracle just release a MySQL to Oracle migration kit? Check the Oracle website. Cal http://www.calevans.com -Original Message- From: Noor Dawod [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 13, 2001 8:41 AM To: MySQL List Subject: Exporting data from MySQL to Oracle Hello, I'm having a problem exporting MySQL BLOB's to Oracle, especially those records that has "Carriage Return" or "Line Feed" characters in them. Has anyone done this before, and how did they do it? I need an immediate help in this subject. I'd appreciate you help very much. Thanks Noor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Exporting data from MySQL to Oracle
I couldn't find such product on Oracle's website. Noor -Original Message- From: Cal Evans [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 13, 2001 5:51 PM To: Noor Dawod; MySQL List Subject: RE: Exporting data from MySQL to Oracle Didn't Oracle just release a MySQL to Oracle migration kit? Check the Oracle website. Cal http://www.calevans.com -Original Message- From: Noor Dawod [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 13, 2001 8:41 AM To: MySQL List Subject: Exporting data from MySQL to Oracle Hello, I'm having a problem exporting MySQL BLOB's to Oracle, especially those records that has "Carriage Return" or "Line Feed" characters in them. Has anyone done this before, and how did they do it? I need an immediate help in this subject. I'd appreciate you help very much. Thanks Noor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Exporting data from MySQL to Oracle
Hi there, please check the oracle migration bench at the oracle technet. http://technet.oracle.com kind regards denis mettler -Urspr?ngliche Nachricht- Von: Noor Dawod [mailto:[EMAIL PROTECTED]] Gesendet: Dienstag, 13. Februar 2001 17:23 An: Cal Evans; MySQL List Betreff: RE: Exporting data from MySQL to Oracle I couldn't find such product on Oracle's website. Noor -Original Message- From: Cal Evans [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 13, 2001 5:51 PM To: Noor Dawod; MySQL List Subject: RE: Exporting data from MySQL to Oracle Didn't Oracle just release a MySQL to Oracle migration kit? Check the Oracle website. Cal http://www.calevans.com -Original Message- From: Noor Dawod [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 13, 2001 8:41 AM To: MySQL List Subject: Exporting data from MySQL to Oracle Hello, I'm having a problem exporting MySQL BLOB's to Oracle, especially those records that has "Carriage Return" or "Line Feed" characters in them. Has anyone done this before, and how did they do it? I need an immediate help in this subject. I'd appreciate you help very much. Thanks Noor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Exporting data
How do I dump all the contents of a table called "clients" into a .txt file (or anyother kind of file for that matter). Thanks for your help. Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Exporting data
Hello, if you want to have a file with just the bare data contents of the table, you simply do: mysql SELECT * FROM table_name INTO OUTFILE '/tmp/lala.txt'; otherwise if you want your data represented in SQL, so that you can reconstruct the table by importing at a later time (a backup form): $ mysqldump database_name table_name and since this gets printed to stdout, you should probably redirect to a file like: $ mysqldump database_name table_name table_name.sql.bak regards, thalis On Sat, 10 Feb 2001, Mike Yuen wrote: How do I dump all the contents of a table called "clients" into a .txt file (or anyother kind of file for that matter). Thanks for your help. Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Exporting data
Despite the temptation to suggest RTFM ... mysqldump database table(s)textfile.txt ~ or ~ mysqldump -uUserID -pPassword database table(s)textfile.txt If you type mysqldump|more at the command prompt, the syntax for this command is fully documented. You will also find the MySQL Manual to be an invaluable reference. G. Jensen - Original Message - From: "Mike Yuen" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 10, 2001 7:03 PM Subject: Exporting data How do I dump all the contents of a table called "clients" into a .txt file (or anyother kind of file for that matter). Thanks for your help. Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
