VB.NET Read from Excel File

Posted on 2011-10-11
Last Modified: 2012-05-12
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

Question by:slightlyoff
    LVL 1

    Accepted Solution

    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"""

    LVL 1

    Author Closing Comment

    I found the answer.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    This video discusses moving either the default database or any database to a new volume.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now