?
Solved

DataType problem whilst importing Excel spreadsheet using OleDb

Posted on 2007-10-17
11
Medium Priority
?
2,230 Views
Last Modified: 2008-01-09
Hi,

I know that this is a well-known problem. Unfortunately, I have not found an answer despite several searches.

I have an excel spreadsheet with several columns. Each columns DataType is supposed to be String. Unfortunately, I have a problem when the first entry in a column is a number but the succeeding entries are strings. OleDb then assumes that the column is of type Double and not String. This means that it ingnores everything which is not a number.

I am using this connection string (C#):

connection_string = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1" + (char)34;

If I set the HDR to no I do not have the problem. However, I do not obtain any column names, which are crucial for my validation.

Any pointers to overcome this problem would be very much appreciated. Many thanks in advance.

BW,

Chris
0
Comment
Question by:csetzkorn
  • 6
  • 5
11 Comments
 
LVL 28

Accepted Solution

by:
strickdd earned 2000 total points
ID: 20092473
The quickest easiest solution I can come up with is to make a call to get the column headers using the original way and then set the HDR to no and get the data.
0
 

Author Comment

by:csetzkorn
ID: 20092506
I thought about this but it is really cumbersome isn't it. Why the heck does Oledb use this weird strategy to look at the first 8 rows and decide on that the datatype of the remaining rows? That's madness ...

C
0
 
LVL 28

Expert Comment

by:strickdd
ID: 20092558
Since excel does not pass the datatype of a column it needs to determine a type somehow and this is how it is done. You can always import the data into Access and read from there if you want specific column data types.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:csetzkorn
ID: 20092574
So why does it only look at the first 8 rows?

C

PS: I gathered that you can change this somehow ...
0
 
LVL 28

Expert Comment

by:strickdd
ID: 20092606
The first 8 rows is to give an average expected datatype that is representative of the entire column. This number can apparently be changed through a registry hack, but it would have to be done on all servers running this code. Here is a good page I found that should help: http://www.brianpeek.com/blogs/archive/2006/04/18/415.aspx
0
 

Author Comment

by:csetzkorn
ID: 20092896
Yeah I was aware of the reg hack.

I will def. accept/ackn. your input.

C
0
 
LVL 28

Expert Comment

by:strickdd
ID: 20092945
Is there a reason you can't use and access db for this? that would allow you to have the type specific columns...
0
 

Author Comment

by:csetzkorn
ID: 20092988
stupid me offered users to upload 'several rows' using a spreadsheet rather than the form. I canot expect users to use access.

I guess I will have to use the solution of reading in two datasets - one just for the headers and the other for the data.
 
C
0
 
LVL 28

Expert Comment

by:strickdd
ID: 20093044
sorry i couldn't be more help....
0
 

Author Comment

by:csetzkorn
ID: 20099549
strickdd,

I am going to accept you first comment.

I am going to accept you first comment.

I came across another problem (I may have to ask another question but I try anyway). The data type 'String' that OLedb uses allows only 255 characters. Unfortunately, I have some entries with more than 255 characters. I there any way to overcome this?

C
0
 

Author Comment

by:csetzkorn
ID: 20099856
Hi,

setting 'TypeGuessRows' to zero as described here:
 
http://support.microsoft.com/kb/281517

seems to solve both of my problems.

C
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month16 days, 7 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question