CODEWITHSUNDEEP
coldfusioncoldfusion-10cfmlrailocfspreadsheet
Matthew Louis
Matthew Louis

Reputation: 45

cfspreadsheet save as .csv, Excel says "The file format and extension of FILE.csv don't match."

I've created a cold fusion page to output a client list from MYSQL into a CSV file for easy uploading to SalesForce.com

I can generate the file with all the correct information. However, when I try to open it with excel I get the error: "The file format and extension of 'SalesForceDailyLeads-20160613125138.csv' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?" I can open it(excel for MAC), but it appears to me that CFSpreadsheet is not creating a legit .csv file and is instead making a xlsx.

    <cfset FileCSV = "SalesForceDailyLeads-#dateformat(getBatch.BATCH,"yyyymmdd")##timeformat(getBatch.BATCH,"HHmmss")#.csv" >
    <cfset filename = "/SF/#fileCSV#">



    <cfset s = spreadsheetNew() >
    <cfset spreadsheetAddRow(s, "FIRST, LAST, MIDDLE, STREET, CITY, ZIP, STATE")>

    <cfinclude template="SFgetList.cfm">

    <cfset spreadsheetAddRows(s, getList)>

    <cfspreadsheet
        action="write"
        overwrite = "true"
        format ="csv"
        name ="s"
        filename ="#filename#"
        >

If I make an XLS file I have no issues like I do with CSVs. Is this a problem with the code, CFSpreadsheet, or excel(for mac)? Can I fix it?

Upvotes: 1

Views: 3319

Answers (2)

Matthew Louis
Matthew Louis

Reputation: 45

Thanks! Of course, right after I posted this I found the docs and saw that I was using it incorrectly and I used CFFILE instead. I used a script/function I found to do this. It took a little work however, to convert the query into CSV- luckily someone else already did it. In case anyone care to see it: I got the querytoCSV script here: https://gist.github.com/CreativeNotice/2775372

<cfscript>
        /**
         * queryToCsv
         * Allows us to pass in a query object and returns that data as a CSV.
         * This is a refactor of Ben Nadel's method, http://www.bennadel.com/blog/1239-Updated-Converting-A-ColdFusion-Query-To-CSV-Using-QueryToCSV-.htm
         * @param  {Query}      q               {required}  The cf query object to convert. E.g. pass in: qry.execute().getResult();
         * @param  {Boolean}    hr              {required}  True if we should include a header row in our CSV, defaults to TRUE
         * @param  {String}     d               {required}  Delimiter to use in CSV, defaults to a comma (,)
         * @return {String}                                         CSV content
         */
    public string function queryToCsv(required query q, required boolean hr = true, required string d = ","){



var colNames    = listToArray( lCase(arguments.q.columnlist) );
        var newLine     = (chr(13) & chr(10));
        var buffer      = CreateObject('java','java.lang.StringBuffer').Init();
        // Check if we should include a header row
        if(arguments.hr){
            // append our header row
            buffer.append(
              ArrayToList(colNames,arguments.d) & newLine
            );
        }
        // Loop over query and build csv rows
        for(var i=1; i <= arguments.q.recordcount; i=i+1){
            // this individual row
            var thisRow = [];
            // loop over column list
            for(var j=1; j <= arrayLen(colNames); j=j+1){
                // create our row
                thisRow[j] = replace( replace( arguments.q[colNames[j]][i],',','','all'),'""','""""','all' );
            }
            // Append new row to csv output
            buffer.append(
                JavaCast( 'string', ( ArrayToList( thisRow, arguments.d ) & iif(i < arguments.q.recordcount, "newLine","") ) )
            );
        }
        return buffer.toString();
    };
</cfscript>
<cfinclude template="getDups.cfm">
<cfinclude template="SFgetList.cfm">
<cfset FileCSV = "SalesForceDailyLeads-#dateformat(getBatch.BATCH,"yyyymmdd")##timeformat(getBatch.BATCH,"HHmmss")#.CSV" >
<cfset filename = "/mnt/nas-share/data/feed/SF/#fileCSV#">

<cfset qc = #queryToCsv(getList, false, ",")# >
<cfoutput>#qc#</cfoutput>
<cfset heads= "FIRST, LAST, MIDDLE, STREET, CITY, ZIP, STATE">
>
<cffile
    action = "write"
    file = #filename#
    output = #heads#
    addNewLine = "yes"
    fixnewline = "no">

<cffile
    action = "append"
    file = #filename#
    output = #qc#
    addNewLine = "yes"
    fixnewline = "no">

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Use cffile, not cfspreadsheet to create the files. Per the documentation:

The cfspreadsheet tag writes only XLS[X] format files. To write a CSV file, put your data in a CSV formatted string variable and use the cffile tag to write the variable contents in a file.

Upvotes: 3

Related Questions