14 Replies Latest reply on Nov 29, 2012 7:23 AM by BKBK

    Escaping Quotes while Creating .csv files

    cpdistributing

      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(ExtendedDescri ption)#","#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

        • 1. Re: Escaping Quotes while Creating .csv files
          BKBK Adobe Community Professional & MVP

          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 query="getActiveProducts">

           

          <cfset description = replace(getActiveProducts.description,'"','""','all')>

          <cfset extendedDescription = replace(getActiveProducts.extendedDescription,'"','""','all')>

          <cfset catSubCat = replace(getActiveProducts.catSubCat,'"','""','all')>

           

          <cfset description = replace(description,"'","''","all")>

          <cfset extendedDescription = replace(extendedDescription,"'","''","all")>

          <cfset catSubCat = replace(catSubCat,"'","''","all")>

           

          <cffile action="append" output='"#trim(ID)#","#trim(ItemLookupCode)#","#trim(description)#","#trim(extendedDescri ption)#","#trim(PriceA)#","#trim(catSubCat)#","#tr im(AVAIL)#","Y",""' file="\\server\storage\X-Cart\ExportFile\ExportFile.csv" addnewline="yes">

           

          </cfoutput>

          1 person found this helpful
          • 2. Re: Escaping Quotes while Creating .csv files
            cpdistributing Level 1

            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.

            • 3. Re: Escaping Quotes while Creating .csv files
              BKBK Adobe Community Professional & MVP

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

              • 4. Re: Escaping Quotes while Creating .csv files
                cpdistributing Level 1

                [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.

                • 5. Re: Escaping Quotes while Creating .csv files
                  BKBK Adobe Community Professional & MVP

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

                  • 6. Re: Escaping Quotes while Creating .csv files
                    BKBK Adobe Community Professional & MVP

                    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:

                     

                    <cfset product_dim = "18.5">

                    <cfset product_descr = "HP LED BL Monitor">

                    <!--- '"" ' represents single quote followed by 2 double quotes followed by space followed by single quote--->

                    <cfset product = product_dim & '"" ' & product_descr>

                    • 7. Re: Escaping Quotes while Creating .csv files
                      cpdistributing Level 1

                      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:

                       

                      <cfset product_dim = "18.5">

                      <cfset product_descr = "HP LED BL Monitor">

                      <!--- '"" ' represents single quote followed by 2 double quotes followed by space followed by single quote--->

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

                      • 8. Re: Escaping Quotes while Creating .csv files
                        BKBK Adobe Community Professional & MVP

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

                        <!--- '"" ' represents single quote followed by 2 double quotes followed by space followed by single quote--->

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

                        • 9. Re: Escaping Quotes while Creating .csv files
                          cpdistributing Level 1

                          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

                          • 10. Re: Escaping Quotes while Creating .csv files
                            BKBK Adobe Community Professional & MVP

                            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.

                             

                            <!--- The quotes in the terms '"','""' are: single double single [comma] single double double single --->

                            <cfset description = replace(getActiveProducts.description,'"','""','all')>

                            <cfset extendedDescription = replace(getActiveProducts.extendedDescription,'"','""','all')>

                            <cfset catSubCat = replace(getActiveProducts.catSubCat,'"','""','all')>

                             

                            <!--- The quotes in the terms "'","''" are: double single double [comma] double single single double --->

                            <cfset description = replace(description,"'","''","all")>

                            <cfset extendedDescription = replace(extendedDescription,"'","''","all")>

                            <cfset catSubCat = replace(catSubCat,"'","''","all")>

                            • 11. Re: Escaping Quotes while Creating .csv files
                              cpdistributing Level 1

                              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 :

                               

                              <cfoutput query="getActiveProducts">

                               

                                        <!--- The quotes in the terms '"','""' are: single double single [comma] single double double single --->

                                        <cfset description = replace(getActiveProducts.description,'"','""','all')>

                                        <cfset extendedDescription = replace(getActiveProducts.extendedDescription,'"','""','all')>

                               

                                        <!--- The quotes in the terms "'","''" are: double single double [comma] double single single double --->

                                        <cfset description = replace(description,"'","''","all")>

                                        <cfset extendedDescription = replace(extendedDescription,"'","''","all")>

                               

                                        <cffile action="append"

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

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

                                        addnewline="yes">

                               

                              </cfoutput>

                               

                               

                               

                              I really appreciate your continued help.

                               

                               


                              • 12. Re: Escaping Quotes while Creating .csv files
                                BKBK Adobe Community Professional & MVP

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

                                • 13. Re: Escaping Quotes while Creating .csv files
                                  cpdistributing Level 1

                                  It looks like the same thing:

                                   

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

                                  • 14. Re: Escaping Quotes while Creating .csv files
                                    BKBK Adobe Community Professional & MVP

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