• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Finding and replacing characters between ""


I have written a script that exports the content of a DB Table to a CSV file.

This has been done so that a user can edit the information externally and then import once they are done.

One of the fields of this table is a description and the description therefore, in places, requires the use of commas.

The use of commas obviously screws up the format of the document, but I think that if I surround the description field in "" before I export it, I can search for commas between those i.e. "This is a description, it also contains commas".

Upon import I can convert any commas between the "" into , (the ascii HTML symbol for a comma) and then swap them round again when the file is exported.

Could someone please show me how I can do a find replace in a string between 2 set characters i.e. " "
(I have been trying for hours but have given up and come here).

Thanks in advance.


  • 2
1 Solution
chrissp26Author Commented:
This is an example I have been "working on" but I can't get it to work :(

<cfset fileContentsArray = 'This is a long line of text that contains several commas, but only the ones surrounded by "speech marks, will be changed"'>
<cfset reg = '"([*a-z][*0-9])(,)([*a-z][*0-9])"'>
<cfset fileContentsArray = reReplace(fileContentsArray, reg , "#chr(44)#" , "ALL")>

If you surround a field in quotes, you don't need to worry about the commas for a CSV file.  This should be perfectly valid:

Column 1,Column 2,Column 3,Column 4
This will be in Column 1,This will be in column 2,"This, Will, Be, In, Column, 3",Column 4 here!

What you want to make sure you do is escape your quotes, so if there is a " in the column, double it up before you put it in the CSV file.  Example:

Column 1,Column 2,Description,Other Field
7,8,"This field has a ""quote"" in it",OK!

Let us know how it goes.
chrissp26Author Commented:
The only way I can seem to get this to work is like this:

<cfset menuList = arrayNew(1)>

<cfset fileContentsArray =
'Menu Type,Dish Name,Dish Description,Serves,Price,Disabled,Chefs Special,Vegetarian,#Chr(13)#
Snacks,10oz Rump Steak,"From the chargrill, served with chips and a choice of sauce",1,11.95,No,No,No,#Chr(13)#
Snacks,Wild Mushroom Risotto,"with parmesan, wafer and truffle oil",1,8.25,No,No,Yes,#Chr(13)#'
<cfset reg = '"([^"]*)(,)([^"]*)"'>
<cfset fileContentsArray = ReReplace(fileContentsArray,reg,'"\1&##44;\3"',"all")>
<cfset fileContentsArray = replace(fileContentsArray, Chr(13), "~", "ALL")>
<cfset fileContentsArray = listToArray(fileContentsArray, "~")>
<cfset fileContentsArrayDelete = arrayDeleteAt(fileContentsArray, 1)>
<cfset fileContentsArrayLength = arrayLen(fileContentsArray)>
<cfloop from="1" to="#fileContentsArrayLength#" index="idx">

      <cfset fileContentsArray[idx] = listToArray(fileContentsArray[idx], ",")>
      <cfset menuList[idx] = structNew()>
      <cfset menuList[idx].menuTypeID = fileContentsArray[idx][1]>
      <cfset menuList[idx].dishName = replace(fileContentsArray[idx][2], "&##44;", ",", "ALL")>
      <cfset menuList[idx].dishDescription = replace(fileContentsArray[idx][3], "&##44;", ",", "ALL")>
      <cfset menuList[idx].dishDescription = replace(fileContentsArray[idx][3], "<br>", Chr(13), "ALL")>
      <cfset menuList[idx].dishDescription = replace(fileContentsArray[idx][3], '"', "", "ALL")>
      <cfset menuList[idx].serves = fileContentsArray[idx][4]>
      <cfset menuList[idx].price = fileContentsArray[idx][5]>
      <cfset menuList[idx].disabled = fileContentsArray[idx][6]>
      <cfset menuList[idx].chefsSpecial = fileContentsArray[idx][7]>
      <cfset menuList[idx].vegetarian = fileContentsArray[idx][8]>

<cfdump var="#menuList#">

This is a stand alone script so it should work with a minimum of hassle. I can't get it to work by simply surrounding it with "". So I have had to use another resolution which is a regular expression. Which seems to be working so far.
PAQed with points refunded (500)

Community Support Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now