Write VERY large record-sets to CSV, JSON with ColdFusion

So I was tasked to build something that writes a very large data set to CSV Files, and JSON files. I was running into memory issues with ColdFusion because I was trying to store the CSV string in memory before writing it to a file on the server. For a 1 Million+ row recordset in SQL Server this usually would time out after a 2 hour request timeout, and just not finish. By using the underlying Java objects we can write 1 Million+ records to a CSV file, or JSON file in just about 8-10 seconds. Credit for finding these Java objects goes to my co-worker Sean Louisin.

The fix for this, which worked for me, is using the native Java functions that ColdFusion has access to. My process and full function below:

1) Instantiate the Java objects we need:

oFileWriter = CreateObject("java","java.io.FileWriter").init(local.FileName,JavaCast("boolean","true"));
oBufferedWriter = CreateObject("java","java.io.BufferedWriter").init(oFileWriter);

This creates our File and prepares the Buffer for writing to that file.

2) Create headers for the CSV file (Query Columns)

oBufferedWriter.write( local.qCSV.columnList & chr(13) & chr(10) ); // Write Headers First

3) Loop query and write directly to the file.

   local.rowArray = arrayNew(1);
   for (row in local.qCSV) {
      local.rowArray = arrayNew(1);
      local.columnCntr = 1;

      aCol = listToArray (local.qCSV.ColumnList);
      for( fieldName in aCol ) {
         local.rowArray[local.columnCntr] = PrepareCSVValue(local.qCSV[fieldName][local.qCSV.currentRow]);
         local.columnCntr += 1;
      }

   oBufferedWriter.write(arrayToList(local.rowArray) & chr(13) & chr(10)); // Write fields, followed by new line carriage return.

   }

Here we are using a generic function to “prep” the CSV value by escaping double quotes, etc. And then after we process the field’s for the current row, we write them directly to the file using “arrayToList”.. so that they are comma seperated for the CSV file. By doing it this way, we do not have to store the string in memory and risk out of memory issues. We write the query fields directly to the file and move on to the next. We can see how this might be an issue when working with rather large datasets with millions of records. Having all of those in memory is just a bad idea.

Here’s the full function for Query to CSV File writing:

Full Function:

public any function exportToCSVFile(required query q, required string fileName) {
   var local = {};
   local.FileName = arguments.fileName;
   local.qCSV = arguments.q; // The Query Object. To Which we will Export
   local.queryResult = structNew(); // Result set of the Query Object

   oFileWriter = CreateObject("java","java.io.FileWriter").init(local.FileName,JavaCast("boolean","true"));
   oBufferedWriter = CreateObject("java","java.io.BufferedWriter").init(oFileWriter);

   oBufferedWriter.write( local.qCSV.columnList & chr(13) & chr(10) ); // Write Headers First

   local.rowArray = arrayNew(1);
   for (row in local.qCSV) {
      local.rowArray = arrayNew(1);
      local.columnCntr = 1;

      aCol = listToArray (local.qCSV.ColumnList);
      for( fieldName in aCol ) {
         local.rowArray[local.columnCntr] = PrepareCSVValue(local.qCSV[fieldName][local.qCSV.currentRow]);
         local.columnCntr += 1;
      }

   oBufferedWriter.write(arrayToList(local.rowArray) & chr(13) & chr(10)); // Write fields, followed by new line carriage return.

  }

   oBufferedWriter.close(); // Close file buffer writer.

   return;
}

Writing a Query to JSON file is much easier and I won’t explain the functions again but here is a Query to JSON File function:

public any function exportToJSONFile(required query q, required string FileName) {
   var local = {};
   local.FileName = arguments.fileName;

   oFileWriter = CreateObject("java","java.io.FileWriter").init(local.FileName,JavaCast("boolean","true"));
   oBufferedWriter = CreateObject("java","java.io.BufferedWriter").init(oFileWriter);
   oBufferedWriter.write( SerializeJSON(arguments.q) ); // Write file
   oBufferedWriter.close(); // Close file buffer writer.
 
   return;
}

Brandon

[mc4wp_form id=”277″]