ByteScout Barcode Suite – C# – Convert xls to sql server (via csv bulk insert) with spreadsheet sdk

Home
/
Articles
/
ByteScout Barcode Suite – C# – Convert xls to sql server (via csv bulk insert) with spreadsheet sdk

printable version:
ByteScout-Barcode-Suite-C-sharp-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 C# and ByteScout Barcode Suite

How to write a robust code in C# to convert xls to sql server (via csv bulk insert) with spreadsheet sdk with this step-by-step tutorial

Sample source code below will display you how to manage a complex task like convert xls to sql server (via csv bulk insert) with spreadsheet sdk in C#. ByteScout Barcode Suite can convert xls to sql server (via csv bulk insert) with spreadsheet sdk. It can be applied from C#. ByteScout Barcode Suite is the bundle that privides 3 SDK products to generate barcodes (Barcode SDK), read barcodes (Barcode Reaer SDK) and read and write spreadsheets (Spreadsheet SDK).

These C# code samples for C# guide developers to speed up coding of the application when using ByteScout Barcode Suite. This C# sample code is all you need for your app. Just copy and paste the code, add references (if needs to) and you are all set! Enjoy writing a code with ready-to-use sample codes in C#.

You can download free trial version of ByteScout Barcode Suite from our website with this and other source code samples for C#.

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

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

Program.cs

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

namespace ExportToSQLServer
{
	class Program
	{
		static void Main(string[] args)
		{
			try
			{
				// Load XLS document
				using (Spreadsheet document = new Spreadsheet())
				{
					document.LoadFromFile("SimpleReport.xls");
					string csvFile = Path.GetTempPath() + "SimpleReport.csv";

					// Save the document as CSV file
					document.Workbook.Worksheets[0].SaveAsCSV(csvFile);
					document.Close();

					if (File.Exists(csvFile))
					{
						// MODIFY THE CONNECTION STRING WITH YOUR CREDENTIALS!!!
						string connectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=true;";

						using (SqlConnection connection = 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 = '\n')");
							
							// Check the data successfully exported
							using (SqlCommand command = new SqlCommand("SELECT * from CsvTest", connection))
							{
								SqlDataReader reader = command.ExecuteReader();

								if (reader != null)
								{
									Console.WriteLine();
									Console.WriteLine("Exported CSV 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 ExecuteQueryWithoutResult(SqlConnection connection, string query)
		{
			using (SqlCommand command = new SqlCommand(query, connection))
			{
				command.ExecuteNonQuery();
			}
		}
	}
}