Webmaster Forum / ColdFusion / Advanced Techniques / September 2004

I'm generating an Excel doc from a query, using CFCONTENT. When there is a " in
the database, the subsequent Excel doc ends up being corrupted.

Has anyone seen this before? How do you deal with it? I hate to scrub the
user's data if I don't have to. Any suggestions are appreciated!

Here is my code:

   
   
    <cfquery name="GetSpreadsheetValues" datasource="#OrtDSN#" dbtype="ODBC">
            SELECT
                Qty,
                Unit,
                Description,
                MfgName,
                MfgPartNum,
                SupplierPartNum,
                MAPCONStockNum,
                UnitCost,
                WTunit,
                CUunit,
                MfgLeadTime,
                Station,
                ROSDate,
                CruiseNum,
                ProjectCode,
                ActivityEventCode,
                StockRoom,
                MarkFor,
                PricingSource,   
                FundingDivision,
                WBSChargeCode,   
                ShipTo,
                ShipToOtherAddress,
                CSICode,
                ConstructionPhase,
                SpecialNotes,
                SupplierCompanyName,
                SupplierAddress1,
                SupplierAddress2,
                SupplierCity,
                SupplierState,
                SupplierZip,
                SupplierCountry,
                SupplierInternationalAddress,
                SupplierContactName,
                SupplierPhone,
                SupplierFax,
                MAPCONVendorNum,
                ORTRequisitionNum
            FROM tblImport
            WHERE FileName='#URL.import#'
</cfquery>

<cfset tab=chr(9)>
<cfset carriage=chr(13)>

<cfheader
    name="Content-Type"
    value="application/msexcel">
    <cfheader
    name="Content-Disposition"
    value="attachment; filename=ORTimport.xls; sheetname=ORTimport.xls">

<CFCONTENT TYPE="application/msexcel"

    WTunit    CUunit    MfgLeadTime    Station    ROSDate    CruiseNum    ProjectCode    ActivityEventCo
de    StockRoom    MarkFor    PricingSource    FundingDivision    WBSChargeCode    ShipTo    ShipToOt
herAddress    CSICode    ConstructionPhase    SpecialNotes    SupplierCompanyName    SupplierAd
dress1    SupplierAddress2    SupplierCity    SupplierState    SupplierZip
SupplierCountry    SupplierInternationalAddress    SupplierContactName    SupplierPhone    S
upplierFax    MAPCONVendorNum    ORTRequisitionNum

<CFOUTPUT
QUERY="GetSpreadsheetValues">#Qty##tab##Unit##tab##Description##tab##MfgName##ta
b##MfgPartNum##tab##SupplierPartNum##tab##MAPCONStockNum##tab##UnitCost##tab##WT
unit##tab##CUunit##tab##MfgLeadTime##tab##Station##tab##ROSDate##tab##CruiseNum#
#tab##ProjectCode##tab##ActivityEventCode##tab##StockRoom##tab##MarkFor##tab##Pr
icingSource##tab##FundingDivision##tab##WBSChargeCode##tab##ShipTo##tab##ShipToO
therAddress##tab##CSICode##tab##ConstructionPhase##tab##SpecialNotes##tab##Suppl
ierCompanyName##tab##SupplierAddress1##tab##SupplierAddress2##tab##SupplierCity#
#tab##SupplierState##tab##SupplierZip##tab##SupplierCountry##tab##SupplierIntern
ationalAddress##tab##SupplierContactName##tab##SupplierPhone##tab##SupplierFax##
tab##MAPCONVendorNum##tab##ORTRequisitionNum##Carriage#
</CFOUTPUT>

<!---     <table cellspacing="0" cellpadding="0" border="1">
        <tr bgcolor="cccccc">
           <td><strong>Qty</strong></td>
           <td><strong>Unit</strong></td>
           <td><strong>Description</strong></td>
           <td><strong>MfgName</strong></td>
           <td><strong>MfgPartNum</strong></td>
           <td><strong>SupplierPartNum</strong></td>
           <td><strong>MAPCONStockNum</strong></td>
           <td><strong>UnitCost</strong></td>
           <td><strong>WTunit</strong></td>
           <td><strong>CUunit</strong></td>
           <td><strong>MfgLeadTime</strong></td>
           <td><strong>Station</strong></td>
           <td><strong>ROSDate</strong></td>
           <td><strong>CruiseNum</strong></td>
           <td><strong>ProjectCode</strong></td>
           <td><strong>ActivityEventCode</strong></td>
           <td><strong>StockRoom</strong></td>
           <td><strong>MarkFor</strong></td>
           <td><strong>PricingSource</strong></td>
           <td><strong>FundingDivision</strong></td>
           <td><strong>WBSChargeCode</strong></td>
           <td><strong>ShipTo</strong></td>
           <td><strong>ShipToOtherAddress</strong></td>
           <td><strong>CSICode</strong></td>
           <td><strong>ConstructionPhase</strong></td>
           <td><strong>SpecialNotes</strong></td>
           <td><strong>SupplierCompanyName</strong></td>
           <td><strong>SupplierAddress1</strong></td>
           <td><strong>SupplierAddress2</strong></td>
           <td><strong>SupplierCity</strong></td>
           <td><strong>SupplierState</strong></td>
           <td><strong>SupplierZip</strong></td>
           <td><strong>SupplierInternationalAddress</strong></td>
           <td><strong>SupplierContactName</strong></td>
           <td><strong>SupplierPhone</strong></td>
           <td><strong>SupplierFax</strong></td>
           <td><strong>MAPCONVendorNum</strong></td>
           <td><strong>ORTRequisitionNum</strong></td>
        </tr>
        <cfoutput query="GetSpreadsheetValues">
        <tr>
           <td>#Qty#</td>
           <td>#Unit#</td>
           <td>#Description#</td>
           <td>#MfgName#</td>
           <td>#MfgPartNum#</td>
           <td>#SupplierPartNum#</td>
           <td>#MAPCONStockNum#</td>
           <td>#UnitCost#</td>
           <td>#WTunit#</td>
           <td>#CUunit#</td>
           <td>#MfgLeadTime#</td>
           <td>#Station#</td>
           <td>#ROSDate#</td>
           <td>#CruiseNum#</td>
           <td>#ProjectCode#</td>
           <td>#ActivityEventCode#</td>
           <td>#StockRoom#</td>
           <td>#MarkFor#</td>
           <td>#PricingSource#</td>
           <td>#FundingDivision#</td>
           <td>#WBSChargeCode#</td>
           <td>#ShipTo#</td>
           <td>#ShipToOtherAddress#</td>
           <td>#CSICode#</td>
           <td>#ConstructionPhase#</td>
           <td>#SpecialNotes#</td>
           <td>#SupplierCompanyName#</td>
           <td>#SupplierAddress1#</td>
           <td>#SupplierAddress2#</td>
           <td>#SupplierCity#</td>
           <td>#SupplierState#</td>
           <td>#SupplierZip#</td>
           <td>#SupplierInternationalAddress#</td>
           <td>#SupplierContactName#</td>
           <td>#SupplierPhone#</td>
           <td>#SupplierFax#</td>
           <td>#MAPCONVendorNum#</td>
           <td>#ORTRequisitionNum#</td>
        </tr>
    </cfoutput>
    </table>  --->