ByteScout Spreadsheet SDK – C# – TXT to SQL Server

ByteScout Spreadsheet SDK – C# – TXT to SQL Server

Program.cs

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

namespace ExportToSQLServer
{
class Program
{
static void Main(string[] args)
{
try
{
// 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 (‘TxtTests’) IS NOT NULL DROP DATABASE TxtTests”);
// Create empty database
ExecuteQueryWithoutResult(connection, “CREATE DATABASE TxtTests”);
// Switch to created database
ExecuteQueryWithoutResult(connection, “USE TxtTests”);
// Create a table for TXT data
ExecuteQueryWithoutResult(connection,
“CREATE TABLE TxtTest (Name VARCHAR(40), FullName VARCHAR(255))”);

// Load TXT document
using (Spreadsheet document = new Spreadsheet())
{
document.LoadFromFile(“sample.txt”, “\t”); // \t – TAB delimiter
Worksheet worksheet = document.Workbook.Worksheets[0];

for (int row = 0; row <= worksheet.UsedRangeRowMax; row++) { String insertCommand = string.Format("INSERT TxtTest VALUES('{0}','{1}')", worksheet.Cell(row, 0).Value, worksheet.Cell(row, 1).Value); ExecuteQueryWithoutResult(connection, insertCommand); } } // Check the data successfully exported using (SqlCommand command = new SqlCommand("SELECT * from TxtTest", connection)) { SqlDataReader reader = command.ExecuteReader(); if (reader != null) { Console.WriteLine(); Console.WriteLine("Exported TXT 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(); } } } } [/csharp]

sample.txt

Homer	Homer Jay Simpson
Marjorie	Marjorie Marge Simpson (Bouvier)
Bartholomew	Bartholomew Jojo Bart Simpson
Lisa	Lisa Marie Simpson
Margaret	Margaret Maggie

  Click here to get your Free Trial version of the SDK