Lucee cfquery to a CSV file not formatting correctly

Question

I am trying to take the result of a query and put it into a CSV file to then email to the user. All of the data in the CSV file is correct, but I'm also getting the HTML elements for some reason. Here is my code:

<cffunction name="ledgerTest" access="remote" >

    <cfquery name="ledgerCheck" datasource="RBDMS_UPDATE">
     SELECT * FROM dbo.OOGNS_Schedules 
    </cfquery>
     <cfoutput>  
                <cfsavecontent variable="myCSV">

                    <div>
                        <table border="1"> 
                            <thead align="center">
                                <th>Job Key</th>
                                <th>INSPECTOR</th>
                                <th>PERMIT USER</th>
                                <th>START DATE</th>
                                <th>END DATE</th>
                            </thead>
                            <cfloop query="ledgerCheck">
                                <tr align="left">
                                    <td>#JobKey#</td>
                                    <td>#Inspector#</td>
                                    <td>#permitUser#</td>
                                    <td>#StartDate#</td>
                                    <td>#EndDate#</td>
                                </tr> 
                            </cfloop>
                        </table>
                    </div>

            </cfsavecontent>
          </cfoutput>  
                <cffile action="write" file="temp/ok.csv" output="#myCSV#" />

</cffunction>

And here is the resulting CSV file (I blacked out peoples email address's):

I think that the issue may have to do with the tag because without it I don't get the data from the query. Without the tag the CSV file contains only my code exactly. Yet, regardless of where I move the tag the result is the same.

Lucee doesn't support spreadsheets (including `` and `spreadsheetNew()`) natively. — David Faber, Feb 22 '22 at 21:45

score 0 · Answer 1 · answered Mar 05 '20 at 16:57

0

You write a HTML table to a a file which you call .csv. This feels very wrong. You can write this to a file with the extension .html and open it in Excel. That might work as Excel interpretes HTML tables. If you want a CSV-file you should write your data as you need it. Each column separated by ; (semicolon) and a line break #chr(13)##chr(10)# after each row.

<cfsavecontent variable="myCSV">                 
    <cfloop query="ledgerCheck">#JobKey#;#Inspector#;#permitUser#;#StartDate#;#EndDate##chr(13)##chr(10)#</cfloop>
</cfsavecontent>

answered Mar 05 '20 at 16:57

Bernhard Döbler

1,960
2
25
39

You're answer led me to my answer so thank you. The only issue was using .csv instead of .xls. I will keep this in mind for the future. – Cole Perry Mar 05 '20 at 17:05
1

Columns separated by semicolons? – Dan Bracuk Mar 05 '20 at 17:36
As @DanBracuk pointed out, CSV stands for "comma-separated values". While other delimiters can be used, commas are the default. However, if you have commas in your data, you can delimit each column with double quotes. `"` – user12031119 Mar 05 '20 at 18:07
Depends. Sometimes semicolon, sometimes comma, sometimes TAB. Recently customer wanted hash sign as seperator. – Bernhard Döbler Mar 05 '20 at 20:53
As a test, I used notepad to create a file consisting of two lines. The first was `a;b;c` and the second was `d;e;f`. I saved it with a .csv extension. When I opened it with excel, there were no delimiters. The semicolons were part of the data. If you are producing files semicolon, tab, or octothorp separators, you are probably giving them .txt extensions. I am downvoting this answer. – Dan Bracuk Mar 05 '20 at 21:02
Thank you very much! – Bernhard Döbler Mar 05 '20 at 21:50
I listed some separaters I used over the years. Everyone is free to use seperators according to their liking! I believe it's clear in my code what to replace! – Bernhard Döbler Mar 05 '20 at 21:52

score 0 · Answer 2 · answered Mar 05 '20 at 17:03

0

The only issues was that I was using .csv. I changed it from ok.csv to ok.xls and it works fine.

answered Mar 05 '20 at 17:03

Cole Perry

333
1
5
27

4

It's still a bad approach. If you want a .xls file ColdFusion has spreadsheet functions that are much more reliable than outputting html. – Dan Bracuk Mar 05 '20 at 17:38

Lucee cfquery to a CSV file not formatting correctly

2 Answers2