This content has been marked as final. Show 7 replies
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.
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
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
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
1,432,"I'm some string data',99
1,432,|I'm some "string" data|,99
Excel has not trouble opening a file with a defined text qualifying
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.
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 :)
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.
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.
The CFX_excel did the job, I just got it going :)