Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional 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 October 16th, 2007, 12:27 AM
Authorized User
 
Join Date: Jul 2007
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem in reading excel data

Hi All,
   I have a problem in reading the excel data.
In the excel sheet I have column named "EmpCode"
and it has values like 234,177,478,202,9033,9023,
733,4890,RC392,RC922,RC738, etc.
When I read the excel data using ASP.NET, I was able to
read only the interger values.
The string values like RC392, RC922,....
are unable to read.
In the excel the "EmpCode" has "general" type.
I have also tried with "Text" type and "value" type
but could not achieve the result.
Can any one help me to sort this problem

  #2 (permalink)  
Old October 16th, 2007, 03:52 AM
Friend of Wrox
Points: 1,935, Level: 17
Points: 1,935, Level: 17 Points: 1,935, Level: 17 Points: 1,935, Level: 17
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: United Kingdom
Posts: 550
Thanks: 0
Thanked 1 Time in 1 Post
Default

Can you let us know how you are trying to access the data from the Excel sheet, may be then we will be able to help you out!!!

Regards
Mike
  #3 (permalink)  
Old October 17th, 2007, 04:21 AM
Authorized User
 
Join Date: Jul 2007
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
   I have tried with two approaches.
One is by using "Import and Export data" wizard of sql server.
Second is by using OleDbSchemaTable in my ASP.NET application.
Both the approach does not give the desired result.
What I suspect is that, based on the data from the first 6 to 7 rows
in the excel sheet the datatype for the column in excel is decided.
Consider for example I have got 10 rows in a sheet.
First 8 rows has numeric value and last 2 rows has alphanumeric value. When I export the sheet using "Import and Export data wizard"
or read the data using OleDbSchemaTable in my ASP.NET application,
I could get the exact values for the first 8 rows and for the remaining 2 rows I could not get the values.



  #4 (permalink)  
Old October 17th, 2007, 08:56 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Doesn't know exactly how the wizard works, but I know that access import wizard do exactly that, take the firsts 5 rows to get the data type of the column... so maybe is the same???
You can do 2 things: insert a dummy row at the top of the table, or just mixed the rows so the wizard will take every column as varchar...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
  #5 (permalink)  
Old October 18th, 2007, 05:02 AM
Authorized User
 
Join Date: Jul 2007
Location: , , .
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,
  Thanks for your ideas. I will try with your ideas and sort out the problem.

  #6 (permalink)  
Old October 22nd, 2007, 10:14 AM
Friend of Wrox
 
Join Date: Feb 2006
Location: , , USA.
Posts: 116
Thanks: 0
Thanked 0 Times in 0 Posts
Default

for reading in excel, also check out this link:

http://dailydoseofexcel.com/archives...ed-data-types/

There are a few hangups everyone experiences and it sounds like you're already on the right track. The jet driver defaults to reading in the first 8 rows of data, and determines a data type based on that. What you want to usually do is override that and force it to use the mixed data type for columns with varchar-like data.

If you don't need a program to do this, you should definitely just use SQL Server. SQL Server 2005 makes it pretty easy. 2000's DTS is not as easy but you'll figure it out....


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error reading data from excel to sql using ASP m_soni21 Classic ASP Professional 1 June 17th, 2008 12:40 AM
data reading from excel using VB.net fly2irfan VB Databases Basics 1 January 27th, 2008 02:35 AM
Reading and Writing data from Excel using VB? honey123 VB How-To 0 March 25th, 2006 08:48 AM
data reading from excel using VB belzicool VB Databases Basics 3 December 22nd, 2005 06:28 AM
reading data from excel sheet rajiv_software Classic ASP Basics 2 April 30th, 2005 01:03 AM





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