Strange Excel importing problem

  • Thread starter Thread starter Rob T
  • Start date Start date
R

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
 
Works great! Thanks!

Darrell Wesley said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.



:

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
Click to expand...
Click to expand...
Click to expand...
 
If you search the Excel help using "date system" you will find that Excel can
use 2 different date systems. The first and probably most commonly used is
1900 where January 1, 1900 has a value of 1. The second date system is 1904
where January 2, 1904 has a value of 1.

T.Stanley said:
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 said:
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 said:
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?

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.



:

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
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Darrell. I found that information and it all makes more sense now.

Darrell Wesley said:
If you search the Excel help using "date system" you will find that Excel can
use 2 different date systems. The first and probably most commonly used is
1900 where January 1, 1900 has a value of 1. The second date system is 1904
where January 2, 1904 has a value of 1.

T.Stanley said:
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 said:
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.

:

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?

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.



:

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
Click to expand...
Click to expand...
Click to expand...
 
hi. i've got the same problem reading integer from excel but i'm using C in
a C++ environment.

The first author mentioned he got no problem reading an all integer cell.

I've tried the IMEX=1 but got problem with "Could not find installable ISAM".
Since i'm running on the server and i don't have privilege to change the
windows registry of the server, i may be able to do away with IMEX=1 since
the column is not mixed integer/string.

Thanks.

Julius
 
Back
Top