Create CSV file from a query?

[Guest]

Greetings

I am attempting to create a csv file from a query that would pop up for downloading.

I found this code:

<!--- Create the file with column headers. It is crucial that you have all the outputted text on one line with no spaces between the comma and the next column header.--->

<cffile action="write"

file="C:\TEMP\Analysis.csv"

output="Assigned Technician,Received Date,Received Time,Priority,Due Date,User Name,User Office,Assigned Date,Assigned Time,Completed Date,Completed Time,Hours to Complete,Description"

addnewline="yes">

<!--- Output the contents of the qryResults query ---> <cfoutput> <cfloop query="qryResults">

<!--- Append (Insert) the contents of the query into the already created CSV file. Make sure that you keep all the output on one line exactly like the column header--->

<cffile action="append"

file="C:\TEMP\Analysis.csv"

output= "#(tech_lname)#, #TRIM(queue_dte)#, #TRIM(queue_tme)#,  #TRIM(queue_priority)#, #TRIM(due_dte#, #TRIM(user_name)#,  #TRIM(office_name)#, #TRIM(assign_date)#, #TRIM(assign_time)#, #TRIM(repair_dte)#, #TRIM(repair_time)#, # TRIM(repair_ttc)#, #Left(q_description,128)#"

addnewline="yes">

</cfloop>

</cfoutput>

<!--- This will pop up the file for downloading. You can open it in excel or any other text editing software. --->

<cflocation url="#your.url#/Analysis.csv">

Something is wrong with the query.

Here's the error:

Invalid CFML construct found on line 135 at column 105. 

ColdFusion was looking at the following text:

#

The CFML compiler was processing:

An expression beginning with TRIM, on line 135, etc.

135 : output= "#(tech_lname)#

Any help would be appreciated....

Thanks in advance

Norman

[Dan_Bracuk]

You are missing a closing bracket when trimming due_dte.

[Guest]

Dan:

Thanks for picking that up- I did put in the missing bracket, but still get the same error.

[Dan_Bracuk]

Given that the error is with your first field, it might have something to do with the brackets around the variable name.

By the way, did you know that cfoutput has a query attribute that makes this code redundant?

<cfoutput> <cfloop query="qryResults">

Also, your code will probably run faster if you use cfsavecontent to create a variable for your file contents.  Then you only have to write to it once.

[Guest]

Dan:

I was following the code as I found it- always nice to get a working solution already for use- so I didn't notice that, but it works, so I didn't mess with it.

Thanks Again.

Norman

[Guest]

Well, maybe I still have a question on the storage and reteival of the file via a URL link.

As you can see in the code, I was saving and appending the file in C:\TEMP which can't be accessed via a URL- I tried to store it via a fully qualified URL to a directory in the Web root- no luck.

I'll keep trying....

[Dan_Bracuk]

You can always use cfcontent to make the file available.

[Guest]

Dan:

Not to be a nudge, but what would that look like?

I found: <cfcontent file="#fPath#" deletefile="true" type="application/csv" />

Are you saying I could still store it in the user's C:\TEMP directory and that it would delete it from there when the user saves it wherever they want?

Norman

[Dan_Bracuk]

You can't store anything on the user's machine in any directory.  You can only store files on your application server.  For many developers, these are the same machine when they are writing code.  It sounds like you are one of these developers.

[Guest]

Dan- solved the problem- it was a bracket issue. Going blind in my old age- sorry.....

Thanks for your help.