Skip to main content
A
ACS LLC
Inspiring
May 29, 2008
Question

Removing Quotes from strings

  • May 29, 2008
  • 6 replies
  • 2730 views
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
This topic has been closed for replies.

6 replies

A
ACS LLCAuthor
Inspiring
May 29, 2008
The CFX_excel did the job, I just got it going :)
N
Newsgroup_User
Inspiring
May 29, 2008
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.
N
Newsgroup_User
Inspiring
May 29, 2008
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.
A
ACS LLCAuthor
Inspiring
May 29, 2008
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 :)
N
Newsgroup_User
Inspiring
May 29, 2008
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.


D
Dan_Bracuk
Inspiring
May 29, 2008
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.
A
ACS LLCAuthor
Inspiring
May 29, 2008
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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices