Good evening, everyone!
I recently had an unexpected upgrade to Lucee 5.2.8.50 at hosting (was running 4.5).
After getting assistance for a datasource configuration (allow multiple queries), everything has been running smooth until my user attempted to export data to a CSV file.
For some reason, it is exporting the data within quotes and this tricks Excel into stuffing all of the data into one cell.
Is there a setting somewhere that is causing this? I’ve looked, but I don’t recognize anything and don’t want to start experimenting with settings I am not sure of.
Here’s a copy of the code in question - if I edit the export file to remove the double quotes, it reads normally.
Rosters<cfset getOrderByID = ExportCFC.getOrderByID(session.ExportCID)>
<cfset getExportSchoolName = ExportCFC.getExportSchoolName(session.ExportCID)>
<cfset exportFileName = getExportSchoolName.ClassName>
<cfif getOrderByID.recordcount GTE 66000>
<!--- Excel can only handle 66000 rows --->
<cfoutput>Too many records - alert administrator</cfoutput>
<cfelse>
<!--- Create object, create header row --->
<cfset sbOutput = CreateObject("java","java.lang.StringBuffer").Init()>
<cfset sbOutput.Append('StudentName,ParentName,ParentEmail,ParentPhone,TeacherName,Grade,Aftercare,Allergies' & Chr(13) & Chr(10))>
<!--- Set the initial value for oID --->
<cfset intOrderID = -1 >
<!--- loop through records --->
<cfloop query ="getOrderByID">
<cfif (getOrderByID.oID NEQ intOrderID)>
<cfset intOrderID = getOrderByID.oID>
<cfset sbOutput.Append('#trim(oChildFirstName)#,#trim(oChildLastName)#,#trim(oParentFirstName)#,#trim(oParentLastName)#,#trim(oParentEmail)#,#trim(oParentPhone)#,#trim(oChildTeacherLastName)#,#trim(oChildGrade)#,#trim(oChildAftercare)#,#trim(oChildAllergies)#' & Chr(13) & Chr(10))>
</cfif>
</cfloop>
<cfset swapNames = serialize(sbOutput.ToString())>
<cffile action = "WRITE" file="#ExportPath#\#exportFileName#.csv" output="#swapNames#">
<cflocation url = "#ExportPath#/#exportFileName#.csv">
</cfif>