Many thanks for your speedy reply Curtis. That worked a treat!
The extended property I required was IMEX=1 My connection string now looks like this: Dim _cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myFile.xls;Extended Properties=""Excel 8.0; IMEX=1""")
> Go down to the section "Considerations That Apply to Both OLE DB Providers > A Caution about Mixed Data Types". It discusses what to expect when using > mixed types.
> Hope that helps...
> -Curtis
> "Ben Reese" wrote:
> > Hi
> > I have an OleDB connection to Excel like this: > > Dim _cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data > > Source=myFile.xls;Extended Properties=Excel 8.0")
> > and a select command like this: > > Dim _com As New OleDb.OleDbCommand("SELECT * FROM Sheet1$")
> > All pretty straight forward, but... > > Some of the values returned for a particular row in the resultant dataset > > have null values. Now, If I open the spreadsheet I can see values in all rows > > for this field, but some of the rows are formatted as number and some are > > formatted as text with a preceeding apostrophe. Excel gives a message next to > > these "The number in this cell is formatted as text or proceeded by an > > apostrope". Clicking the floating message removes the appostrophe and formats > > the cell as a number. It is the cells formatted as numbers that are returning > > Null! The text formated numbers are returning the correct data.
> > My guess is that I need to specify something in the Extended Pproperties of > > the connection string. But what please?
> > Incedentally. I have tried importing the spread sheet into SQL Server using > > the impoprt data wizard and this suffers the same problem.
Ben, I am using the IMEX = 1 extended properties in my vb.net (VSTO 2003) code and it is not over riding the the mixed type. Here is the following code:
' Select the data from Sheet1 of the workbook. sItemSQL = " Select * " sItemSQL += " From [item charts$]"
MyCommand = New System.Data.OleDb.OleDbDataAdapter(sItemSQL, MyConnection)
DS = New System.Data.DataSet MyCommand.Fill(DS, "Item")
The result is giving only numeric information on my parts column and blank when the part number starts with an alpha numeric. Is there anything else I have to do to get this to recogonize my mixed type column?
"Ben Reese" wrote: > Many thanks for your speedy reply Curtis. That worked a treat!
> The extended property I required was IMEX=1 > My connection string now looks like this: > Dim _cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data > Source=myFile.xls;Extended Properties=""Excel 8.0; IMEX=1""")
> > Go down to the section "Considerations That Apply to Both OLE DB Providers > > A Caution about Mixed Data Types". It discusses what to expect when using > > mixed types.
> > Hope that helps...
> > -Curtis
> > "Ben Reese" wrote:
> > > Hi
> > > I have an OleDB connection to Excel like this: > > > Dim _cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data > > > Source=myFile.xls;Extended Properties=Excel 8.0")
> > > and a select command like this: > > > Dim _com As New OleDb.OleDbCommand("SELECT * FROM Sheet1$")
> > > All pretty straight forward, but... > > > Some of the values returned for a particular row in the resultant dataset > > > have null values. Now, If I open the spreadsheet I can see values in all rows > > > for this field, but some of the rows are formatted as number and some are > > > formatted as text with a preceeding apostrophe. Excel gives a message next to > > > these "The number in this cell is formatted as text or proceeded by an > > > apostrope". Clicking the floating message removes the appostrophe and formats > > > the cell as a number. It is the cells formatted as numbers that are returning > > > Null! The text formated numbers are returning the correct data.
> > > My guess is that I need to specify something in the Extended Pproperties of > > > the connection string. But what please?
> > > Incedentally. I have tried importing the spread sheet into SQL Server using > > > the impoprt data wizard and this suffers the same problem.
On Thu, 18 Oct 2007 07:40:02 -0700, Todd <T...@discussions.microsoft.com> wrote:
¤ Ben, ¤ I am using the IMEX = 1 extended properties in my vb.net (VSTO 2003) code ¤ and it is not over riding the the mixed type. Here is the following code: ¤ ¤ MyConnection = New System.Data.OleDb.OleDbConnection( _ ¤ "provider=Microsoft.Jet.OLEDB.4.0;" & _ ¤ "Data Source=" & sFile & ";" & _ ¤ "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";") ¤ ¤ ' Select the data from Sheet1 of the workbook. ¤ sItemSQL = " Select * " ¤ sItemSQL += " From [item charts$]" ¤ ¤ MyCommand = New System.Data.OleDb.OleDbDataAdapter(sItemSQL, ¤ MyConnection) ¤ ¤ DS = New System.Data.DataSet ¤ MyCommand.Fill(DS, "Item") ¤ ¤ The result is giving only numeric information on my parts column and blank ¤ when the part number starts with an alpha numeric. Is there anything else I ¤ have to do to get this to recogonize my mixed type column? ¤
Probably will require a change to the Excel Registry setting TypeGuessRows:
Paul, Thanks for responding. It seems like this is an issue with a lot of folks out there. I will not have access to edit everyone's registry. This would not be a viable solution for myself. Any other solution that can be done without editing any local machine settings? I hate to open the excel file and read it line by line. Any other ideas?
"Paul Clement" wrote: > On Thu, 18 Oct 2007 07:40:02 -0700, Todd <T...@discussions.microsoft.com> wrote:
> ¤ Ben, > ¤ I am using the IMEX = 1 extended properties in my vb.net (VSTO 2003) code > ¤ and it is not over riding the the mixed type. Here is the following code: > ¤ > ¤ MyConnection = New System.Data.OleDb.OleDbConnection( _ > ¤ "provider=Microsoft.Jet.OLEDB.4.0;" & _ > ¤ "Data Source=" & sFile & ";" & _ > ¤ "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";") > ¤ > ¤ ' Select the data from Sheet1 of the workbook. > ¤ sItemSQL = " Select * " > ¤ sItemSQL += " From [item charts$]" > ¤ > ¤ MyCommand = New System.Data.OleDb.OleDbDataAdapter(sItemSQL, > ¤ MyConnection) > ¤ > ¤ DS = New System.Data.DataSet > ¤ MyCommand.Fill(DS, "Item") > ¤ > ¤ The result is giving only numeric information on my parts column and blank > ¤ when the part number starts with an alpha numeric. Is there anything else I > ¤ have to do to get this to recogonize my mixed type column? > ¤
> Probably will require a change to the Excel Registry setting TypeGuessRows:
On Fri, 19 Oct 2007 07:06:03 -0700, Todd <T...@discussions.microsoft.com> wrote:
¤ Paul, ¤ Thanks for responding. It seems like this is an issue with a lot of folks ¤ out there. I will not have access to edit everyone's registry. This would ¤ not be a viable solution for myself. Any other solution that can be done ¤ without editing any local machine settings? I hate to open the excel file ¤ and read it line by line. Any other ideas? ¤
Unfortunately I'm not aware of another workaround for this.
First of all thanks for that useful information. I'd like to add a caution about numeric fields in Excel though. The oledb adapter tends to convert large numeric values to E representation (i.e. 4906945 to 4.906945E+006) and this creates a mass if you are transferring the numeric data to a text field (i.e. phone number which may be written as +90(312)4906945 or simply 4906945 depending on users choice). Therefore if you are willing to have the exact text entered to a cell in Excel you should set cell content type to Text before entering and transferring data.
"Ben Reese" wrote: > Many thanks for your speedy reply Curtis. That worked a treat!
> The extended property I required was IMEX=1 > My connection string now looks like this: > Dim _cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data > Source=myFile.xls;Extended Properties=""Excel 8.0; IMEX=1""")
> > Go down to the section "Considerations That Apply to Both OLE DB Providers > > A Caution about Mixed Data Types". It discusses what to expect when using > > mixed types.
> > Hope that helps...
> > -Curtis
> > "Ben Reese" wrote:
> > > Hi
> > > I have an OleDB connection to Excel like this: > > > Dim _cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data > > > Source=myFile.xls;Extended Properties=Excel 8.0")
> > > and a select command like this: > > > Dim _com As New OleDb.OleDbCommand("SELECT * FROM Sheet1$")
> > > All pretty straight forward, but... > > > Some of the values returned for a particular row in the resultant dataset > > > have null values. Now, If I open the spreadsheet I can see values in all rows > > > for this field, but some of the rows are formatted as number and some are > > > formatted as text with a preceeding apostrophe. Excel gives a message next to > > > these "The number in this cell is formatted as text or proceeded by an > > > apostrope". Clicking the floating message removes the appostrophe and formats > > > the cell as a number. It is the cells formatted as numbers that are returning > > > Null! The text formated numbers are returning the correct data.
> > > My guess is that I need to specify something in the Extended Pproperties of > > > the connection string. But what please?
> > > Incedentally. I have tried importing the spread sheet into SQL Server using > > > the impoprt data wizard and this suffers the same problem.
|
