Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
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 asp.net 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)
{
LoadCsvDataIntoSqlServer();

}

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= 172.25.10.4" + ";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
dataTable.Columns.Add("Name");
dataTable.Columns.Add("shippingstreet");
dataTable.Columns.Add("shippingpostalcode");
dataTable.Columns.Add("ShippingCountry");
dataTable.Columns.Add("shippingstate");

//dataTable.Columns.Add("LastName");

using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();

// Truncate the live table
using (var sqlCommand = new SqlCommand(_truncateLiveTableCommandText, sqlConnection))
{
sqlCommand.ExecuteNonQuery();
}

// 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)
{
dataTable.Rows.Add(textFieldParser.ReadFields());

createdCount++;

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

break;
}
}

InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);

sqlConnection.Close();
}
}
}

protected static void InsertDataTable(SqlBulkCopy sqlBulkCopy, SqlConnection sqlConnection, DataTable dataTable)
{
sqlBulkCopy.WriteToServer(dataTable);

dataTable.Rows.Clear();
}

}

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:20 PM
Points: 13,252, Visits: 10,133
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 LessThanDot.

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

Add to briefcase

Permissions Expand / Collapse