R – Auto Correcting Excel Spreadsheets for Idiots


I have a client who is incapable of formatting spreadsheets properly. I've asked them 10 times over to follow a sensible guideline (As in FORMAT THE COLUMNS WITH THE CORRECT DATATYPE) but in their lack of intelligence or professionalism or both, they just can't /won't do it.

I have an ODBC connection set up for the spreadsheets and pulls the data except for the columns that contain product counts or product prices. Unless I manually set the type for each of those columns to "Number" in Excel, the values appear null in the ASP page that displays the results.

Is it possible to write a SQL statement that will alter the datatype as it comes in? I don't care it if converts every column to a string as long as the ADODB.Recordset will display the values of all of the columns.


Dim MM_SQLSource

MM_SQLSource = "SELECT * FROM [Inventory$]"
Set rsGetExcelInfo = Server.CreateObject("ADODB.Recordset")
rsGetExcelInfo.ActiveConnection = MM_Excel_Connect
rsGetExcelInfo.Source = MM_SQLSource
rsGetExcelInfo.CursorType = 0
rsGetExcelInfo.CursorLocation = 2
rsGetExcelInfo.LockType = 1


Best Solution

It may not be entirely your client's fault. Excel sometimes has its own ideas about how a column should be formatted. For example, if you have a column containing zip codes, some with the Plus 4, others without, it is pretty much a crap shoot as to how that column will be formatted.

As for your original question, according to this site, CONVERT is a valid SQL scalar function, so maybe something like

SELECT CONVERT(BadField, SQL_CHAR) AS FixedField FROM [Inventory$]

might work?

My first inclination was to suggest using COM to read the data from the spreadsheet. I'm pretty sure you would be able to read each cell's format and deal with it accordingly, but I always found Excel via COM to be difficult and not terribly fast (I've only done it from C++).