Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Export data to SQL Server 2005 from CSV file Expand / Collapse
Posted Thursday, October 17, 2013 12:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 11:07 PM
Points: 4, Visits: 19
Following code fetches data from CSV file and inserts it into SQL server 2005 table.

However, i need to specify Table name, Field names whose data will be fetched, plus mapping info(Mapping between 'column names of CSV' and 'column names of table') statically in code.

How can i modify below code so that it works for any Table name.
In short, Table name and Mapping should be handled dynamically.

public class CsvBulkCopyDataIntoSqlServer

protected const string _truncateLiveTableCommandText = @"TRUNCATE TABLE Account";
protected const int _batchSize = 100000;

static void Main(string[] args)


public static void LoadCsvDataIntoSqlServer()
// This should be the full path
var fileName = @"D:\output.csv";
var createdCount = 0;

using (var textFieldParser = new TextFieldParser(fileName))
textFieldParser.TextFieldType = FieldType.Delimited;
textFieldParser.Delimiters = new[] { "," };
textFieldParser.HasFieldsEnclosedInQuotes = true;

// var connectionString = ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString;
string connectionString = "Data Source=" + ";Initial Catalog= SFdata" + ";Persist Security Info=True;User ID= sa" + ";Password= Newuser@123";

var dataTable = new DataTable("Account");

// Add the columns in the temp table


using (var sqlConnection = new SqlConnection(connectionString))

// Truncate the live table
using (var sqlCommand = new SqlCommand(_truncateLiveTableCommandText, sqlConnection))

// Create the bulk copy object
var sqlBulkCopy = new SqlBulkCopy(sqlConnection)
DestinationTableName = "Account"

// Setup the column mappings, anything ommitted is skipped
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("shippingstreet", "shippingstreet");
sqlBulkCopy.ColumnMappings.Add("shippingpostalcode", "shippingpostalcode");
sqlBulkCopy.ColumnMappings.Add("ShippingCountry", "ShippingCountry");
sqlBulkCopy.ColumnMappings.Add("shippingstate", "shippingstate");

// Loop through the CSV and load each set of 100,000 records into a DataTable
// Then send it to the LiveTable
while (!textFieldParser.EndOfData)


if (createdCount % _batchSize == 0)
InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);


InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);


protected static void InsertDataTable(SqlBulkCopy sqlBulkCopy, SqlConnection sqlConnection, DataTable dataTable)



Post #1505492
Posted Thursday, October 17, 2013 1:34 AM



Group: General Forum Members
Last Login: Yesterday @ 1:35 AM
Points: 15,510, Visits: 13,170
You can read out the metadata of the table from INFORMATION_SCHEMA.COLUMNS.
The columns in the flat file have to have the same names though.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505508
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse