ByteScout Data Extraction Suite – VB.NET – Convert xls to sql server (via csv bulk insert) with spreadsheet sdk

Home
/
Articles
/
ByteScout Data Extraction Suite – VB.NET – Convert xls to sql server (via csv bulk insert) with spreadsheet sdk

printable version:
ByteScout-Data-Extraction-Suite-VB-NET-Convert-xls-to-sql-server-(via-csv-bulk-insert)-with-spreadsheet-sdk.pdf

How to convert xls to sql server (via csv bulk insert) with spreadsheet sdk in VB.NET and ByteScout Data Extraction Suite

Learn to code in VB.NET to convert xls to sql server (via csv bulk insert) with spreadsheet sdk with this step-by-step tutorial

We made thousands of pre-made source code pieces for easy implementation in your own programming projects. ByteScout Data Extraction Suite is the bundle that includes three SDK tools for data extraction from PDF, scans, images and from spreadsheets: PDF Extractor SDK, Data Extraction SDK, Barcode Reader SDK. It can be applied to convert xls to sql server (via csv bulk insert) with spreadsheet sdk using VB.NET.

This prolific sample source code in VB.NET for ByteScout Data Extraction Suite contains various functions and other necessary options you should do calling the API to convert xls to sql server (via csv bulk insert) with spreadsheet sdk. IF you want to implement the functionality, just copy and paste this code for VB.NET below into your code editor with your app, compile and run your application. Complete and detailed tutorials and documentation are available along with installed ByteScout Data Extraction Suite if you’d like to learn more about the topic and the details of the API.

You can download free trial version of ByteScout Data Extraction Suite from our website with this and other source code samples for VB.NET.

On-demand (REST Web API) version:
Web API (on-demand version)

On-premise offline SDK for Windows:
60 Day Free Trial (on-premise)

Program.vb

      Imports System.IO
Imports Bytescout.Spreadsheet
Imports System.Data.SqlClient

Class Program
    Friend Shared Sub Main(args As String())
        Try
            ' Load XLS document
            Using document As New Spreadsheet()
                document.LoadFromFile("SimpleReport.xls")
                Dim csvFile As String = Path.GetTempPath() & "SimpleReport.csv"

                ' Save the document as CSV file
                document.Workbook.Worksheets(0).SaveAsCSV(csvFile)
                document.Close()

                If File.Exists(csvFile) Then
                    ' MODIFY THE CONNECTION STRING WITH YOUR CREDENTIALS!!!
                    Dim connectionString As String = "Data Source=localhost;Initial Catalog=master;Integrated Security=true;"

                    Using connection As New SqlConnection(connectionString)
                        connection.Open()

                        ' Drop test database if exists
                        ExecuteQueryWithoutResult(connection, "IF DB_ID ('XlsTests') IS NOT NULL DROP DATABASE XlsTests")
                        ' Create empty database
                        ExecuteQueryWithoutResult(connection, "CREATE DATABASE XlsTests")
                        ' Switch to created database
                        ExecuteQueryWithoutResult(connection, "USE XlsTests")
                        ' Create a table for CSV data
                        ExecuteQueryWithoutResult(connection, "CREATE TABLE CsvTest (Name VARCHAR(40), FullName VARCHAR(255))")
                        ' Export CSV data from local file
                        ExecuteQueryWithoutResult(connection, "BULK INSERT CsvTest FROM '" & csvFile & "' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '" & vbLf & "')")

                        ' Check the data successfully exported
                        Using command As New SqlCommand("SELECT * from CsvTest", connection)
                            Dim reader As SqlDataReader = command.ExecuteReader()

                            If reader IsNot Nothing Then
                                Console.WriteLine()
                                Console.WriteLine("Exported CSV data:")
                                Console.WriteLine()

                                While reader.Read()
                                    Console.WriteLine([String].Format("{0}  |  {1}", reader(0), reader(1)))
                                End While
                            End If
                        End Using

                        Console.WriteLine()
                        Console.WriteLine("Press any key.")
                        Console.ReadKey()
                    End Using
                End If
            End Using
        Catch ex As Exception
            Console.WriteLine("Error: " & ex.Message)
            Console.ReadKey()
        End Try
    End Sub

    Private Shared Sub ExecuteQueryWithoutResult(connection As SqlConnection, query As String)
        Using command As New SqlCommand(query, connection)
            command.ExecuteNonQuery()
        End Using
    End Sub
End Class