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

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

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

  Click here to get your Free Trial version of the SDK