asked on

Finding and replacing characters between ""

Hello,

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.

Chris

chrissp26

ASKER

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

<cfoutput>
#fileContentsArray#
</cfoutput>

usachrisk1983

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.

chrissp26

ASKER

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

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

ASKER CERTIFIED SOLUTION

CetusMOD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Finding and replacing characters between &quot;&quot;

Finding and replacing characters between ""