ByteScout Spreadsheet SDK – C# – Convert XLS to SQL Server (via CSV BULK INSERT)

Home
/
Articles
/
ByteScout Spreadsheet SDK – C# – Convert XLS to SQL Server (via CSV BULK INSERT)

printable version:
ByteScout-Spreadsheet-SDK-C-sharp-Convert-XLS-to-SQL-Server-(via-CSV-BULK-INSERT).pdf

How to convert XLS to SQL server (via CSV BULK insert) in C# with ByteScout Spreadsheet SDK

How to convert XLS to SQL server (via CSV BULK insert) in C#

This sample source code below will demonstrate you how to convert XLS to SQL server (via CSV BULK insert) in C#. ByteScout Spreadsheet SDK is the library (SDK) that is capable of writing, reading, modifying and calculating Excel and CSV spreadsheets. Most popular formulas can be calculated and reculculated with Excel installed. You may import or export data to and from CSV, XML, JSON as well as to and from databases, arrays. It can be used to convert XLS to SQL server (via CSV BULK insert) using C#.

This rich sample source code in C# for ByteScout Spreadsheet SDK includes the number of functions and options you should do calling the API to convert XLS to SQL server (via CSV BULK insert). In order to implement the functionality, you should copy and paste this code for C# below into your code editor with your app, compile and run your application. Use of ByteScout Spreadsheet SDK in C# is also explained in the documentation included along with the product.

Trial version of ByteScout Spreadsheet SDK is available for free. Source code samples are included to help you with your C# app.

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();
			}
		}
	}
}