I have an exel file with 1100 items in it. I'm trying to read those items from the excel file and eventually put them into a database.
The code below is my attempt to read the file. The file opens correctly and the first few "skus" read correctly. After that though, the values are "Null" - even though there is information in the cell.
For example (my excel file)
Row1 Cell 0: 202
Row2 Cell 0: 203
Row3 Cell 0: 100T-14
Row4 Cell 0: 100T-21
When I run the code below the message box will display:
0 Sku: 202
1 Sku: 203
but the third one will generate a "null" value error (Conversion from type 'DBNull' to type 'String' is not valid.) - even though it should be "2 Sku: 100T-14"
I've tried formating the column in excel (to text) - i've tried just typing text - but it seems that if it's not a number, it can't be read and it returns null.
thanks for your help!!!
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DS As DataSet
Dim DT As DataTable
Dim sku As String
MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & ExcelFilePath & "; " & _
"Extended Properties=Excel 8.0")
' Select the data from sheet: Henderson11 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Henderson11$]", MyConnection)
DS = New DataSet
Dt = DS.Tables(0)
Dim count As Integer
count = DS.Tables(0).Rows.Count
For x = 0 To count
sku = DS.Tables(0).Rows(x).Item(0)
MsgBox(x & " Sku: " & sku & ".")