Link to home
Start Free TrialLog in
Avatar of bradderick
bradderickFlag for Australia

asked on

Exporting to Excel for Mailmerging

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
Avatar of Tacobell777
Tacobell777

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.
Avatar of bradderick

ASKER

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
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
this solution is not for you then..
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.
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
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]
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 ?
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]
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?

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 ...
ASKER CERTIFIED SOLUTION
Avatar of mrichmon
mrichmon

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
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.
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