Problem Reading Values From A Column

[Guest]

Hi, I used below code to retrieve data from an Excel file
'Code to open the file here
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\CoopMetrics\test.xls';Extended Properties=Excel 8.0;

'Now using the OledbDataAdapter you can query the excel sheet
Dim myDataset As New DataSe
Dim myData As New OleDbDataAdapter("SELECT * FROM [General Ledger Trial Balanc (2)$]", strConn
myData.TableMappings.Add("Table", "ExcelTest"
myData.Fill(myDataset

The fourth column in this excel spreadsheet has no values for the first 42 records but has value on the 43rd record. When I try to read the value for the fourth column in the 43rd record, it read in a null value though it SHOULD have a value. Why is it this way? How can I fix that

Thanks for info

 

[Mohamoss]

Hi
is this column " the forth one in you excel sheet a calculated one " , i
mean do you use an equation inside excel to get that column

 

[Guest]

Hi, Mohamoss. The column doesn't have a formula. However, the column mostly have no values except for a few rows. Also for the first 42 rows, this column has no value. Do you think this might cause the problem?

 

[Paul Clement]

¤ Hi, I used below code to retrieve data from an Excel file.
¤ 'Code to open the file here.
¤ Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\CoopMetrics\test.xls';Extended Properties=Excel 8.0;"
¤
¤ 'Now using the OledbDataAdapter you can query the excel sheet.
¤ Dim myDataset As New DataSet
¤ Dim myData As New OleDbDataAdapter("SELECT * FROM [General Ledger Trial Balanc (2)$]", strConn)
¤ myData.TableMappings.Add("Table", "ExcelTest")
¤ myData.Fill(myDataset)
¤
¤ The fourth column in this excel spreadsheet has no values for the first 42 records but has value on the 43rd record. When I try to read the value for the fourth column in the 43rd record, it read in a null value though it SHOULD have a value. Why is it this way? How can I fix that?
¤

Try specifying the IMEX argument in your connection string:

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\CoopMetrics\test.xls;Extended Properties=""Excel 8.0;IMEX=1;"""


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Paul Clement]

¤ Thanks a lot Paul Clement! That works!!
¤
¤ What is IMEX, btw?

Long time issue that applies to the Excel driver when using mixed mode characters in a column:

PRB: Excel Values Returned as NULL Using DAO OpenRecordset
http://support.microsoft.com/default.aspx?scid=kb;en-us;194124


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)