How to import data from XLS to SQL in VB.NET or C#

xls to sql importing is frequently requested so this sample source codes show how to import data from Excel files (XLS) into Microsoft SQL Server databases in Visual Basic .NET or C# using Spreadsheet SDK. 

C#:

using System;
using System.IO;
using Bytescout.Spreadsheet;
using System.Data.SqlClient;

<span data-scayt_word="namespace" data-scaytid="11">namespace</span> <span data-scayt_word="ExportToSQLServer" data-scaytid="12">ExportToSQLServer</span>
{
class Program
{
 static void Main(string[] <span data-scayt_word="args" data-scaytid="13">args</span>)
 {
 	try
 	{
   // MODIFY THE CONNECTION STRING WITH YOUR CREDENTIALS!!!
   string <span data-scayt_word="connectionString" data-scaytid="14">connectionString</span> = "Data Source=<span data-scayt_word="localhost" data-scaytid="16">localhost</span>;Initial Catalog=master;Integrated Security=true;";

   using (<span data-scayt_word="SqlConnection" data-scaytid="17">SqlConnection</span> connection = new <span data-scayt_word="SqlConnection" data-scaytid="18">SqlConnection</span>(<span data-scayt_word="connectionString" data-scaytid="15">connectionString</span>))
   {
   	connection.Open();

   	// Drop test database if exists
   	<span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="20">ExecuteQueryWithoutResult</span>(connection,
            "IF DB_ID ('<span data-scayt_word="XlsTests" data-scaytid="26">XlsTests</span>') IS NOT NULL DROP DATABASE <span data-scayt_word="XlsTests" data-scaytid="27">XlsTests</span>");
   	// Create empty database
   	<span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="21">ExecuteQueryWithoutResult</span>(connection, "CREATE DATABASE <span data-scayt_word="XlsTests" data-scaytid="28">XlsTests</span>");
   	// Switch to created database
   	<span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="22">ExecuteQueryWithoutResult</span>(connection, "USE <span data-scayt_word="XlsTests" data-scaytid="29">XlsTests</span>");
   	// Create a table for <span data-scayt_word="XLS" data-scaytid="2">XLS</span> data
   	<span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="23">ExecuteQueryWithoutResult</span>(connection,
            "CREATE TABLE <span data-scayt_word="XlsTest" data-scaytid="33">XlsTest</span> (Name <span data-scayt_word="VARCHAR" data-scaytid="36">VARCHAR</span>(40), <span data-scayt_word="FullName" data-scaytid="38">FullName</span> <span data-scayt_word="VARCHAR" data-scaytid="37">VARCHAR</span>(255))");

   	// Load <span data-scayt_word="XLS" data-scaytid="3">XLS</span> document
   	using (Spreadsheet document = new Spreadsheet())
   	{
     document.LoadFromFile("SimpleReport.xls");
     Worksheet worksheet = document.Workbook.Worksheets[0];

     for (<span data-scayt_word="int" data-scaytid="39">int</span> row = 0; row <= worksheet.UsedRangeRowMax; row++)
     {
     	String <span data-scayt_word="insertCommand" data-scaytid="40">insertCommand</span> = string.Format("INSERT <span data-scayt_word="XlsTest" data-scaytid="34">XlsTest</span> VALUES('{0}','{1}')",
       worksheet.Cell(row, 0).Value, worksheet.Cell(row, 1).Value);
     	<span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="24">ExecuteQueryWithoutResult</span>(connection, <span data-scayt_word="insertCommand" data-scaytid="41">insertCommand</span>);
     }
   	}
   	
   	// Check the data successfully exported
   	using (<span data-scayt_word="SqlCommand" data-scaytid="42">SqlCommand</span> command = new <span data-scayt_word="SqlCommand" data-scaytid="43">SqlCommand</span>("SELECT * from <span data-scayt_word="XlsTest" data-scaytid="35">XlsTest</span>", connection))
   	{
     <span data-scayt_word="SqlDataReader" data-scaytid="46">SqlDataReader</span> reader = command.ExecuteReader();

     if (reader != null)
     {
     	Console.WriteLine();
     	Console.WriteLine("Exported <span data-scayt_word="XLS" data-scaytid="4">XLS</span> data:");
     	Console.WriteLine();

     	while (reader.Read())
     	{
       Console.WriteLine(String.Format("{0}  |  {1}", reader[0], reader[1]));
     	}
     }
   	}

   	Console.WriteLine();
   	Console.WriteLine("Press any key.");
   	Console.ReadKey();
   }
 	}
 	catch(Exception ex)
 	{
   Console.WriteLine("Error: " + ex.Message);
   Console.ReadKey();
 	}
 }

 static void <span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="25">ExecuteQueryWithoutResult</span>(<span data-scayt_word="SqlConnection" data-scaytid="19">SqlConnection</span> connection, string query)
 {
 	using (<span data-scayt_word="SqlCommand" data-scaytid="44">SqlCommand</span> command = new <span data-scayt_word="SqlCommand" data-scaytid="45">SqlCommand</span>(query, connection))
 	{
   command.ExecuteNonQuery();
 	}
 }
}
}

VB.NET:

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

Class Program
Friend Shared Sub Main(<span data-scayt_word="args" data-scaytid="47">args</span> As String())
 Try
 	' MODIFY THE CONNECTION STRING WITH YOUR CREDENTIALS!!!
 	Dim <span data-scayt_word="connectionString" data-scaytid="48">connectionString</span> As String = "Data Source=<span data-scayt_word="localhost" data-scaytid="50">localhost</span>;Initial Catalog=master;Integrated Security=true;"

 	Using connection As New <span data-scayt_word="SqlConnection" data-scaytid="51">SqlConnection</span>(<span data-scayt_word="connectionString" data-scaytid="49">connectionString</span>)
   connection.Open()

   ' Drop test database if exists
   <span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="53">ExecuteQueryWithoutResult</span>(connection, "IF DB_ID ('<span data-scayt_word="XlsTests" data-scaytid="59">XlsTests</span>') IS NOT NULL DROP DATABASE <span data-scayt_word="XlsTests" data-scaytid="60">XlsTests</span>")
   ' Create empty database
   <span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="54">ExecuteQueryWithoutResult</span>(connection, "CREATE DATABASE <span data-scayt_word="XlsTests" data-scaytid="61">XlsTests</span>")
   ' Switch to created database
   <span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="55">ExecuteQueryWithoutResult</span>(connection, "USE <span data-scayt_word="XlsTests" data-scaytid="62">XlsTests</span>")
   ' Create a table for <span data-scayt_word="XLS" data-scaytid="8">XLS</span> data
   <span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="56">ExecuteQueryWithoutResult</span>(connection, "CREATE TABLE <span data-scayt_word="XlsTest" data-scaytid="66">XlsTest</span> (Name <span data-scayt_word="VARCHAR" data-scaytid="69">VARCHAR</span>(40), <span data-scayt_word="FullName" data-scaytid="71">FullName</span> <span data-scayt_word="VARCHAR" data-scaytid="70">VARCHAR</span>(255))")

   ' Load <span data-scayt_word="XLS" data-scaytid="9">XLS</span> document
   Using document As New Spreadsheet()
   	document.LoadFromFile("SimpleReport.xls")
   	Dim worksheet As Worksheet = document.Workbook.Worksheets(0)

   	For row As Integer = 0 To worksheet.UsedRangeRowMax
     Dim <span data-scayt_word="insertCommand" data-scaytid="72">insertCommand</span> As [String] = String.Format("INSERT <span data-scayt_word="XlsTest" data-scaytid="67">XlsTest</span> VALUES('{0}','{1}')", worksheet.Cell(row, 0).Value, worksheet.Cell(row, 1).Value)
     <span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="57">ExecuteQueryWithoutResult</span>(connection, <span data-scayt_word="insertCommand" data-scaytid="73">insertCommand</span>)
   	Next
   End Using

   ' Check the data successfully exported
   Using command As New <span data-scayt_word="SqlCommand" data-scaytid="74">SqlCommand</span>("SELECT * from <span data-scayt_word="XlsTest" data-scaytid="68">XlsTest</span>", connection)
   	Dim reader As <span data-scayt_word="SqlDataReader" data-scaytid="76">SqlDataReader</span> = command.ExecuteReader()

   	If reader <span data-scayt_word="IsNot" data-scaytid="107">IsNot</span> Nothing Then
     Console.WriteLine()
     Console.WriteLine("Exported <span data-scayt_word="XLS" data-scaytid="10">XLS</span> 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
 Catch ex As Exception
 	Console.WriteLine("Error: " & ex.Message)
 	Console.ReadKey()
 End Try
End Sub

Private Shared Sub <span data-scayt_word="ExecuteQueryWithoutResult" data-scaytid="58">ExecuteQueryWithoutResult</span>(connection As <span data-scayt_word="SqlConnection" data-scaytid="52">SqlConnection</span>, query As String)
 Using command As New <span data-scayt_word="SqlCommand" data-scaytid="75">SqlCommand</span>(query, connection)
 	command.ExecuteNonQuery()
 End Using
End Sub
End Class