we are currently using cfspreadsheet to process excel spreadsheets that are being imported into our app.
At present we dont have an easy way of validating the data types that are imported from the data as we are trying to work with a QoQ object after we have the spreadsheet in memory.
Is there any sort of easy way to loop over a query object to detect the data types for each column in the query dataset ?
<cfspreadsheet action="read" src="#form.uploadedFile#" query="mycontent" headerrow="1" excludeheaderrow="yes">
<cfquery name="mycontent" dbtype="query">
SELECT *
FROM mycontent
</cfquery>
Ive tried looking for meta data functions for queries, but cant seem to find any
No. There are no built in methods that return the data types (or more accurately "cell types") of the values read from a spreadsheet. You must use the underlying POI library to access that information.
In addition, as Dan alluded to above, there is not an exact correlation between "cell types" and query "data types". Unlike database tables, a spreadsheet may contain multiple types of cells, within the same column. Just because the first cell in a column contains date, is no guarantee that the all of the cells in that column do as well. That is one of the reasons why all of the resulting query columns are assigned type
varchar. Technically there are no "column" data types when it comes to spreadsheets.That said, here is an example of how to extract the types of individual cells using POI. It is primarily geared towards examining cell format, but the basic concepts are the same.
Can you elaborate on the ultimate goal? ie How do you intend to use this information and how does it relate to your QoQ?