Solved

Exporting to Excel for Mailmerging

Posted on 2004-08-23
16
386 Views
Last Modified: 2013-12-24
Hi All,

This is more of a general question. I was wondering if anyone has the time to explain how (using what tags, functions etc) I can create a CF page that:

- creates a new excel file
-  exports certain fields of a Users table in a MSSQL database (say  into the worksheet (so i can then do a mailmerge into word)
- gives me the option to download the file
- deletes the file after downloading is done

If there is a better way to achieve the mailmerge then feel free to let me know that as well.

Thanks for your time.

Regards,
Brad
0
Comment
Question by:bradderick
  • 7
  • 4
  • 2
  • +2
16 Comments
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
If you are using MS SQL you should really use a DTS package to export to an excel file.. You can call the package with DTSRun, you can create the arguments for the package with DTSRunui, once you created the arguments you can execute it with cfexecute.
0
 

Author Comment

by:bradderick
Comment Utility
Thanks for the quick reply.

Could you please be a little more explicit as I'm unsure as to what a DTS package is or how to use DTSRun

Regards,
Brad
0
 

Author Comment

by:bradderick
Comment Utility
I would really like a  solution that I would be able to run on an externally hosted solution. So that should take in normal security that is applied to hosted websites.

Regards,
Brad
0
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
this solution is not for you then..
0
 
LVL 17

Expert Comment

by:anandkp
Comment Utility
U cld create excel sheet from with MSSQL.
this wld export the results extracted by the query & save them to a excel file on the server.
u cld then use CFCONTENT tag to download the fiel & set the attribute for DELETEFILE="Yes" - so that it deletes as soon as the download it complete.
0
 

Author Comment

by:bradderick
Comment Utility
hi anand,

could you elaborate a bit on your idea? How would I do that with MSSQL? Could i do it with a hosted solution?

Could I run the creation of the excel spreadsheet with a coldfusion tag somehow? So although the sheet is created with MSSQL it can be kicked off via CF somehow?

Regards,
Brad
0
 
LVL 17

Expert Comment

by:anandkp
Comment Utility
b4 u execute the MSSQL code - u cld have the file created via <CFFILE action="Write"
u cld create a new file & have a DSN point to it & then export ur results to that excel file.
[but i am searching for the code that cld write it from MSSQL itself - i remember having something similar to it]
0
 

Author Comment

by:bradderick
Comment Utility
so how do you make a dsn point to it and use coldfusion to export results from a cfquery to an excel file after having created it with cffile ?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:anandkp
Comment Utility
u'll have to create the DSN via CFAdmin - have it point to this excel file [uncheck maintain connections - so the file can be deleted]
once the dsn is set - query it just the way u wld query ur table - via the DataSource name in CFquery.
u cld isnert/update/delete the values from this excel file thru ur CFM code.

Depending on the version of CF ur using - u cld also use DSNLess Connections [version 5 & earlier]
0
 

Author Comment

by:bradderick
Comment Utility
interesting, interesting...

I'll have to do some more reading up on that method.

Did you find any more info for the mssql direct method and how to activate it using cf?

0
 
LVL 17

Expert Comment

by:anandkp
Comment Utility
I've used this earlier to export results - in a report format available for users to download ... via excel ... so i know it has to work for sure ... abt the direct method via MSSQL - i am looking into it ...
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 500 total points
Comment Utility
Okay it is actually really simple.

You can have CF generate an excel file that is streamed to the browser and so there is nevera  copy saved on the server - so no need to delete afterwards.

And no need to create another DSN

Here is the VERY SIMPLE code.  Notice that there is no <html> or <body> tags - only the few lines of code below.

<cfquery datasource="#mydsn#" name="GetInfo">
      SELECT FirstName, LastName, Address1, Address2, City, State, Zip, etc...
        FROM mytable
</cfquery>

<cfheader name="content-disposition" value="inline;filename=AddressReport.xls">
<cfcontent type="application/msexcel">
<table border="1">
      <tr>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Address Line 1</th>
            <th>Address Line 2</th>
            <th>City</th>
            <th>State</th>
            <th>Zip</th>
      </tr>
      <cfoutput query="GetInfo">
            <tr>
                  <td>#First Name#</td>
                  <td>#Last Name#</td>
                  <td>#Address Line 1#</td>
                  <td>#Address Line 2#</td>
                  <td>#City#</td>
                  <td>#State#</td>
                  <td>#Zip#</td>
            </tr>
      </cfoutput>
</table>
0
 
LVL 1

Expert Comment

by:lxdev
Comment Utility
As an alternative to mrichmon's code above, you can also create it as a CSV file:

<!--- START OF CODE --->
<cfsetting enablecfoutputonly="Yes">
<cfquery datasource="#mydsn#" name="GetInfo">
     SELECT FirstName, LastName, Address1, Address2, City, State, Zip
        FROM mytable
</cfquery>

<cfcontent type="application/csv">
<cfheader name="Content-Disposition" value="attachment; filename=AddressReport.csv">

<cfoutput>"First Name","Last Name","Address Line 1","Address Line 2","City","State","Zip"
</cfoutput>
<cfoutput query="GetInfo">"#GetInfo.FirstName#","#GetInfo.LastName#","#GetInfo.Address1#","#GetInfo.Address2#","#GetInfo.City#","#GetInfo.State#","#GetInfo.Zip#"
</cfoutput>
<!--- END OF CODE --->

If you use this solution, then you MUST remember to use the CFSETTING tag, which will enable output only between CFOUTPUT tags.
0
 

Author Comment

by:bradderick
Comment Utility
Hi Guys,

Those are some excellent examples, I really appreciate you being so detailed.

If I did stream it to my browser, could I then use it in a mailmerge in word or would it be easier to create a downloadable copy to use with word? I would much rather not have any extraneous files floating around the server so if I can do a mailmerge using it just in my browser so much the better! In fact, the more streamlined it is the better.

Cheers,
Brad
0
 
LVL 35

Expert Comment

by:mrichmon
Comment Utility
If you use my method it does not create a file on the server.  Instead it sends the file to the user and either

a) prompts for download to save to client harddrive
or
b) displays in browser so user can save

A or B depends on the user's browser settings, but in 99% of the cases it will be A

Then once saved it is just a regular excel file that can be used with mailmerge.
0
 

Author Comment

by:bradderick
Comment Utility
Hi Guys,

Thank you all for your input on this general question. Mrichmon's solution seems to be the cleanest and most efficient so I have accepted that as the answer.

Thanks again for your time all.

Regards,
Brad
0

Featured Post

Superior storage. Superior surveillance.

WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

Join & Write a Comment

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now