title: Coldfusion CSV to Spreadsheet
body: <p>I have a few processes that utilize the CFSpreadsheet tag to import and then manipulate Excel data.  This works great for .XLS &amp; .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 <em>simple</em> 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.</p>
  
  <p>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 ( <a href="https://www.bennadel.com/blog/2041-update-parsing-csv-data-files-in-coldfusion-with-csvtoarray.htm" rel="nofollow noreferrer">https://www.bennadel.com/blog/2041-update-parsing-csv-data-files-in-coldfusion-with-csvtoarray.htm</a> ) 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.</p>
  
  <p>Here's an EXAMPLE:</p>
  
  <pre><code>&lt;!--- This include is the function from Ben Nadel referenced above ---&gt;
  &lt;cfinclude template="Function_CSVtoArray.cfm"&gt; 
  
  &lt;cfset result = csvToArray(file="TEST_File.csv") /&gt;
  
  &lt;cfdump var="#result#" label="TESTING"&gt;
  
  &lt;!--- *** The above WORKS up to this point ***---&gt;
  
  &lt;!--- Create a new query. ---&gt;
  &lt;cfset qPartsTwo = QueryNew( "" ) /&gt;
  
  &lt;!--- Loop over keys in the struct. ---&gt;
  &lt;cfloop index="strKey" list="#StructKeyList(result)#" delimiters=","&gt;
  
  &lt;!--- Add column to new query with default values. ---&gt;
  &lt;cfset QueryAddColumn(qPartsTwo,strKey,"VARCHAR",objParts[strKey]) /&gt;
  
  &lt;/cfloop&gt;
  
  &lt;!--- 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 ---&gt;
  </code></pre>
  
  <p>I'd like to end up at something like this (although right now "result" is an array of some kind and not a query):</p>
  
  <pre><code>&lt;cfspreadsheet action="write" filename="&lt;my path&gt;\TEST.xlsx" query="result"&gt;
  </code></pre>
  
  <p>Any ideas would be appreciated!</p>