Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 3rd, 2009, 04:23 PM
Friend of Wrox
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts
Default interpretting Excel data using ASP


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
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...
  #2 (permalink)  
Old March 3rd, 2009, 08:56 PM
Friend of Wrox
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

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:
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.
  #3 (permalink)  
Old March 5th, 2009, 10:39 AM
Friend of Wrox
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts

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!

Similar Threads
Thread Thread Starter Forum Replies Last Post
Missing zeros when export data to Excel using ASP phudong3da Classic ASP Databases 1 January 12th, 2007 06:39 PM
XSLT help : Interpretting HTML tags from XML kunal_kishan XSLT 1 June 26th, 2006 05:37 AM
Getting data from MS Excel to ASP. rupen Classic ASP Basics 4 August 12th, 2005 04:01 PM
Saving Excel data to SQL Server via ASP sankar Classic ASP Databases 6 February 11th, 2005 09:22 AM
.asp request from Excel (Import External Data) chs245 Classic ASP Basics 1 October 6th, 2003 04:24 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.