So, it was asked on twitter if cfspreadsheet could read a csv file. I quickly looked at the docs and it appeared to me that it could. The docs even had this example..

view plain print about
1<cfspreadsheet action="read" src="#theFile#" sheet=1 row="3,4" format="csv" name="csvData">

However, if you try and do that you get this error:

view plain print about
1An error occurred while reading the Excel: java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream.

So, what I figured out was that cfspreadsheet type="csv" just converts the read xls document into csv format. Not sure how usefull that is.

So, I started digging into a possible solution. Then I stumbled into something. I ran into a post that talked about using cfhttp to load the csv. So, that got me thinking and I ended up with this.

view plain print about
1<cfhttp method="get" url="http://path.to.csv.file/test.csv" name="csvData">
2
3<cfoutput>#isQuery(csvData)#</cfoutput>
4
5<cfloop query="csvdata" >
6    <p>
7<cfloop list="#csvdata.columnlist#" index="i">
8    <cfoutput>
9            #csvdata['#i#'][currentRow]# -
10    </cfoutput>    
11</cfloop>
12</p>
13</cfloop>

I was shocked that the isQuery returned true. So, since it uses the first row as the column names I write some code to loop over the data and out put it. I honestly didn't think that this would work. I just get totally excited when something that I thought would be hard became super easy thanks to ColdFusion.

I have only ran this against ColdFusion 9 so I am not sure about backwards compatibility.

UPDATE: If the column name contains a space an error will be thrown. The error happens in the cfhttp tag call so you can't correct them post call. Make sure that if you are doing this that your first row contains text that are column name formatted. I.e... use "my_date" instead of "my date".

Till next time...

--Dave