Writing a CSV file

[ACS LLC]

I have a CFLOOP that loops over some data retrieved from a SQL query, within the loop I create a CSV file by using CFFILE to APPEND data to the file.

the problem is that when I open the file in Excel it looks ok (aside from one long number deciding to go into scientific notation), but the problem is that when I resave it in Excel it tells me that it's not in the correct CSV format. I save it, and bring the data back, and sure enough some of the longer numbers are messed up, losing a significant amount of accuracy.

Is there a particular way I need to write a CSV, perhaps setting headers in a special way?

Thanks

Mark

[tclaremont]

Do your cfheader and cfcontent lines look like this?

<cfheader name="Content-Disposition" value="filename=ReportName.csv">
<CFCONTENT reset="yes" TYPE="application/msexcel">

[ACS LLC]

I don't have those tags in there right now, just a CFFILE within the loop creating what is basically a tXT file with a CSV extension

[tclaremont]

Here is the way I do it...

<cfquery name="GetAll" datasource="#DSN#">
SELECT Field1, Field2, Field3, Field4
FROM tblName
</cfquery>


<cfheader name="Content-Disposition" value="filename=ReportName.csv">
<CFCONTENT reset="yes" TYPE="application/msexcel">
<CFOUTPUT QUERY="GetAll">
"#GetAll.Field1#","#GetAll.Field2#","#GetAll.Field3#","#GetAll.Field4#"
</cfoutput>

[ACS LLC]

This creates the file? I tried it with my data.. no file

[Newsgroup_User]

tclaremont wrote:
> Here is the way I do it...
>
> <cfquery name="GetAll" datasource="#DSN#">
> SELECT Field1, Field2, Field3, Field4
> FROM tblName
> </cfquery>
>
>
> <cfheader name="Content-Disposition" value="filename=ReportName.csv">
> <CFCONTENT reset="yes" TYPE="application/msexcel">
> <CFOUTPUT QUERY="GetAll">
> "#GetAll.Field1#","#GetAll.Field2#","#GetAll.Field3#","#GetAll.Field4#"
> </cfoutput>
>


It should be noted that this solution sends a file to the client browser
which most would recognize as an Excel file and bring up an open or save
dialog box for this.

The original poster was talking about just writing a csv text file to
the server. I have never had trouble doing this, but it sounds like
some of your data may be unusually large numbers. How exactly are you
writing the file? What does the file look like before excel gets its
hands on it? I.E. what does it look like in a plain text editor such as
notepad.

[ACS LLC]

that's correct I don't want to output to the client, but create a file

the data looks fine when I open it in a text editor

This is what Im using

<CFFILE ACTION="Append" FILE="#csvfile#"

OUTPUT="lead_uid,lead_fn,lead_ln,lead_email,lead_phone,lead_phone_full,lead_date,lead_aff1,lead_aff2,lead_aff3"

ADDNEWLINE="yes">


<CFLOOP QUERY="GetData"><CFFILE ACTION="Append" FILE="#csvfile#" OUTPUT="#Getdata.lead_uid#,#replacenocase

(GetData.lead_fn,","," ","ALL")#,#replacenocase(GetData.lead_ln,","," ","ALL")#,#replacenocase(GetData.lead_email,",","

","ALL")#,#replacenocase(GetData.lead_phone,","," ","ALL")#,01144#right(replacenocase(GetData.lead_phone,","," ","ALL"),

(len(replacenocase(GetData.lead_phone,","," ","ALL"))-1))#,#createODBCDATETIME(GetData.lead_date)#,#replacenocase

(GetData.lead_aff1,","," ","ALL")#,#replacenocase(GetData.lead_aff2,","," ","ALL")#,#replacenocase(GetData.lead_aff3,",","

","ALL")#"></CFLOOP>