Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Databases
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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

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...
Reply With Quote
  #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
Default

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.
Reply With Quote
  #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
Default

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!
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:21 PM.


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