Removing Quotes from strings

[ACS LLC]

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

[Dan_Bracuk]

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.

[ACS LLC]

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

[Newsgroup_User]

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.

[ACS LLC]

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

[Newsgroup_User]

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.

[Newsgroup_User]

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.

[ACS LLC]

The CFX_excel did the job, I just got it going 🙂