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

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)

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.

Try ByteScout Spreadsheet SDK today:  60 Day Free Trial (on-premise) or  Web API (on-demand version)

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

Try ByteScout Spreadsheet SDK today:  60 Day Free Trial (on-premise) or  Web API (on-demand version)

VIDEO

ON-PREMISE VERSION INFORMATION

Get 60 Day Free Trial or Visit ByteScout Spreadsheet SDK Home Page

Explore ByteScout Spreadsheet SDK Documentation

Get ByteScout Spreadsheet SDK Free Training

WEB API

Get Your Free API Key

Explore Web API Documentation

Tutorials:

prev
next