Problem when importing data from Excel spreadsheet

[judge6]

Hi all, I am reading data from an excel spreadsheet. The column data which i am reading from my spreadsheet reads as follows:

integer
integer
integer
var char
integer
integer
integer
var char
var char
null
integer
etc.....

As you can see it contains no specific data types, but just a selection of random ones.

My problem is that when i read this into my vb code, the cells containing var char values get returned as Null values. I have seen bits by searching google but nothing that really helps me solve this problem. My understanding is that the connection string needs to contain some additional extended properties in order to prevent the dbconnection from assuming the correct data type for the column.

My Code:

Dim conn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pathOfSpreadSheet & ";Extended Properties=Excel 8.0")

Dim cmd As New OleDbDataAdapter("SELECT [rowX] FROM [Sheet1$]", conn)

'Fill my dataset
cmd.Fill(myDataSet)

Any help would be greatly appreciated.

[sbanks_charles]

I have had several issues importing data from Excel. Let me start by having you look at this: http://support.microsoft.com/default.aspx/kb/257819?p=1.
The extended property you are most likely looking for is HDR.
I saw another article somewhere that talked about a bug when reading Excel Data which I have run accross. The issue appears that when using the OLE Provider it looks at the first (I think) 4 rows to obtain column type.
I use OLED to import 3rd party addresses. If the zip code in the first 4 rows is only 5 didgets, the data comes in as INT and I loose any 5 plus. If I start out with 5 plus, I am fine.
Charles

Hi all, I am reading data from an excel spreadsheet. The column data which i am reading from my spreadsheet reads as follows:

integer
integer
integer
var char
integer
integer
integer
var char
var char
null
integer
etc.....

As you can see it contains no specific data types, but just a selection of random ones.

My problem is that when i read this into my vb code, the cells containing var char values get returned as Null values. I have seen bits by searching google but nothing that really helps me solve this problem. My understanding is that the connection string needs to contain some additional extended properties in order to prevent the dbconnection from assuming the correct data type for the column.

My Code:

Dim conn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pathOfSpreadSheet & ";Extended Properties=Excel 8.0")

Dim cmd As New OleDbDataAdapter("SELECT [rowX] FROM [Sheet1$]", conn)

'Fill my dataset
cmd.Fill(myDataSet)

Any help would be greatly appreciated.

[judge6]

I have had several issues importing data from Excel. Let me start by having you look at this: http://support.microsoft.com/default.aspx/kb/257819?p=1.
The extended property you are most likely looking for is HDR.
I saw another article somewhere that talked about a bug when reading Excel Data which I have run accross. The issue appears that when using the OLE Provider it looks at the first (I think) 4 rows to obtain column type.
I use OLED to import 3rd party addresses. If the zip code in the first 4 rows is only 5 didgets, the data comes in as INT and I loose any 5 plus. If I start out with 5 plus, I am fine.
Charles

Thanks very much for your input, I will take a look at the link you sent me. As for reading the data into a dataset, I am now taking a different route to achieving what i wanted.

Thanks again, Jules.