CODEWITHSUNDEEP
sql-serverexcelvb.net
Muhammad Haroon
Muhammad Haroon

Reputation: 284

Import data from Excel to SQL Server using vb.net

I am importing data from Excel into a SQL Server database using vb.net. In my Excel file, in column bNumber, there are values of different types i.e some are numbers and some are text:

Telenorx
Telenorx
8
97150219924
97150219924
97150219924
97150219924
Easypayx
92

When I select the data from Excel through OleDbCommand, it retrieves numbers correctly but text values as blank.

In Excel, the column's data type is General.

This is my code to retrieve data from Excel.

excelConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data 
Source= " + OpenFileDialog1.FileName + ";Extended Properties=""Excel 12.0 
Xml;HDR=Yes""")
Dim oleDbCommand As OleDbCommand = New OleDbCommand("Select bNumber from 
[Sheet1$]", excelConn)
excelConn.open()
Dim dataReader = oleDbCommand.ExecuteReader 
dataReader.read()

Upvotes: 1

Views: 8150

Answers (1)

Karen Payne
Karen Payne

Reputation: 5102

This is not necessarily a solution for your case yet would I did was created a SQL-Server table with bNumber as nvarchar, used SQL-Server Management Studio to export to Excel where I placed the Excel file in the bin\Debug folder of the project. Using the code below all rows returned properly (as we have a string column strings are returned).

The key here is using IMEX=1 in the connection string, Excel can be finicky, this may or may not resolve the issue. 

Imports System.Data.OleDb
Public Class Operations
    Public Function GetData(ByVal FileName As String) As List(Of String)
        Dim valueList As New List(Of String)
        Using cn As New OleDbConnection With
            {
                .ConnectionString = ConnectionString(FileName)
            }
            Using cmd As OleDbCommand = New OleDbCommand("SELECT bNumber FROM [Table_1$]", cn)
                cn.Open()
                Dim reader As OleDbDataReader = cmd.ExecuteReader
                While reader.Read
                    valueList.Add(reader.GetString(0))
                End While
            End Using
        End Using
        Return valueList
    End Function
    Public Function ConnectionString(ByVal FileName As String) As String
        Dim Builder As New OleDbConnectionStringBuilder
        If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", "Excel 8.0;IMEX=1;HDR=Yes;")
        Else
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", "Excel 12.0;IMEX=1;HDR=Yes;")
        End If

        Builder.DataSource = FileName

        Return Builder.ConnectionString

    End Function
End Class

Form code

Private Sub Button3_Click(sender As Object, e As EventArgs) _
    Handles Button3.Click

    Dim ops As New Operations
    Dim fileName As String = IO.Path.Combine(
        AppDomain.CurrentDomain.BaseDirectory, "Downloaded.xlsx")
    Dim valueList As List(Of String) = ops.GetData(fileName)
    For Each value As String In valueList
        Console.WriteLine(value)
    Next
End Sub

Results in the IDE Output window

enter image description here

Table structure

enter image description here

Worksheet

enter image description here

EDIT: The following link points to a MSDN code sample for working with SpreadSheetLight library. Once downloaded, set startup form to StackOverFlow1QuestionMixedTypesForm. Right click on the solution in solution explorer and select "Restore NuGet Packages", now build and run the project. There are two buttons on the form, first does mixed types for Sheet1 while button two is a variant of the same data laid out differently in Sheet2.

Code sample https://code.msdn.microsoft.com/Alternate-methods-to-work-4c52c4a2

Upvotes: 1

Related Questions