Strange Excel importing problem

[Rob T]

I have a routine that imports a list of part numbers into a dataview:

strExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
boxFile.Text & ";Extended Properties=""Excel 8.0;HDR=NO"""
cnnExcel = New OleDbConnection(strExcel)
da = New OleDbDataAdapter("Select * from [ODRJYUFI$]", cnnExcel)
da.Fill(ds, "Excel")
dv = New DataView(ds.Tables("Excel"))

This works fine exept when the following condition happens: The part
numbers are alpha-numeric. When a numeric only part number (mixed in with
alpha's) is read in, NULL is stored. The routine will read in numeric
values ONLY when all the part numbers in the spreadsheet are numeric.

I've tried formatting the column in the spreadsheet as Text to try and trick
the routine, with no luck.

Any suggestions? Thanks

[Darrell Wesley]

Take a look at this site:
http://www.connectionstrings.com/

There is another papramer to add in to the connection string to make certain
Excel treats intermeixed columns as text.

"Rob T" wrote:

I have a routine that imports a list of part numbers into a dataview:

strExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
boxFile.Text & ";Extended Properties=""Excel 8.0;HDR=NO"""
cnnExcel = New OleDbConnection(strExcel)
da = New OleDbDataAdapter("Select * from [ODRJYUFI$]", cnnExcel)
da.Fill(ds, "Excel")
dv = New DataView(ds.Tables("Excel"))

This works fine exept when the following condition happens: The part
numbers are alpha-numeric. When a numeric only part number (mixed in with
alpha's) is read in, NULL is stored. The routine will read in numeric
values ONLY when all the part numbers in the spreadsheet are numeric.

I've tried formatting the column in the spreadsheet as Text to try and trick
the routine, with no luck.

Any suggestions? Thanks

[Rob T]

Works great! Thanks!

"Darrell Wesley" <Da***********@discussions.microsoft.com> wrote in message
news:42**********************************@microsof t.com...

Take a look at this site:
http://www.connectionstrings.com/

There is another papramer to add in to the connection string to make
certain
Excel treats intermeixed columns as text.

"Rob T" wrote:

I have a routine that imports a list of part numbers into a dataview:

strExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
boxFile.Text & ";Extended Properties=""Excel 8.0;HDR=NO"""
cnnExcel = New OleDbConnection(strExcel)
da = New OleDbDataAdapter("Select * from [ODRJYUFI$]", cnnExcel)
da.Fill(ds, "Excel")
dv = New DataView(ds.Tables("Excel"))

This works fine exept when the following condition happens: The part
numbers are alpha-numeric. When a numeric only part number (mixed in
with
alpha's) is read in, NULL is stored. The routine will read in numeric
values ONLY when all the part numbers in the spreadsheet are numeric.

I've tried formatting the column in the spreadsheet as Text to try and
trick
the routine, with no luck.

Any suggestions? Thanks

[Rob T]

Perhaps I spoke a bit too soon.... that part works, but now all my date
fields are returned as an integer. Any way to convert these back to a date
type?

"Darrell Wesley" <Da***********@discussions.microsoft.com> wrote in message
news:42**********************************@microsof t.com...

Take a look at this site:
http://www.connectionstrings.com/

There is another papramer to add in to the connection string to make
certain
Excel treats intermeixed columns as text.

"Rob T" wrote:

I have a routine that imports a list of part numbers into a dataview:

strExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
boxFile.Text & ";Extended Properties=""Excel 8.0;HDR=NO"""
cnnExcel = New OleDbConnection(strExcel)
da = New OleDbDataAdapter("Select * from [ODRJYUFI$]", cnnExcel)
da.Fill(ds, "Excel")
dv = New DataView(ds.Tables("Excel"))

This works fine exept when the following condition happens: The part
numbers are alpha-numeric. When a numeric only part number (mixed in
with
alpha's) is read in, NULL is stored. The routine will read in numeric
values ONLY when all the part numbers in the spreadsheet are numeric.

I've tried formatting the column in the spreadsheet as Text to try and
trick
the routine, with no luck.

Any suggestions? Thanks

[Darrell Wesley]

What your probably seing is the numeric value of the date field , if it is
then all you need to do is convert it back to a date in your VB program.

"Rob T" wrote:

Perhaps I spoke a bit too soon.... that part works, but now all my date
fields are returned as an integer. Any way to convert these back to a date
type?

"Darrell Wesley" <Da***********@discussions.microsoft.com> wrote in message
news:42**********************************@microsof t.com...

Take a look at this site:
http://www.connectionstrings.com/

There is another papramer to add in to the connection string to make
certain
Excel treats intermeixed columns as text.

"Rob T" wrote:

I have a routine that imports a list of part numbers into a dataview:

strExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
boxFile.Text & ";Extended Properties=""Excel 8.0;HDR=NO"""
cnnExcel = New OleDbConnection(strExcel)
da = New OleDbDataAdapter("Select * from [ODRJYUFI$]", cnnExcel)
da.Fill(ds, "Excel")
dv = New DataView(ds.Tables("Excel"))

This works fine exept when the following condition happens: The part
numbers are alpha-numeric. When a numeric only part number (mixed in
with
alpha's) is read in, NULL is stored. The routine will read in numeric
values ONLY when all the part numbers in the spreadsheet are numeric.

I've tried formatting the column in the spreadsheet as Text to try and
trick
the routine, with no luck.

Any suggestions? Thanks

[T.Stanley]

Guys... Excuse me for jumping in, but I have exactly Rob T's problem, and
have done what Darrell suggested with success. The integer returned seems to
represent the number of days from a reference day, which I calculated to
12/30/1899. That is, an Excel table cell that contains '7/4/2005' returns
the integer 38537. If your reference date is: dt =
DateTime.Parse('12/30/1899'), then dt.AddDays(38537) is '7/4/2005' which is
correct. But... isn't it a bit odd that 12/31/1899 (the last day of the
century) is not used? Am I missing something? Thanks! Tom Stanley
"Darrell Wesley" wrote:

What your probably seing is the numeric value of the date field , if it is
then all you need to do is convert it back to a date in your VB program.

"Rob T" wrote:

Perhaps I spoke a bit too soon.... that part works, but now all my date
fields are returned as an integer. Any way to convert these back to a date
type?

"Darrell Wesley" <Da***********@discussions.microsoft.com> wrote in message
news:42**********************************@microsof t.com...

Take a look at this site:
http://www.connectionstrings.com/

There is another papramer to add in to the connection string to make
certain
Excel treats intermeixed columns as text.

"Rob T" wrote:

> I have a routine that imports a list of part numbers into a dataview:
>
> strExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> boxFile.Text & ";Extended Properties=""Excel 8.0;HDR=NO"""
> cnnExcel = New OleDbConnection(strExcel)
> da = New OleDbDataAdapter("Select * from [ODRJYUFI$]", cnnExcel)
> da.Fill(ds, "Excel")
> dv = New DataView(ds.Tables("Excel"))
>
> This works fine exept when the following condition happens: The part
> numbers are alpha-numeric. When a numeric only part number (mixed in
> with
> alpha's) is read in, NULL is stored. The routine will read in numeric
> values ONLY when all the part numbers in the spreadsheet are numeric.
>
> I've tried formatting the column in the spreadsheet as Text to try and
> trick
> the routine, with no luck.
>
> Any suggestions? Thanks
>
>
>