• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

VB.NET Read from Excel File

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.

Any ideas?

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 & ".")

Open in new window

  • 2
1 Solution
slightlyoffAuthor Commented:
Found the answer.

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""

slightlyoffAuthor Commented:
I found the answer.

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now