The opposite day, I used to be having a chat with Adam Cameron concerning a very previous (2008) submit that I wrote for changing a ColdFusion question right into a CSV payload. The code in that submit makes me cringe; and represents each a mode and a mindset that feels archaic. As such, I needed to go about modernizing that code. However, as I used to be rewriting it, I saved working into hurdles. What I noticed is that changing a ColdFusion question straight right into a CSV is just not one thing I do that usually. As an alternative, I exploit a two-phase course of that first builds an Array-based illustration of the “report information”; after which, I serialize this middleman worth as CSV (Comma Separated Values).
If all it’s good to do is take a ColdFusion question and serialize it as a system change payload to be consumed by one other system, you then most likely do not want a lot customization; all you want is a format that may be learn by one other pc. The issue is, in my line of labor, I am virtually by no means producing CSV payloads for computer systems, I am producing them for people.
And, as soon as a human is your target market, then Consumer Expertise (UX) turns into an element. Which implies, the way you format your report information issues. You possibly can’t simply dump information to the display: it’s important to contemplate how persons are going to learn it; and, the way you may have the ability to take away “noise” with a view to make it extra human-consumable.
The opposite huge problem that I run into with CSV report technology is that I usually have to pull information from a number of sources. Which implies, even when I had a perform that transformed a single question right into a single CSV payload, I could not use it constantly with out loads of pre-serialization SQL and code gymnastics.
Since every CSV report has its personal particular constraints, I’ve taken to breaking the method up into two phases:
-
Producing a custom-made, two-dimensional array of information.
-
Producing a CSV from the middleman array.
Having two phases means extra overhead for the report technology. However, I’ve but to run right into a report the place the database pace wasn’t the most important bottleneck. As such, I am not involved. And, I consider it makes the code extra versatile, specific, and simpler to keep up.
This is an instance of producing a Consumer report. You will see that my middleman information construction makes choices about header names, column order, formatting dates, and omitting Falsy values with a view to scale back noise in a Sure/No column. These aren’t “greatest practices” throughout the board – they’re merely the alternatives I’ve made for this report:
NOTE: I am utilizing the
CsvSerializer.cfcthat I created in my earlier submit on remediating CSV Injection assaults in ColdFusion.
<cfscript>
embrace "./sample-data2.cfm";
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
// STEP ONE: Taking the database question and mapping it onto an middleman two-
// dimensional array that signify the cells in a spreadsheet. This enables me to be
// very exacting in how I wish to title and format information. For instance, I can utterly
// rename headers, select totally different date-masks for various columns, and exclude
// values which can be "falsy".
rows = [
[
"ID",
"Name",
"Location",
"Created",
"Still Active"
]
];
loop question = pattern {
rows.append([
sample.id,
sample.name,
sample.location,
// These are report-specific formatting functions.
formatReportDate( sample.createdAt ),
formatReportYesNo( sample.isActive )
]);
}
// STEP TWO: As soon as I've my row-data specified, full with customized row headers and
// report-specific formatting for dates, Booleans, and so forth, I can now go about serializing
// the information down right into a CSV payload. And, since we have extracted the high-touch, per-
// report formatting, it implies that the CSV technology could be very generic.
csvData = new CsvSerializer()
.serializeArray( rows )
;
echo( "<pre>#encodeForHtml( csvData )#</pre>" );
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
/**
* I return the date/time format for the given worth, returning an empty string for
* null values.
*/
personal string perform formatReportDate( required date enter ) {
// If this can be a NULL'in a position date, skip it.
if ( enter == "" ) {
return( "" );
}
return( dateFormat( enter, "yyyy-mm-dd" ) );
}
/**
* I return the Sure/No format for the given worth, returning an empty string for false
* values.
*/
personal string perform formatReportYesNo( required boolean enter ) {
return( enter ? "Sure" : "" );
}
</cfscript>
As you’ll be able to see, I am merely looping over the ColdFusoin question and I am mapping it (so to talk) onto an array, the place every row is an array unto itself. I discover this code to be simple. And, extra importantly, I discover it simple to switch. Which means, throughout the CFLoop – for instance – I can simply add row-specific logic. Heck, I may even have rows of various lengths as a result of the CsvSerializer.cfc does not have to have a strict “Grid” of information – it simply offers with Cells.
If I run this ColdFusion code in Lucee CFML, we get the next output:
As you’ll be able to see, we have been in a position to generate a CSV (Comma Separated Worth) output in ColdFusion with custom-made headers, custom-made date masks, and customised Sure/No values.
I am not saying that every one CSVs should be generated like this. That is simply the strategy that I exploit particularly for producing CSVs for human consumption. That stated, I really feel prefer it creates a pleasant separation of issues; and does not overload the CSV serialization.
Need to use code from this submit?
Take a look at the license.
