Thanks JR, Shawn, Scott, ... for your replies.
I choose to make use of the SELECT ... INTO OUTFILE.
This works fine, but I also want a header-line in the CSV file.
So I made the following statement:
SELECT `ID`, `Code`, `Name`
UNION
SELECT `ID`, `Code`, `Name` INTO OUTFILE 'D:/MySQL Datafiles/Units.csv'
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
FROM Units ORDER BY `ID`;
But this makes the header-line a trailer-line in the csv file, like this:
11,kg,KiloGrams
12,g,Grams
13,Ton,Tonne
...
ID,Code,Name
Any idea how to make a sorted csv file with a real header-line ?
TIA, Cor
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Saturday, December 10, 2005 5:17 PM
Subject: RE: from MySQL to MS Excel ...
There is at least one other option that JR didn't mention... at least some
versions of Excel have the menu option Data->Get External Data which
allows you to link through ODBC to run queries directly from within Excel.
I have barely used it and I have never tried it with MySQL so I can't
really explain how to use it or what it's limitations will be but I know
that it works through at least two other ODBC drivers.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"J.R. Bullington" <[EMAIL PROTECTED]> wrote on 12/10/2005 10:33:18 AM:
Here's the skinny -- YES and NO.
NO in that it won't export directly, YES in that you have to do a little
leg
work in order for it to be done.
You have 3 options -- ODBC, Code and CSV.
ODBC -- Excel has the ability to use ODBC connections to the MySQL
database.
Run your MySQL query with the HTML flags turned on and then export to a
file
so that Excel can read it. (Thanks to SGreen for this info from an
earlier
post).
CODE -- If you code it in ASP or PHP, you can get your code to push
directly
into Excel with field headers and data, and have formatting options
because
Excel can interpret HTML code.
CSV -- Do your MySQL query from the CLI and then use MySQL to export
your
results to a CSV file. Then open the CSV file in Excel (using the Excel
File
> Open). See ODBC connection above for another option using the HTML
flag.
HTH,
J.R.
-----Original Message-----
From: C.R.Vegelin [mailto:[EMAIL PROTECTED]
Sent: Saturday, December 10, 2005 9:35 AM
To: mysql@lists.mysql.com
Subject: from MySQL to MS Excel ...
Hi Friends,
I am looking for an easy and seamless way to export MySQL query output
to MS
Excel.
At this moment I am using MS Access 2003 as front-end for a MySQL
database.
With MS Access I can easily send the output of queries on my database to
MS
Excel.
All I need to do is select Tools > Office Links > Analyze it with
Microsoft
Office Excel.
That's all. This applies to all kinds of MySQL queries, including WITH
ROLLUP options.
In the manual I found:
http://dev.mysql.com/doc/refman/5.0/en/doctoexcel.html
But this is too much trouble, and does not allow full functionality of
MySQL
queries.
Question: is it possible to create MS Excel files directly from MySQL ?
TIA, Cor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
