Brainstorming" time!

Ok. I'm looking into alternatives. I'm trying to figure out an alternative
to mysql exporting into xls file. Is there any another way you can export
into file and make it readable? What format do you use? I'm open to ideas
from experienced database programmers :) I'm upgrading the whole database
system from stupid Access "database" into SQL variant, whether it be MySQL
or SQL server (MeowSoft)



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Brainstorming" time!

----- Original Message -----
From: "Scott Hamm"
To: "'Mysql ' (E-mail)"
Sent: Friday, August 20, 2004 12:02 PM
Subject: Brainstorming' time!


> Ok. I'm looking into alternatives. I'm trying to figure out an alternative
> to mysql exporting into xls file. Is there any another way you can export
> into file and make it readable? What format do you use? I'm open to ideas
> from experienced database programmers :) I'm upgrading the whole database
> system from stupid Access "database" into SQL variant, whether it be MySQL
> or SQL server (MeowSoft)
>
How do you export a MySQL table into an XLS file? ;-)

You raised an interesting point - the whole issue of exporting data from
MySQL - so I took a quick glance and couldn't see *anything* that looked
like an export utility of the kind I've seen in many other programs and
relational databases.

Just about the only thing I saw was the mysqldump utility which basically
generates the SQL needed to re-create and re-populate a table on another
system. But I don't think that's what you're looking for and it clearly
won't generate an XLS file. So I'm not sure how you'd even export to XLS.

In the absence of an actual export utility - assuming I haven't missed one
in the manual somewhere! - you are hosed. Unless of course you have
programming skills; in that case you could write your own export utility to
export data in any format you like. If you do that, you might consider
sharing it with the rest of us when it is done, even if it isn't a
full-function ultra-slick piece of code; it might still be useful to some of
us if we ever need to export data.

Or maybe there are some export utilities floating around for MySQL that are
described somewhere other than the manual. I could well imagine users
developing their own homegrown utilities and donating them to MySQL. I can
also imagine professional developers writing a full-function export utility
for MySQL; a Google search might turn those up.

Rhino


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Brainstorming" time!

Use Perl:

http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.0 4/lib/Spreadsheet/WriteExcel.pm

use strict;
use warnings;
use DBI;
use Spreadsheet::WriteExcel;

my ($user, $pass, $db, $table) = ('foo', 'bar', 'test', 'users');

my $dbh = DBI->connect("DBI:mysql:database=$db", $user, $pass);
my $workbook = Spreadsheet::WriteExcel->new("$table.xls");
my $worksheet = $workbook->add_worksheet();

my ($col, $row) = (0, 0);

for (@{ $dbh->selectall_arrayref("SHOW COLUMNS FROM $table") }) {
$worksheet->write($row, $col, $_->[0]);
$col++;
}

for (@{ $dbh->selectall_arrayref("SELECT * FROM $table") }) {
$col = 0;
$row++;

for (@{ $_ }) {
$worksheet->write($row, $col, $_);
$col++;
}
}

____________________________________________________________
Eamon Daly



----- Original Message -----
From: "Scott Hamm"
To: "'Mysql ' (E-mail)"
Sent: Friday, August 20, 2004 11:02 AM
Subject: Brainstorming' time!


> Ok. I'm looking into alternatives. I'm trying to figure out an alternative
> to mysql exporting into xls file. Is there any another way you can export
> into file and make it readable? What format do you use? I'm open to ideas
> from experienced database programmers :) I'm upgrading the whole database
> system from stupid Access "database" into SQL variant, whether it be MySQL
> or SQL server (MeowSoft)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Brainstorming" time!

Scott Hamm wrote:

> Ok. I'm looking into alternatives. I'm trying to figure out an alternative
> to mysql exporting into xls file.

Gosh, what's wrong with CSV files? Surely Access can export a classic
CSV file format with fields separated by commas and enclosed by ""?

Then you can just use

LOAD DATA INFILE file.csv INTO TABLE whatever
FIELDS TERMINATED BY ',' ENCLOSED BY '"';

Of course, you still have the problem of creating the appropriate
equivalent tables in mysql, but I'm sure you can dump the schema in
Access and massage the output to create the schema in MySQL.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

RE: Brainstorming" time!

Ok unless I missed something here, wouldn't you just use the "SELECT ...
INTO OUTFILE" Syntax found here
http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

Just search for "outfile" and you can find it faster.

You can run a query against a table and save the output into a file and
in the case of creating excel type data, just save it into a file with
the fields separated by commas, a CSV file in other words.

Then use excel to open the file.



Chris Hood =20
Investigator Verizon Global Security Operations Center=20
Email: christopher.l.hood@verizon.com=20
Desk: 972.399.5900 =20

Verizon Proprietary=20

NOTICE - This message and any attached files may contain information
that is confidential and/or subject of legal privilege intended only for
the use by the intended recipient. If you are not the intended
recipient or the person responsible for delivering the message to the
intended recipient, be advised that you have received this message in
error and that any dissemination, copying or use of this message or
attachment is strictly forbidden, as is the disclosure of the
information therein. If you have received this message in error please
notify the sender immediately and delete the message.=20

-----Original Message-----
From: rhino1@sympatico.ca [mailto:rhino1@sympatico.ca]=20
Sent: Friday, August 20, 2004 11:30 AM
To: Scott Hamm; 'Mysql ' (E-mail)
Subject: Re: Brainstorming' time!


----- Original Message -----=20
From: "Scott Hamm"
To: "'Mysql ' (E-mail)"
Sent: Friday, August 20, 2004 12:02 PM
Subject: Brainstorming' time!


> Ok. I'm looking into alternatives. I'm trying to figure out an
alternative
> to mysql exporting into xls file. Is there any another way you can
export
> into file and make it readable? What format do you use? I'm open to
ideas
> from experienced database programmers :) I'm upgrading the whole
database
> system from stupid Access "database" into SQL variant, whether it be
MySQL
> or SQL server (MeowSoft)
>
How do you export a MySQL table into an XLS file? ;-)

You raised an interesting point - the whole issue of exporting data from
MySQL - so I took a quick glance and couldn't see *anything* that looked
like an export utility of the kind I've seen in many other programs and
relational databases.

Just about the only thing I saw was the mysqldump utility which
basically
generates the SQL needed to re-create and re-populate a table on another
system. But I don't think that's what you're looking for and it clearly
won't generate an XLS file. So I'm not sure how you'd even export to
XLS.

In the absence of an actual export utility - assuming I haven't missed
one
in the manual somewhere! - you are hosed. Unless of course you have
programming skills; in that case you could write your own export utility
to
export data in any format you like. If you do that, you might consider
sharing it with the rest of us when it is done, even if it isn't a
full-function ultra-slick piece of code; it might still be useful to
some of
us if we ever need to export data.

Or maybe there are some export utilities floating around for MySQL that
are
described somewhere other than the manual. I could well imagine users
developing their own homegrown utilities and donating them to MySQL. I
can
also imagine professional developers writing a full-function export
utility
for MySQL; a Google search might turn those up.

Rhino


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Dchristopher.l.hood@veri zon.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql@m.gmane.org

AW: Brainstorming" time!

Rhino

Get the free DBTools DBManager Pro from
http://www.dbtools.com.br/EN/dbmanagerpro.php - there is a cool CSV =
export
function there

Or get MyODBC and connect from Excel to your MySQL database

Or as Scott said, roll your own exporter if you have programming skills

Freddie=20

> -----Ursprüngliche Nachricht-----
> Von: Rhino [mailto:rhino1@sympatico.ca]=20
> Gesendet: Freitag, 20. August 2004 18:30
> An: Scott Hamm; 'Mysql ' (E-mail)
> Betreff: Re: Brainstorming' time!
>=20
>=20
> ----- Original Message -----
> From: "Scott Hamm"
> To: "'Mysql ' (E-mail)"
> Sent: Friday, August 20, 2004 12:02 PM
> Subject: Brainstorming' time!
>=20
>=20
> > Ok. I'm looking into alternatives. I'm trying to figure out=20
> an alternative
> > to mysql exporting into xls file. Is there any another way=20
> you can export
> > into file and make it readable? What format do you use? I'm=20
> open to ideas
> > from experienced database programmers :) I'm upgrading the=20
> whole database
> > system from stupid Access "database" into SQL variant,=20
> whether it be MySQL
> > or SQL server (MeowSoft)
> >
> How do you export a MySQL table into an XLS file? ;-)
>=20
> You raised an interesting point - the whole issue of=20
> exporting data from
> MySQL - so I took a quick glance and couldn't see *anything*=20
> that looked
> like an export utility of the kind I've seen in many other=20
> programs and
> relational databases.
>=20
> Just about the only thing I saw was the mysqldump utility=20
> which basically
> generates the SQL needed to re-create and re-populate a table=20
> on another
> system. But I don't think that's what you're looking for and=20
> it clearly
> won't generate an XLS file. So I'm not sure how you'd even=20
> export to XLS.
>=20
> In the absence of an actual export utility - assuming I=20
> haven't missed one
> in the manual somewhere! - you are hosed. Unless of course you have
> programming skills; in that case you could write your own=20
> export utility to
> export data in any format you like. If you do that, you might consider
> sharing it with the rest of us when it is done, even if it isn't a
> full-function ultra-slick piece of code; it might still be=20
> useful to some of
> us if we ever need to export data.
>=20
> Or maybe there are some export utilities floating around for=20
> MySQL that are
> described somewhere other than the manual. I could well imagine users
> developing their own homegrown utilities and donating them to=20
> MySQL. I can
> also imagine professional developers writing a full-function=20
> export utility
> for MySQL; a Google search might turn those up.
>=20
> Rhino
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =20
> http://lists.mysql.com/mysql?unsub=3Dfreddie@parawebic.com
>=20
>=20



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql@m.gmane.org

Re: Brainstorming" time!

----- Original Message -----
From: "Freddie Sorensen"
To: "'Rhino'" ; "'Scott Hamm'"
; "''Mysql ' (E-mail)'"
Sent: Friday, August 20, 2004 2:28 PM
Subject: AW: Brainstorming' time!


Rhino

| Get the free DBTools DBManager Pro from
| http://www.dbtools.com.br/EN/dbmanagerpro.php - there is a cool CSV export
| function there
|
| Or get MyODBC and connect from Excel to your MySQL database
|
| Or as Scott said, roll your own exporter if you have programming skills
|

Thanks for the suggestions, Freddie!

Actually, I don't need to export MySQL data to Excel format; I was just
remarking that I hadn't seen a real export utility in MySQL when I was
replying to Scott's original question. I had forgotten about SELECT ... INTO
OUTFILE altogether and didn't hit it when I searched the MySQL manual.

The free tool you suggested - or MyODBC - would probably be good options if
I really needed to export MySQL data to Excel so I'll keep them in mind if I
need to do that.

As for programming skills, I'm pretty familiar with Java so I feel sure I
could write a suitable utility with that. In fact, unless I had a really
urgent need, I would prefer to write my own stuff just because I like
writing code ;-)

Rhino


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Brainstorming" time!

You could export the file as xml with either '--xml'
or '-X':

mysql --xml -u username -p db_name > dbfile.xml

http://dev.mysql.com/doc/mysql/en/mysql.html

HTH,
James




> ----- Original Message -----
> From: "Scott Hamm"
> To: "'Mysql ' (E-mail)"
> Sent: Friday, August 20, 2004 11:02 AM
> Subject: Brainstorming' time!
>
>
> > Ok. I'm looking into alternatives. I'm trying to
> figure out an alternative
> > to mysql exporting into xls file. Is there any
> another way you can export
> > into file and make it readable? What format do you
> use? I'm open to ideas
> > from experienced database programmers :) I'm
> upgrading the whole database
> > system from stupid Access "database" into SQL
> variant, whether it be MySQL
> > or SQL server (MeowSoft)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=itjayw@yahoo.com
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Brainstorming" time!

100 quatloos to Chris for the simplest answer. Do that.

I'll add that you could also use mysqldump:

mysqldump -T /tmp test users

This would create /tmp/users.sql, containing the CREATE
TABLE, and /tmp/users.txt, containing a tab-delimited file
suitable for import. No header row, though.

And, apparently, my perl solution completely reinvented the
wheel. See pp.527-530 "MySQL Cookbook", 1st ed. for a
better script.

____________________________________________________________
Eamon Daly


----- Original Message -----
From:
To: ; ;

Sent: Friday, August 20, 2004 1:24 PM
Subject: RE: Brainstorming' time!


> Ok unless I missed something here, wouldn't you just use the "SELECT ...
> INTO OUTFILE" Syntax found here
> http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
>
> Just search for "outfile" and you can find it faster.
>
> You can run a query against a table and save the output into a file and
> in the case of creating excel type data, just save it into a file with
> the fields separated by commas, a CSV file in other words.
>
> Then use excel to open the file.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Brainstorming" time!

Note that based upon this thread, I downloaded and installed MyODBC (simple
Google search), configured a DSN, created an Excel query, and loaded the DB
into the worksheet in under 5-minutes total time. While I don't use Excel
that much for anything, it was a very simple exercise and might prove handy
for some things.

Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters
USA Central Time Zone
636-922-9158 ext. 8652 fax 636-447-4471

dirk.bremer@nisc.cc
www.nisc.cc
----- Original Message -----
From: "Eamon Daly"
To:
Sent: Friday, August 20, 2004 13:55
Subject: Re: Brainstorming' time!


> 100 quatloos to Chris for the simplest answer. Do that.
>
> I'll add that you could also use mysqldump:
>
> mysqldump -T /tmp test users
>
> This would create /tmp/users.sql, containing the CREATE
> TABLE, and /tmp/users.txt, containing a tab-delimited file
> suitable for import. No header row, though.
>
> And, apparently, my perl solution completely reinvented the
> wheel. See pp.527-530 "MySQL Cookbook", 1st ed. for a
> better script.
>
> ____________________________________________________________
> Eamon Daly
>
>
> ----- Original Message -----
> From:
> To: ; ;
>
> Sent: Friday, August 20, 2004 1:24 PM
> Subject: RE: Brainstorming' time!
>
>
> > Ok unless I missed something here, wouldn't you just use the "SELECT ...
> > INTO OUTFILE" Syntax found here
> > http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
> >
> > Just search for "outfile" and you can find it faster.
> >
> > You can run a query against a table and save the output into a file and
> > in the case of creating excel type data, just save it into a file with
> > the fields separated by commas, a CSV file in other words.
> >
> > Then use excel to open the file.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=dirk.bremer@nisc.cc
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Re: Brainstorming" time!

At 11:02 AM 8/20/2004, you wrote:
>Ok. I'm looking into alternatives. I'm trying to figure out an alternative
>to mysql exporting into xls file. Is there any another way you can export
>into file and make it readable? What format do you use? I'm open to ideas
>from experienced database programmers :) I'm upgrading the whole database
>system from stupid Access "database" into SQL variant, whether it be MySQL
>or SQL server (MeowSoft)
>

Try these:

http://www.shareware4u.com/Business-Finance/Database-Managem ent/MySQL-to-Excel.htm
http://ems-hitech.com/mysqlutils/

You should also be able to use the MySQL ODBC driver and open the database
table in Excel directly.

Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org