Problem Reading Values From A Column

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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?
 
¤ 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)
 
Back
Top