bradderick
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
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
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.
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
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
ASKER
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
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.
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.
ASKER
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
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]
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]
ASKER
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]
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]
ASKER
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'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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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# ","#GetInf o.LastName #","#GetIn fo.Address 1#","#GetI nfo.Addres s2#","#Get Info.City# ","#GetInf o.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.
<!--- 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"
<cfoutput>"First Name","Last Name","Address Line 1","Address Line 2","City","State","Zip"
</cfoutput>
<cfoutput query="GetInfo">"#GetInfo.
</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.
ASKER
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
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.
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.
ASKER
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
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