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 it today: Get 60 Day Free Trial or sign up for Web API

ExportToSQLServer.VS2005.csproj
      
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <PropertyGroup> <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration> <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform> <ProductVersion>8.0.50727</ProductVersion> <SchemaVersion>2.0</SchemaVersion> <ProjectGuid>{8E22E915-5677-406D-8606-467F9C1CF2F4}</ProjectGuid> <OutputType>Exe</OutputType> <AppDesignerFolder>Properties</AppDesignerFolder> <RootNamespace>ExportToSQLServer</RootNamespace> <AssemblyName>ExportToSQLServer</AssemblyName> </PropertyGroup> <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' "> <DebugSymbols>true</DebugSymbols> <DebugType>full</DebugType> <Optimize>false</Optimize> <OutputPath>bin\Debug\</OutputPath> <DefineConstants>DEBUG;TRACE</DefineConstants> <ErrorReport>prompt</ErrorReport> <WarningLevel>4</WarningLevel> </PropertyGroup> <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' "> <DebugType>pdbonly</DebugType> <Optimize>true</Optimize> <OutputPath>bin\Release\</OutputPath> <DefineConstants>TRACE</DefineConstants> <ErrorReport>prompt</ErrorReport> <WarningLevel>4</WarningLevel> </PropertyGroup> <ItemGroup> <Reference Include="Bytescout.Spreadsheet, Version=2.2.0.307, Culture=neutral, PublicKeyToken=f7dd1bd9d40a50eb, processorArchitecture=MSIL"> <SpecificVersion>False</SpecificVersion> </Reference> <Reference Include="System" /> <Reference Include="System.Data" /> <Reference Include="System.Xml" /> </ItemGroup> <ItemGroup> </ItemGroup> <ItemGroup> <Compile Include="Program.cs" /> <Compile Include="Properties\AssemblyInfo.cs" /> </ItemGroup> <ItemGroup> <Content Include="SimpleReport.xls"> <CopyToOutputDirectory>Always</CopyToOutputDirectory> </Content> </ItemGroup> <Import Project="$(MSBuildBinPath)\Microsoft.CSharp.targets" /> <!-- To modify your build process, add your task inside one of the targets below and uncomment it. Other similar extension points exist, see Microsoft.Common.targets. <Target Name="BeforeBuild"> </Target> <Target Name="AfterBuild"> </Target> --> </Project>

Try it today: Get 60 Day Free Trial or sign up for Web API

ExportToSQLServer.csproj
      
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <PropertyGroup> <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration> <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform> <ProductVersion>8.0.50727</ProductVersion> <SchemaVersion>2.0</SchemaVersion> <ProjectGuid>{8E22E915-5677-406D-8606-467F9C1CF2F4}</ProjectGuid> <OutputType>Exe</OutputType> <AppDesignerFolder>Properties</AppDesignerFolder> <RootNamespace>ExportToSQLServer</RootNamespace> <AssemblyName>ExportToSQLServer</AssemblyName> </PropertyGroup> <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' "> <DebugSymbols>true</DebugSymbols> <DebugType>full</DebugType> <Optimize>false</Optimize> <OutputPath>bin\Debug\</OutputPath> <DefineConstants>DEBUG;TRACE</DefineConstants> <ErrorReport>prompt</ErrorReport> <WarningLevel>4</WarningLevel> </PropertyGroup> <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' "> <DebugType>pdbonly</DebugType> <Optimize>true</Optimize> <OutputPath>bin\Release\</OutputPath> <DefineConstants>TRACE</DefineConstants> <ErrorReport>prompt</ErrorReport> <WarningLevel>4</WarningLevel> </PropertyGroup> <ItemGroup> <Reference Include="Bytescout.Spreadsheet, Version=2.2.0.307, Culture=neutral, PublicKeyToken=f7dd1bd9d40a50eb, processorArchitecture=MSIL"> <SpecificVersion>False</SpecificVersion> </Reference> <Reference Include="System" /> <Reference Include="System.Data" /> <Reference Include="System.Xml" /> </ItemGroup> <ItemGroup> </ItemGroup> <ItemGroup> <Compile Include="Program.cs" /> <Compile Include="Properties\AssemblyInfo.cs" /> </ItemGroup> <ItemGroup> <Content Include="SimpleReport.xls"> <CopyToOutputDirectory>Always</CopyToOutputDirectory> </Content> </ItemGroup> <Import Project="$(MSBuildBinPath)\Microsoft.CSharp.targets" /> <!-- To modify your build process, add your task inside one of the targets below and uncomment it. Other similar extension points exist, see Microsoft.Common.targets. <Target Name="BeforeBuild"> </Target> <Target Name="AfterBuild"> </Target> --> </Project>

Try it today: Get 60 Day Free Trial or sign up for Web API

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 it today: Get 60 Day Free Trial or sign up for Web API

MORE INFORMATION

Get 60 Day Free Trial or Visit ByteScout Spreadsheet SDK page

Explore ByteScout Spreadsheet SDK documentation

WEB API VERSION

Sign Up for free Web API key

Explore Web API Documentation

Tutorials:

prev
next