Hi,

I'm loading an excel file, and put the data into an Access table - which works fine! The issue is that the data in Excel may not be consistently entered (phone number format for example). But say that in certain rows data is entered as xxxxxxxxx, instead of xxx-xxx-xxxx, for the rows that are with the dashes, I'm not able to "read" the data...

Set xlConnStr = Server.CreateObject("ADODB.Connection")
xlConnStr.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & Server.MapPath ("\Upload\Test1.xls") & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes"""
Set cat = Server.CreateObject("ADOX.Catalog")
cat.ActiveConnection = xlConnStr
sheetName = cat.Tables(0).Name
Set xlRst = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM [" & sheetName & "]"
xlRst.Open sql, xlConnStr
xlRst.MoveFirst
Do While not xlRst.EOF

response.write xlRst.Fields.Item(2).Value <- Only outputs the tn's with no dashes...
...

Is there a way to force it to return the values, or at least turn off the fact that Excel deteremines what kind of values they are...

By default, the JET engine scans the first N rows (I think N is 8???) to determine what data type each column is. So if the first N rows LOOK like a number to it (e.g., a phone number with no dashes), then it assumes numeric data and any non-numeric is treated as illegal.

You *CAN* override this action by putting a special file into the same directory as the ".xls" file. If I recall correctly, it must be named "schema.ini". And then there are all sorts of things you can specify in that file. Here's a description of using it with text files:
http://msdn.microsoft.com/en-us/library/ms709353.aspx
I *believe* it's basically the same thing with Excel files, but don't quote me on that.

Another possibility: If you *can*, purposely go into the Excel file and insert a first data row that has junk text values in every field. That will force all the columns to be TEXT and then you can use VBScript functions to convert the appropriate ones to numbers. Another choice that worked once for me: I change from HDR=YES to HDR=NO. So then my column headers in the spreadsheet were seen as *data*! And since, of course, they are all text string, all the fields were seen as text! If you do that, you will then have to get your actual data values by position number instead of by field name, but it does work.

Thank you, I really like that last option by removing the header.. then I just have to move to the "2nd" row and start inputting from there!