RE: Populate values in an Excel sheet from MySQL

[Arjan Hulshoff]

Hello Nick,

This you can do with the MySQL ODBC Driver installed
(http://dev.mysql.com/downloads/connector/odbc/3.51.html). Further more
you need to activate Microsoft ActiveX Data Objects in the references.
You can use the following code:

--Begin Code--
Dim cn As ADODB.Connection
Dim rs As ADODB.RecordSet

Set cn = New ADODB.Connection
Set rs = New ADODB.RecordSet

cn.ConnectionString = DRIVER={MySQL ODBC 3.51
Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
name;PASSWORD=myPassword;OPTION=3;
cn.Open

sSQL = SELECT * FROM database

rs.Open sSQL, cn

If Not rs.BOF Then rs.MoveFirst
Do While Not rs.EOF
Cells(1, 1) = rs.Fields(index)' This line you can
adjust with your own code
rs.MoveNext
Loop

On Error Resume Next' This is my solution to make sure that the
recordset is always closed, _
without the errorhandling there
occurs an error when you use a query _
that doesn't return results
('INSERT' e.g.). If there is a better way _
to close the connection, then
let me know.
If rs.State = adStateOpen Then rs.Close
On Error Goto 0
cn.Close

Set rs = Nothing
Set cn = nothing
--End Code--

HTH,
Arjan.

-Original Message-
From: Nick Jones [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 01, 2005 08:23 PM
To: mysql@lists.mysql.com
Subject: Populate values in an Excel sheet from MySQL

Does anyone know if it is possible to populate values into an Excel
spreadsheet from a MySQL database? Can I do this directly in Excel or do
I need to create an external program to do the work (i.e. in VB).

Thanks
-Nick

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



-- 
The information contained in this communication and any attachments is 
confidential and may be privileged, and is for the sole use of the intended 
recipient(s). Any unauthorized review, use, disclosure or distribution is 
prohibited. If you are not the intended recipient, please notify the sender 
immediately by replying to this message and destroy all copies of this message 
and any attachments. ASML is neither liable for the proper and complete 
transmission of the information contained in this communication, nor for any 
delay in its receipt.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Populate values in an Excel sheet from MySQL

[inferno]

Hi,

   I had to do the same thing on a project and the problem was that if 
you use CSV you will not be able to make a formated excel document.
   I am using now *Spreadsheet_Excel_Writer 
/package/Spreadsheet_Excel_Writer ( 
*http://pear.php.net/package/Spreadsheet_Excel_Writer ) and it does 
everything I need, including formating the page for printing, color, 
bold and boarder on cells and the best part is that it's no really hard 
to use.
   If you change your mind and want to generate that from perl you also 
have some PEAR packages for that, but I've sticked to PHP and with this 
the problem was solved and I generate my data on access, custom build 
depending on the select.


Best regards,
Cristi Stoica

Arjan Hulshoff wrote:


Hello Nick,

This you can do with the MySQL ODBC Driver installed
(http://dev.mysql.com/downloads/connector/odbc/3.51.html). Further more
you need to activate Microsoft ActiveX Data Objects in the references.
You can use the following code:

--Begin Code--
Dim cn As ADODB.Connection
Dim rs As ADODB.RecordSet

Set cn = New ADODB.Connection
Set rs = New ADODB.RecordSet

cn.ConnectionString = DRIVER={MySQL ODBC 3.51
Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
name;PASSWORD=myPassword;OPTION=3;
cn.Open

sSQL = SELECT * FROM database

rs.Open sSQL, cn

If Not rs.BOF Then rs.MoveFirst
Do While Not rs.EOF
Cells(1, 1) = rs.Fields(index)  ' This line you can
adjust with your own code
rs.MoveNext
Loop

On Error Resume Next' This is my solution to make sure that the
recordset is always closed, _
without the errorhandling there
occurs an error when you use a query _
that doesn't return results
('INSERT' e.g.). If there is a better way _
to close the connection, then
let me know.
If rs.State = adStateOpen Then rs.Close
On Error Goto 0
cn.Close

Set rs = Nothing
Set cn = nothing
--End Code--

HTH,
Arjan.

-Original Message-
From: Nick Jones [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 01, 2005 08:23 PM

To: mysql@lists.mysql.com
Subject: Populate values in an Excel sheet from MySQL

Does anyone know if it is possible to populate values into an Excel
spreadsheet from a MySQL database? Can I do this directly in Excel or do
I need to create an external program to do the work (i.e. in VB).

Thanks
-Nick

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



 

Re: Populate values in an Excel sheet from MySQL [SOLVED]

[Nick Jones]

Thanks to all for your help. I solved my problem the
quick and dirty way. Here's what I did:

1. Created my Excel spreadsheet to look the way I
wanted it, saved it as an XML spreadsheet.

2. Used PHP to pull the desired data from the MySQL
server on our iSeries machine.

3. PHP writes the XML data to a new text file using
the values from the MySQL DB and gives it the .xls
extension.

4. Browser is redirected to the new spreadsheet.

5. Spreadsheet opens like normal in IE for
saving/downloading. Or in Firefox it lets you download
it and it opens like a normal .xls spreadsheet.

Thanks to everyone here for their insight. I coudln't
have achieved it without all of you!

-Nick

--- inferno [EMAIL PROTECTED] wrote:

 Hi,
 
 I had to do the same thing on a project and the
 problem was that if 
 you use CSV you will not be able to make a formated
 excel document.
 I am using now *Spreadsheet_Excel_Writer 
 /package/Spreadsheet_Excel_Writer ( 

*http://pear.php.net/package/Spreadsheet_Excel_Writer
 ) and it does 
 everything I need, including formating the page for
 printing, color, 
 bold and boarder on cells and the best part is that
 it's no really hard 
 to use.
 If you change your mind and want to generate
 that from perl you also 
 have some PEAR packages for that, but I've sticked
 to PHP and with this 
 the problem was solved and I generate my data on
 access, custom build 
 depending on the select.
 
 Best regards,
 Cristi Stoica
 
 Arjan Hulshoff wrote:
 
 Hello Nick,
 
 This you can do with the MySQL ODBC Driver
 installed

(http://dev.mysql.com/downloads/connector/odbc/3.51.html).
 Further more
 you need to activate Microsoft ActiveX Data Objects
 in the references.
 You can use the following code:
 
 --Begin Code--
 Dim cn As ADODB.Connection
 Dim rs As ADODB.RecordSet
 
 Set cn = New ADODB.Connection
 Set rs = New ADODB.RecordSet
 
 cn.ConnectionString = DRIVER={MySQL ODBC 3.51

Driver};SERVER=data.domain.com;PORT=3306;DATABASE=myDatabase;USER=myUser
 name;PASSWORD=myPassword;OPTION=3;
 cn.Open
 
 sSQL = SELECT * FROM database
 
 rs.Open sSQL, cn
 
 If Not rs.BOF Then rs.MoveFirst
 Do While Not rs.EOF
  Cells(1, 1) = rs.Fields(index)' This line you
 can
 adjust with your own code
  rs.MoveNext
 Loop
 
 On Error Resume Next ' This is my solution to make
 sure that the
 recordset is always closed, _
  without the errorhandling there
 occurs an error when you use a query _
  that doesn't return results
 ('INSERT' e.g.). If there is a better way _
  to close the connection, then
 let me know.
 If rs.State = adStateOpen Then rs.Close
 On Error Goto 0
 cn.Close
 
 Set rs = Nothing
 Set cn = nothing
 --End Code--
 
 HTH,
 Arjan.
 
 -Original Message-
 From: Nick Jones [mailto:[EMAIL PROTECTED] 
 Sent: Thursday, September 01, 2005 08:23 PM
 To: mysql@lists.mysql.com
 Subject: Populate values in an Excel sheet from
 MySQL
 
 Does anyone know if it is possible to populate
 values into an Excel
 spreadsheet from a MySQL database? Can I do this
 directly in Excel or do
 I need to create an external program to do the work
 (i.e. in VB).
 
 Thanks
 -Nick
 
 __
 Do You Yahoo!?
 Tired of spam?  Yahoo! Mail has the best spam
 protection around
 http://mail.yahoo.com 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:

http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
   
 
 
 


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Populate values in an Excel sheet from MySQL

[Nick Jones]

Does anyone know if it is possible to populate values
into an Excel spreadsheet from a MySQL database? Can I
do this directly in Excel or do I need to create an
external program to do the work (i.e. in VB).

Thanks
-Nick

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Populate values in an Excel sheet from MySQL

[J.R. Bullington]

Automatically populate??

Not that _I_ know of, but of course there are those on this list that know
much more than I. 

I do know that you can export the values into tab delimited format and then
import it into Excel.

I think that you may want to do this via ASP or PHP. It would make life a
lot easier.

J.R.

-Original Message-
From: Nick Jones [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 01, 2005 2:23 PM
To: mysql@lists.mysql.com
Subject: Populate values in an Excel sheet from MySQL

Does anyone know if it is possible to populate values into an Excel
spreadsheet from a MySQL database? Can I do this directly in Excel or do I
need to create an external program to do the work (i.e. in VB).

Thanks
-Nick

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

--
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: Populate values in an Excel sheet from MySQL

[JamesDR]

Nick Jones wrote:

Does anyone know if it is possible to populate values
into an Excel spreadsheet from a MySQL database? Can I
do this directly in Excel or do I need to create an
external program to do the work (i.e. in VB).

Thanks
-Nick

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Yes, you can do it with odbc in excel. Create and ODBC connection using 
the MySQL driver.

(Office XP/Excel XP):
Data --Import External Data -- New Database Query -- (select your 
odbc connection) -- Setup the query (add cols) -Next- select a col. to 
select the data based upon (if any) -Next- Select a sort col and by (if 
any) -Next- Select Return Data to Microsoft Excel -Finish-


Remember, Excel has a hard limit on the number of rows, (it is not a 
database.)

--
Thanks,
James

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Populate values in an Excel sheet from MySQL

[Nick Jones]

--- J.R. Bullington [EMAIL PROTECTED] wrote:

 Automatically populate??
 
 Not that _I_ know of, but of course there are those
 on this list that know
 much more than I. 
 
 I do know that you can export the values into tab
 delimited format and then
 import it into Excel.
 
 I think that you may want to do this via ASP or PHP.
 It would make life a
 lot easier.
 
 J.R.

PHP would definitely be the way to go on this. I'm
working on a web application running on our AS/400
here running Apache, PHP, and MySQL to automate our
schedule forms that we fill out daily around here.
Everything I've got so far is running through
PHP/MySQL so if I can find a way to do this in PHP I'm
definitely going to try. I'll google around for awhile
and see what I come up with. MS Knowledge Base has
proven to be less than useful so far in my endeavor.

Also, thanks to James for his suggestion on using the
ODBC query directly from Excel. This will get us
started for the time being, and give me some leeway so
I can work on doing this in PHP.

Thanks to you both!
-Nick




Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Populate values in an Excel sheet from MySQL

[SGreen]

Nick Jones [EMAIL PROTECTED] wrote on 09/01/2005 03:18:39 PM:

 
 
 --- J.R. Bullington [EMAIL PROTECTED] wrote:
 
  Automatically populate??
  
  Not that _I_ know of, but of course there are those
  on this list that know
  much more than I. 
  
  I do know that you can export the values into tab
  delimited format and then
  import it into Excel.
  
  I think that you may want to do this via ASP or PHP.
  It would make life a
  lot easier.
  
  J.R.
 
 PHP would definitely be the way to go on this. I'm
 working on a web application running on our AS/400
 here running Apache, PHP, and MySQL to automate our
 schedule forms that we fill out daily around here.
 Everything I've got so far is running through
 PHP/MySQL so if I can find a way to do this in PHP I'm
 definitely going to try. I'll google around for awhile
 and see what I come up with. MS Knowledge Base has
 proven to be less than useful so far in my endeavor.
 
 Also, thanks to James for his suggestion on using the
 ODBC query directly from Excel. This will get us
 started for the time being, and give me some leeway so
 I can work on doing this in PHP.
 
 Thanks to you both!
 -Nick
 

The ODBC query is SO simple and flexible once you get through the darn 
wizard just once. I think it will save you a lot of headaches in the long 
run.

Another alternative is to run your query through the MySQL client with the 
HTML output flag turned on and Tee your output to some temp file. Excel 
(at least the modern versions) are HTML aware and will convert the 
TABLE, TR, TD, etc. tags into cells automagically.

Another alternative: I have also changed the Screen Buffer settings of my 
CMD window so that it no longer wraps at 80 columns. I tend to use 
something like 1024 x 2048 but it can get much bigger if you need it to. 
It allows me to catch a whole lot of CLI output before I hit the limits of 
the buffer. Highlight and copy your query results into Word and replace 
all | characters with tabs. I had to use Word as you can't enter the tab 
character into the replace with field in Notepad. -OR- paste into 
Notepad and save it off as a temp file. Then import that temp file into 
Excel as | delimited data. Of course if your actual data contains the | 
character, some of your rows will be wider by a column or two

However, the easiest is still the Import External Data wizard via ODBC. If 
you stick with the PHP solution, remember that Excel will process any 
tab-delimited file into columns and rows as well as it can do anything 
else. If you are really gonzo, you can actually produce a fully formatted 
sheet so long as you conform to the HTML+XML format that Excel uses when 
you click Save as HTML. That save format IS thoroughly documented in the 
KB (I know I found it recently). 

Options. Way too many options ;-D

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Populate values in an Excel sheet from MySQL

[Partha Dutta]

Nick Jones wrote:


--- J.R. Bullington [EMAIL PROTECTED] wrote:

 


Automatically populate??

Not that _I_ know of, but of course there are those
on this list that know
much more than I. 


I do know that you can export the values into tab
delimited format and then
import it into Excel.

I think that you may want to do this via ASP or PHP.
It would make life a
lot easier.

J.R.
   



PHP would definitely be the way to go on this. I'm
working on a web application running on our AS/400
here running Apache, PHP, and MySQL to automate our
schedule forms that we fill out daily around here.
Everything I've got so far is running through
PHP/MySQL so if I can find a way to do this in PHP I'm
definitely going to try. I'll google around for awhile
and see what I come up with. MS Knowledge Base has
proven to be less than useful so far in my endeavor.

Also, thanks to James for his suggestion on using the
ODBC query directly from Excel. This will get us
started for the time being, and give me some leeway so
I can work on doing this in PHP.

Thanks to you both!
-Nick




Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 



 


How about this for a novel aproach...

Use the CSV storage engine that MySQL provides in the source 
distribution?  It is very easy to use, and there is no headaches.


-- Partha Dutta

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Populate values in an Excel sheet from MySQL

[J.R. Bullington]

Here's a little heads-up, as you will need some XML coding to make it look
right.

PLEASE NOTE: I'm an ASP programmer, so PHP is a little foreign to me. You
will need to do some conversion.

Here is the way to do it:

1) Define your recordset and connection strings.
2) Write PHP strings to add ContentType (or similar call) =
application/vnd.ms-excel
3) (optional) Write PHP echo strings for adding XML tags. (You can find
these at http://msdn.microsoft.com, although I'll be damned if I can find
them now. Ask for them and I will get them from my other computer and send
them on.)


4) In the body of your webpage, put the following snippet:

body
table width=100%
thead
tr
tdCol 1/td
tdCol 2/td
...
tdCol end/td
/tr
/thead
tbody
?PHP Open the records, pull the set 
'do the loop
movefirst
while not eof
?
tr
td? PHP DATA 1 ?/td
td? PHP DATA 2 ?/td
...
td? PHP DATA end ?/td
/tr
?PHP Next in loop
Wend
Close
?
/tbody
/table
/body
/html



I hope this helps a little!

J.R.

PS - Shawn FYI, if you copy and paste a tab into the replace with in
notepad, you can do it that way...


-Original Message-
From: Nick Jones [mailto:[EMAIL PROTECTED] 
Sent: Thursday, September 01, 2005 3:19 PM
To: mysql@lists.mysql.com
Subject: RE: Populate values in an Excel sheet from MySQL



--- J.R. Bullington [EMAIL PROTECTED] wrote:

 Automatically populate??
 
 Not that _I_ know of, but of course there are those on this list that 
 know much more than I.
 
 I do know that you can export the values into tab delimited format and 
 then import it into Excel.
 
 I think that you may want to do this via ASP or PHP.
 It would make life a
 lot easier.
 
 J.R.

PHP would definitely be the way to go on this. I'm working on a web
application running on our AS/400 here running Apache, PHP, and MySQL to
automate our schedule forms that we fill out daily around here.
Everything I've got so far is running through PHP/MySQL so if I can find a
way to do this in PHP I'm definitely going to try. I'll google around for
awhile and see what I come up with. MS Knowledge Base has proven to be less
than useful so far in my endeavor.

Also, thanks to James for his suggestion on using the ODBC query directly
from Excel. This will get us started for the time being, and give me some
leeway so I can work on doing this in PHP.

Thanks to you both!
-Nick




Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs 
 

-- 
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: Populate values in an Excel sheet from MySQL

[Nick Jones]

--- Partha Dutta [EMAIL PROTECTED] wrote:

 Nick Jones wrote:
 
 --- J.R. Bullington [EMAIL PROTECTED]
 wrote:
 
   
 
 Automatically populate??
 
 Not that _I_ know of, but of course there are
 those
 on this list that know
 much more than I. 
 
 I do know that you can export the values into tab
 delimited format and then
 import it into Excel.
 
 I think that you may want to do this via ASP or
 PHP.
 It would make life a
 lot easier.
 
 J.R.
 
 
 
 PHP would definitely be the way to go on this. I'm
 working on a web application running on our AS/400
 here running Apache, PHP, and MySQL to automate our
 schedule forms that we fill out daily around here.
 Everything I've got so far is running through
 PHP/MySQL so if I can find a way to do this in PHP
 I'm
 definitely going to try. I'll google around for
 awhile
 and see what I come up with. MS Knowledge Base has
 proven to be less than useful so far in my
 endeavor.
 
 Also, thanks to James for his suggestion on using
 the
 ODBC query directly from Excel. This will get us
 started for the time being, and give me some leeway
 so
 I can work on doing this in PHP.
 
 Thanks to you both!
 -Nick
 
 
  


 Start your day with Yahoo! - make it your home page
 
 http://www.yahoo.com/r/hs 
  
 
   
 
 How about this for a novel aproach...
 
 Use the CSV storage engine that MySQL provides in
 the source 
 distribution?  It is very easy to use, and there is
 no headaches.
 
 -- Partha Dutta

Ok, here's what we're doing in a nutshell:

Every day we fill out forms with backup times and
tapes for various computer systems. Three forms a day
with 70-100 fields each with all the same data. One
Excel sheet and two word documents that end up as PDFs
later, all of this is very time consuming. I'm
creating a web app in PHP that lets you fill out one
form, then click a button and it creates the PDFs for
you. I've gotten that far, and now I'm trying to get
it to create the Excel sheet too with just one click
(fill in your desired date, click submit, and up pops
your PDFs and spreadsheet.

Thanks
-Nick

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]