By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,168 Members | 2,548 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,168 IT Pros & Developers. It's quick & easy.

Strange Excel importing problem

P: n/a
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
Nov 21 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 21 '05 #2

P: n/a
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

Nov 21 '05 #3

P: n/a
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

Nov 21 '05 #4

P: n/a
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


Nov 21 '05 #5

P: n/a
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
>
>
>


Nov 21 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.