7 Replies Latest reply on May 29, 2008 12:02 PM by ACS LLC

    Removing Quotes from strings

    ACS LLC Level 1
      I am creating a CSV file using CF, well at least I'm trying to, I'm having all sorts of issues with format.

      Can anybody point me to the correct format for a CSV? Should the fields be surrounded by quotes for example?

      One problem is that if any of the fields contain commas or quotes, it messes up in Excel when I open the file.

      What I need to do is remove all quotes and commas. I had no trouble removing commas, but when I remove quotes it does not remove all of them, and then the data is out of alignment in the cells.

      I use this -->
      <CFSET lead_a1_1 = #replacenocase(getdata.lead_a1,",","","all")#>

      followed by this

      <CFSET lead_fn = #replacenocase(lead_fn,"""","","all")#>

      I am sure this could also go into one expression?

      appreciate any help with removing all these quotes and commas, and also pointers for the correct CSV format for Excel. Btw I don't want to output in the browser but actually create a .CSV file

      Thanks

      Mark
        • 1. Re: Removing Quotes from strings
          Dan Bracuk Level 5
          use excel to create a csv file. Make sure your data contains commas and quotes. Close the file and reopen it with notepad. That will give you the desired format.
          • 2. Re: Removing Quotes from strings
            ACS LLC Level 1
            The problem is that I also need to make sure that the fields don't contain commas or quotes, or they mess up the layout, but as above, I had a heck of time trying to strip out all quotes

            I might give this a shot
            http://www.cftagstore.com/tags/cfxexcel.cfm

            The only issue with this is that it takes a query and creates the file, but I may want to add in my own 'created' data with the query data.

            i did create a CSV manually within Excel, then opened it up in notepad, and noticed no quotes, just plain comma delimited text
            • 3. Re: Removing Quotes from strings
              Level 7
              Investigate the concept of a 'text qualifier' in the CSV format. It is
              a charter(s) that tell the system reading the file, anything between
              this and the next instance of this character is text so ignore any
              comma's or other data that looks like a field separation character. The
              common character is a single quote (') but if your data contains single
              quotes as data, then another character would be used.

              Your data could then look something like.

              1,432,'I am some "string" data',99
              OR
              1,432,"I'm some string data',99
              OR
              1,432,|I'm some "string" data|,99

              Excel has not trouble opening a file with a defined text qualifying
              character.

              If you insist on replacing the characters in your data and want to do it
              in one pass then you are looking at regular expressions.

              <cfset lead_fn = rereplace(lead_fn,'[",]','','all')>

              Note my used of mixed single and double quotes to avoid the necessity of
              double escaping the quote character to be searched for.


              • 4. Re: Removing Quotes from strings
                ACS LLC Level 1
                The issue is that when you open a CSV with Excel installed on the computer it does not ask for a qualifier, only if you open it manually does it do that

                I just installed the CFX_excel tag, and the demo looks interesting, I THINK I can pull it off with this :)
                • 5. Re: Removing Quotes from strings
                  Level 7
                  ACS LLC wrote:
                  > The issue is that when you open a CSV with Excel installed on the computer it
                  > does not ask for a qualifier

                  Yeah it will go with the default, single quote qualifier. Good Old MS
                  making it hard for everybody who colors outside of the lines.
                  • 6. Re: Removing Quotes from strings
                    Level 7
                    Ian Skinner wrote:
                    >
                    > Yeah it will go with the default, single quote qualifier. Good Old MS
                    > making it hard for everybody who colors outside of the lines.


                    Sorry, I meant the default double quote qualifier.
                    • 7. Re: Removing Quotes from strings
                      ACS LLC Level 1
                      The CFX_excel did the job, I just got it going :)