4

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!

Paul B
  • 93
  • 5
  • I think `` can read a CSV directory into a query. This might end up being a two step operation. See https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-r-s/cfspreadsheet.html – James A Mohler May 20 '19 at 17:35
  • 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 May 20 '19 at 17:38
  • 2
    Running this tag: 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 May 20 '19 at 17:46
  • Looks like you are trying to build the query up column by column. Consider row by row. – James A Mohler May 20 '19 at 18:04
  • Do you have an example of what you mean with "row by row" in the context of my example? I'm not really sure what you mean and that code was just another example from somewhere else online. – Paul B May 20 '19 at 18:12
  • 2
    Honestly, Apache POI is bundled with CF, so why not use it directly? `createObject("java", "org.apache.commons.csv.CSVParser")` – Alex May 20 '19 at 18:42
  • So that looks really useful, but it seems to do pretty much what the CSVToArray function does? So I'm still not sure how to go from that (i.e. reading/parsing an CSV file) to outputting an XLS file. Do you have a short, simple example of that that you could provide? – Paul B May 20 '19 at 18:54
  • I've also tried CSVToQuery (https://cflib.org/udf/CSVToQuery) but can't seem to figure out how to get that to either modify it to look at an actual CSV *file* OR to get it to look at the output of my data from CSVToArray. – Paul B May 21 '19 at 12:39
  • Re: *You have attempted to dereference a scalar variable ...* That's because the code is using `result` as if it were a structure. It's not, it's an array. Assuming the file has headers, the column names will be the first element in the multi-dimensional array. With `result[ 1 ]` returning an array of the column names, and `result[1][1]` pointing to the first column, `result[1][2]` the second column, etc... – SOS May 21 '19 at 16:34

3 Answers3

3

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 );
SOS
  • 6,430
  • 2
  • 11
  • 29
  • You are amazing---THANK YOU! That actually works perfectly and does EXACTLY what I want it to do (take any .CSV file and re-write it as an .XLSX file). – Paul B May 21 '19 at 19:44
  • Here is the complete code for anyone else that would like it: // create spreadsheet xlsx = SpreadSheetNew("The Results", true); // populate with array data SpreadSheetAddRows( xlsx, result ); // save to file SpreadSheetWrite( xlsx, "c:\temp\TEST.xlsx", true ); – Paul B May 21 '19 at 19:45
  • Glad it helped! – SOS May 21 '19 at 20:26
  • 1
    I like that it goes straight from array if arrays to spreadsheet. I wonder if it can be done with spreadsheet member functions. – James A Mohler May 22 '19 at 01:31
1

I bet you could do something like this

<cfinclude template="Function_CSVtoArray.cfm"> 

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

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


<!--- setup the columns that you need --->
<cfset qPartsTwo = queryNew("id,name,amount","Integer,Varchar,Integer", result) />

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

CSVToArray() looks like it makes an array of structs.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
  • I actually did try that as well early on. It has the disadvantage that I would have to explicitly define every column (and there are multiple potential variations). But then in testing it I'm getting the error message "The column name First Name is invalid. Column names must be valid variable names. They must start with a letter and can only include letters, numbers, and underscores" because the file has multi-word column names (e.g. "First Name").... – Paul B May 21 '19 at 12:45
  • While convenient, `cfspreadsheet` is limited. Rather than jumping through hoops trying to make it work, it may be simpler to use spreadsheet functions. Loop through your array and populate each row. Then you could define whatever headers you want. If you absolutely must use `cfspreadsheet` with a query, you'll have to scrub the potential column names first, to remove any invalid characters – SOS May 21 '19 at 16:53
1

If you already have an array of structures from CSVToArray. Can you then use the ArrayOfStructuresToQuery function: https://cflib.org/udf/ArrayOfStructuresToQuery

Jason Holden
  • 168
  • 3
  • 10
  • Yeah I found that early on in my troubleshooting but I get the same "You have attempted to dereference a scalar variable of type class coldfusion.runtime.Array as a structure with members" error message. Here's the code I'm running: – Paul B May 21 '19 at 12:35
  • 1
    Can you do a dump INSIDE the ArrayOfStructuresToQuery function to see what the incoming array parameter contains? FYI I used this function in my own CSV import initially and ended up moving to Apache CSVManager for performance and compatibility reasons. – Jason Holden May 21 '19 at 13:28