Escaping Quotes while Creating .csv files

Report · Nov 23, 2012

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>

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">

..

<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

Report · Nov 24, 2012

You could, within terms likely to contain quotes, escape each double quote with a further double quote and each single quote with a further single quote, like this:

</cfoutput>

Report · Nov 24, 2012

Thanks for your reply. However, this didn't seem to work for me. The output becomes

[PRODUCTS],,,,

!PRODUCTID,!PRODUCTCODE,!PRODUCT,!DESCR,!PRICE,!CATEGORY,!AVAIL,!FORSALE,!CATEGORYID

"2603","A5V72A8ABA","18.5" HP LED BL Monitor","A:

LineBreak

Test!*","109.0000","Monitor+LED-Backlit","48","Y",""

To further describe my situation, I'm importing the contents of this .csv file into X-Cart (online shopping cart). What is happening is that the " symbol, which needs to be adjacent to certain terms (i.e. 19" monitor 6" Cable), is being displaced.

The previous .csv line would produce a product title of

18.5 HP LED BL Monitor"

When obviously, it needs to be 18.5" HP LED BL Monitor. This is because the PRODUCT column is being imported twice, once for 18.5, and again for HP LED BL Monitor". The two fields are simply concatenated and applied as the product name.

I have successfully used a semicolon in place of commas, with the only stipulation that a line break will disrupt the import feed, and putting quotes around the field to escape this character yields the very results I'm finding here.

Report · Nov 24, 2012

Could you please show us the correct (that is, expected) output line for the product with ID 2603.

Report · Nov 24, 2012

[PRODUCTS],,,,

!PRODUCTID,!PRODUCTCODE,!PRODUCT,!DESCR,!PRICE,!CATEGORY,!AVAIL,!FORSALE,!CATEGORYID

"2603","A5V72A8ABA","18.5"" HP LED BL Monitor","a:

LineBreak

Test!*","109.0000","Monitor+LED-Backlit","48","Y",""

If I simply put another " symbol between the 5 and the ", then it is correct.

Report · Nov 24, 2012

It is now confusing me. What's the point of the line break? As things now stand, the line break is simply a character in the value of an item in the DESCR column. That value is "a:[LineBreak]Test!*". Is that your intention?

This is how I read it:

PRODUCTID:"2603"

PRODUCTCODE:"A5V72A8ABA"

PRODUCT:"18.5"" HP LED BL Monitor"

DESCR:"a:[LineBreak]Test*"

PRICE:"109.0000"

CATEGORY:"Monitor+LED-Backlit"

AVAIL:"48"

FORSALE:"Y"

CATEGORYID:""

Report · Nov 24, 2012

cpdistributing wrote:
When obviously, it needs to be 18.5" HP LED BL Monitor. This is because the PRODUCT column is being imported twice, once for 18.5, and again for HP LED BL Monitor". The two fields are simply concatenated and applied as the product name.

That need not be a problem. It could proceed like this:

Report · Nov 24, 2012

I'm not sure I follow you.
The line break is simply in the description for testing purposes. In our Point Of Sale, item descriptions may have line breaks in them. My intention was only to ensure that the csv would not be affected by the line break.

Now, for your suggestion:

I'm not sure how this can be applied to the cfoutput for any fields potentially with a " symbol.

Report · Nov 25, 2012

cpdistributing wrote:
I'm not sure I follow you.
The line break is simply in the description for testing purposes. In our Point Of Sale, item descriptions may have line breaks in them. My intention was only to ensure that the csv would not be affected by the line break.

Then we mean the same thing. I was just saying that, applying the line break on the forum page, as you did, made it a bit confusing. Look at the area between "a: and Test!*". It can mean one line break, two or even three!

A line break is just a character (or two). Think, for example, of ColdFusion's chr(10) or chr(13). Hence my suggestion to represent the description as "a:[LineBreak]Test*", assuming just one line break.
<cfset product_dim = "18.5">
<cfset product_descr = "HP LED BL Monitor">

<cfset product = product_dim & '"" ' & product_descr>
I'm not sure how this can be applied to the cfoutput for any fields potentially with a " symbol.

You said you arrive at 18.5" HP LED BL Monitor by concatenation. How? All I am saying is, whatever procedure you use, why don't you just add the double double quotes during concatenation.

Report · Nov 25, 2012

The concatenation is erroneous. The " causes this to occur. I need to prevent this (or utilize it to my advantage).

The field is

18.5" HP LED [LINEBREAK] Monitor

We have hundreds of items with a similar format (across different fields).

The correct .csv should read

,18.5" HP LED BL [LINEBREAK] Monitor,

Which wouldn't import because of the special character.

In order to prevent special characters (primarily line breaks) from disrupting the format, the field needs to be enclosed like this:

,"18.5" HP LED BL [LINEBREAK] Monitor",

The problem lies with the fact that the " symbol, which in this case, surrounds 18.5, is considered one field, so the " (inches) symbol is removed.
I'm not sure how the line should read, but the following does work,

,"18.5"" HP LED BL [LINEBREAK] Monitor",

The field is split in two (and concatenated) by the " symbols, but with the extra " inserted, the 5 retains its symbol.

This finally results in the correct output after import, which is

18.5" HP LED BL

Monitor

Report · Nov 26, 2012

Are you saying the Decription comes in directly from the database as 18.5" HP LED [LINEBREAK] Monitor? If so, then the code I gave earlier should have solved the problem.

Perhaps the quotes are confusing. Here is the suggestion once more, with an explanation.

Report · Nov 27, 2012

Yes.

Data flows like this:

2008 SQL Srvr --> ColdFusion Server --> .csv

or

ID,SKU,Product,Description,etc --> ColdFusion Server --> .csv

I will provide a .csv line before and after your suggestion. (Please keep in mind the line break is arbitrary and will be placed in either "product" or "description")

Before :

[PRODUCTS]

!PRODUCTID,!PRODUCTCODE,!PRODUCT,!DESCR

"2603","A5V72A8ABA","18.5" HP LED BL Monitor","A

LineBreak

Test"

After:

[PRODUCTS]

!PRODUCTID,!PRODUCTCODE,!PRODUCT,!DESCR

"2603","A5V72A8ABA","18.5" HP LED BL Monitor","A

LineBreak

Test"

As you can see, no real changes were made. Perhaps I am not insertting the code corectly? Here's the reveleant code :

<cffile action="append"

output='"#trim(ID)#","#trim(ItemLookupCode)#","#trim(description)#","#trim(extendedDescription)#"'

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

addnewline="yes">

</cfoutput>

I really appreciate your continued help.

Report · Nov 27, 2012

Strange! What happens when you use <cfloop query="getActiveProducts"> instead?

Report · Nov 29, 2012

It looks like the same thing:

"2603","A5V72A8ABA","18.5" HP LED BL Monitor","A[LineBreak]Test",109.0000,Monitor+LED-Backlit,45,Y,

Report · Nov 29, 2012

Place the following test code just before </cfoutput>:

"#trim(ID)#","#trim(ItemLookupCode)#","#trim(description)#","#trim(extendedDescription)#"<br>

Do you get "18.5" HP LED BL Monitor" or "18.5"" HP LED BL Monitor"?

Adobe Community

Escaping Quotes while Creating .csv files

18.5 HP LED BL Monitor"