Solved

Exporting to Excel for Mailmerging

Posted on 2004-08-23
16
392 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
  • +2
16 Comments
 
LVL 17

Expert Comment

by:Tacobell777
ID: 11877395
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
ID: 11877641
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
ID: 11877698
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 17

Expert Comment

by:Tacobell777
ID: 11877836
this solution is not for you then..
0
 
LVL 17

Expert Comment

by:anandkp
ID: 11878088
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
ID: 11878292
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
ID: 11878503
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
ID: 11878563
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
 
LVL 17

Expert Comment

by:anandkp
ID: 11878614
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
ID: 11878682
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
ID: 11879205
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
ID: 11882456
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
ID: 11883703
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
ID: 11888253
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
ID: 11893466
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
ID: 11898161
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

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 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