DataType problem whilst importing Excel spreadsheet using OleDb

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
csetzkornAsked:
Who is Participating?
 
strickddConnect With a Mentor Commented:
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
 
csetzkornAuthor Commented:
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
 
strickddCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
csetzkornAuthor Commented:
So why does it only look at the first 8 rows?

C

PS: I gathered that you can change this somehow ...
0
 
strickddCommented:
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
 
csetzkornAuthor Commented:
Yeah I was aware of the reg hack.

I will def. accept/ackn. your input.

C
0
 
strickddCommented:
Is there a reason you can't use and access db for this? that would allow you to have the type specific columns...
0
 
csetzkornAuthor Commented:
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
 
strickddCommented:
sorry i couldn't be more help....
0
 
csetzkornAuthor Commented:
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
 
csetzkornAuthor Commented:
Hi,

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

seems to solve both of my problems.

C
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.