Cold Fusion 2018: how to save .xlsx file as .csv

Question

CF2018 on Win2019 Trying to save user-uploaded .xlsx file as .csv file to be processed into Oracle database. Running this code, which completes without errors, but the result is not legible:

<cfspreadsheet action = "read"
                format="csv"
                src="c:\bin\Nov_sales.xlsx"
                name="foo"
                > 

<cfspreadsheet action="write" 
                filename='c:\bin\Nov_sales.csv' 
                format="csv" 
                name="foo"  
                overwrite=true>

The result in .csv file looks like this:

504b 0304 1400 0808 0800 d260 8f51 0000
0000 0000 0000 0000 0000 0b00 0000 5f72
656c 732f 2e72 656c 73ad 92c1 4a03 3110
865f 25cc bd9b 6d05 1169 da8b 08bd 89d4
.....

What am I missing???

Answer 1

Presuming your excel file has a header record, what you should do is first read the excel file into a query object. From there, you can then export the query object into a csv by writing the file using a <cfoutput> loop.

<cfspreadsheet action="read" src="c:\bin\Nov_sales.xlsx" query="qryExcel">

<cfoutput query="qryExcel">
    <cfset line = "#col1#,#col2#,#col3#,#col4#,#col5#">
    <cffile action="append" file="c:\bin\Nov_sales.csv" output="#line#">
</cfoutput>

Answer 2

"The cfspreadsheet tag always writes spreadsheet data as an XLS file. To write HTML variables or CSV variables as HTML or CSV files, use the cffile tag."

https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-r-s/cfspreadsheet.html

Or in other words, when using cfspreadsheet action="write", the format attribute does not specify the output format, it is specifying the input format - because it could be any one of a CF spreadsheet object, a query, a CSV string, or HTML string.

You're seeing raw bytes in your .csv file, because it's really an Excel file regardless of the .csv filename extension.

Answer 3

After reading:

"The cfspreadsheet tag always writes spreadsheet data as an XLS file. To write HTML variables or CSV variables as HTML or CSV files, use the cffile tag."

I got it to work, something like this:

<cfquery name="ExportData" datasource="yourdatasource">

    /* Only specify the columns you need to display in the query */

    SELECT *    
    FROM instructor_student_session 

    </cfquery>
<cfset doc_nm = "instructor_student">
<cfscript>
        //Use an absolute path for the files.
        theDir="c:\bin\";
        theXLSXFile="#doc_nm#.xlsx";
        //Create two empty ColdFusion spreadsheet objects.
        theSheet = SpreadsheetNew(true); // creates as xlsx
        //Populate each object with a query.
        //SpreadsheetAddRows(theSheet,ExportData); //no headers
        SpreadsheetAddrows(theSheet,ExportData,1,1,true,[""],true); // add headers
    </cfscript>

<cfset theCSVFile = "#doc_nm#.csv">

<cfspreadsheet action="write" filename="#theDir##theXLSXFile#" name="theSheet" sheetname="Students" overwrite="true">

<cfspreadsheet action = "read" src="#theDir##theXLSXFile#" format="csv" name="csvdata" sheet="1">

<cffile action="write" file="#theDir##theCSVFile#" output="#csvdata#">

<cfheader name="Content-disposition" value="attachment;filename=#theCSVFile#">

<cfcontent type="application/vnd.ms-excel" file="#theDir##theCSVFile#" deletefile="yes">