Hi,

I'm using the following query/<cffifle> (see below) command to create a .csv with information from our SQL 2008 database. However, I'm having a problem when exporting data fields with certain characters in them. Specifically, line breaks and ' " '

For example, one of our product's description may be:

18.5"  HP LED BL Monitor

The " character disrupts the format of the field, because field-transitions are marked with double quotes.

Is there a way to escape this character? (And perhaps others, such as line breaks and special characters)

Alternatively, is there a better way to export specific inventory information other than the current method?

Notes :

This is a verbose function that runs in a web browser. This detail isn't necessary.

I can provide the full .cfm file as well as the ouput if necessary.

-------------------------------------------------------------------------------------------------------------------------------

<cfoutput>Getting Active Products..</cfoutput><br>

                    <cfquery name="getActiveProducts" datasource="DATABASE">

                                        USE DATABASE

                                        SELECT dbo.Item.ID, dbo.Item.ItemLookupCode, dbo.Item.Description, dbo.item.ExtendedDescription, PriceA, dbo.Department.Name +'+'+ dbo.category.Name as CatSubCat, dbo.Item.Quantity - dbo.Item.QuantityCommitted as AVAIL

                                        FROM Item

                                        JOIN Department

                                        ON Item.DepartmentID = Department.ID

                                        JOIN Category

                                        ON Item.CategoryID = Category.ID

                                        Where WebItem = 'True'

                                        Order By ItemLookupCode

                    </cfquery>

..

                              <cffile action="write"

                              file="\\server\storage\X-Cart\ExportFile\ExportFile.csv"

                              output="[PRODUCTS],,,,"

                              addnewline="yes">

..                    

                              <cffile action="append"

                              file="\\server\storage\X-Cart\ExportFile\ExportFile.csv"

                              output= "!PRODUCTID,!PRODUCTCODE,!PRODUCT,!DESCR,!PRICE,!CATEGORY,!AVAIL,!FORSALE,!CATEGORYID"

                              addnewline="yes">

..

                    <cfoutput query="getActiveProducts">

                              <cffile action="append"

                              output='"#trim(ID)#","#trim(ItemLookupCode)#","#trim(Description)#","#trim(ExtendedDescription)#","#trim(PriceA)#","#trim(CatSubCat)#","#trim(AVAIL)#","Y",""'

                              file="\\server\storage\X-Cart\ExportFile\ExportFile.csv"

                              addnewline="yes">

                    </cfoutput>

..<br>

<cfoutput>End of Getting Active Products</cfoutput><br>

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The ouput then becomes

"2603","A5V72A8ABA",""18.5"  HP LED BL Monitor"","""","109.0000","Monitor+LED-Backlit","52","Y","" which will not import correctly.

The field should be totally enclosed by separating characters:

[CHAR]18.5"  HP LED BL Monitor[CHAR]

Any help on this matter would be greatly appreciated.

Thank You