Link Search Menu Expand Document

Convert TXT to SQL Server - C#

Spreadsheet SDK sample in C# demonstrating ‘Convert 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();
			}
		}
	}
}

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

Download Source Code (.zip)

Return to the previous page Explore Spreadsheet SDK


Copyright © 2016 - 2021 ByteScout