Coldfusion CSV to Spreadsheet

Question

I have a few processes that utilize the CFSpreadsheet tag to import and then manipulate Excel data. This works great for .XLS & .XLSX files, however, it doesn't work if the data is sent as a .CSV file since CFSpreadsheet apparently was never updated to import .CSV files. At the end of the day I just want a simple pre-processor that takes a .CSV file and re-writes it as an .XLSX file so that my other process can take it from there.

My environment is the developer edition of Coldfusion 2018 and I've tried importing the data manually (which can work if I know all of the column definitions---but I won't always know that). My latest attempt has been with Ben Nadel's CSVToArray function ( https://www.bennadel.com/blog/2041-update-parsing-csv-data-files-in-coldfusion-with-csvtoarray.htm ) which works---I can easily get the .CSV file into an array---but I can't figure out how to go from that array to something like a query that I can write a spreadsheet with using CFSpreadsheet.

Here's an EXAMPLE:

<!--- This include is the function from Ben Nadel referenced above --->
<cfinclude template="Function_CSVtoArray.cfm"> 

<cfset result = csvToArray(file="TEST_File.csv") />

<cfdump var="#result#" label="TESTING">

<!--- *** The above WORKS up to this point ***--->

<!--- Create a new query. --->
<cfset qPartsTwo = QueryNew( "" ) />

<!--- Loop over keys in the struct. --->
<cfloop index="strKey" list="#StructKeyList(result)#" delimiters=",">

<!--- Add column to new query with default values. --->
<cfset QueryAddColumn(qPartsTwo,strKey,"VARCHAR",objParts[strKey]) />

</cfloop>

<!--- This code FAILS with a "You have attempted to dereference a scalar variable of type class coldfusion.runtime.Array as a structure with members" error message --->

I'd like to end up at something like this (although right now "result" is an array of some kind and not a query):

<cfspreadsheet action="write" filename="<my path>\TEST.xlsx" query="result">

Any ideas would be appreciated!

I think <cfspreadsheet> can read a CSV directory into a query. This might end up being a two step operation. See helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/… — James A Mohler
Thank you for responding. Well that was my first try way back when but the CFSpreadsheet tag fails when you try to read a CSV file. It apparently can write a CSV file but not read one (which I know makes zero sense). — Paul B
Running this tag: <cfspreadsheet action="read" src="<my path>\TEST.csv" format="csv" name="ExcelQ"> throws the error "An error occurred while reading the Excel: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Your InputStream was neither an OLE2 stream, nor an OOXML stream." — Paul B
Looks like you are trying to build the query up column by column. Consider row by row. — James A Mohler
Honestly, Apache POI is bundled with CF, so why not use it directly? createObject("java", "org.apache.commons.csv.CSVParser") — Alex

SOS SOS · Accepted Answer · 2019-05-21T18:11:29

It looks like your UDF returns a multi-dimensional array, not an array of structures. Instead of trying to coerce the array into a query object, try using spreadsheet functions to write the array data to an xlsx file.

DEMO / Sample data

result = [ ["First Name", "Last Name", "Address"]   
           , ["John", "Doe", "123 Anywhere Ave"]    
           , ["Mary", "Smith", "456 Somewhere Street"]  
           , ["Charles", "Doe", "789 Anywhere Court"]   
];

Code:

// create spreadsheet
xlsx = SpreadSheetNew("The Results", true);
// populate with array data
SpreadSheetAddRows( xlsx, result ); 
// save to file
SpreadSheetWrite( xlsx, "c:/path/to/test.xlsx", true );

.. or as James A Mohler suggested, you could also use member functions:

xlsx = SpreadSheetNew("The Results", true);
xlsx.addRows( result );
xlsx.write( "c:/path/to/test.xlsx", true );

Coldfusion CSV to Spreadsheet

3 Answers