|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Strange Excel importing problemI 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 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. Show quote "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 > > > Works great! Thanks!
Show quote "Darrell Wesley" <DarrellWes***@discussions.microsoft.com> wrote in message news:42F05849-46C8-4C00-AAD2-1561B7F55608@microsoft.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 >> >> >> 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? Show quote "Darrell Wesley" <DarrellWes***@discussions.microsoft.com> wrote in message news:42F05849-46C8-4C00-AAD2-1561B7F55608@microsoft.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 >> >> >> 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. Show quote "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" <DarrellWes***@discussions.microsoft.com> wrote in message > news:42F05849-46C8-4C00-AAD2-1561B7F55608@microsoft.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 > >> > >> > >> > > > 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 Show quote "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" <DarrellWes***@discussions.microsoft.com> wrote in message > > news:42F05849-46C8-4C00-AAD2-1561B7F55608@microsoft.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 > > >> > > >> > > >> > > > > > > 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. Show quote "T.Stanley" wrote: > 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" <DarrellWes***@discussions.microsoft.com> wrote in message > > > news:42F05849-46C8-4C00-AAD2-1561B7F55608@microsoft.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 > > > >> > > > >> > > > >> > > > > > > > > > Thanks, Darrell. I found that information and it all makes more sense now.
Show quote "Darrell Wesley" wrote: > 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" wrote: > > > 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" <DarrellWes***@discussions.microsoft.com> wrote in message > > > > news:42F05849-46C8-4C00-AAD2-1561B7F55608@microsoft.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 > > > > >> > > > > >> > > > > >> > > > > > > > > > > > > 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  |
|||||||||||||||||||||||
Other interesting topics