...

View Full Version : problems with sqlbulkcopy



fogofogo
06-19-2007, 05:31 PM
Hello All,

Newbie here. I'm trying to copy data from and excel file to a table in a database. I have the exact same column names in the database as the excel file, with the exception of an ID column, that is set as a primary key and auto-incrments.

So here are the column structures

database = ID, AccountNo, Name, PhoneNo, Email

Excel file = AccountNo, Name, PhoneNo, Email

I am experiencing a problem where the data in the first column of the excel file (AccountNo), is trying to go into the first column of my database( ID ). I'm sure this is the nature of sqlbulkcopy, but does anyone know of a way around this? Here is my class for the copy...


public static void excelimport(string filename)
{
// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0";

// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand("Select ID, AccountNo, Name, PhoneNo, Email FROM [Data$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data source = londevs; initial catalog=cashins; integrated security=true";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Accounts";
bulkCopy.WriteToServer(dr);
}

}

}

}
Thanking you in advance

javabits
06-19-2007, 07:32 PM
I think you just need to use ColumnMappings. See below.


public static void excelimport(string filename)
{
// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0";

// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand("Select ID, AccountNo, Name, PhoneNo, Email FROM [Data$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data source = londevs; initial catalog=cashins; integrated security=true";

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Accounts";
bulkCopy.ColumnMappings.Add("AccountNo", "AccountNo");
bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.ColumnMappings.Add("PhoneNo", "PhoneNo");
bulkCopy.ColumnMappings.Add("Email", "Email");
bulkCopy.WriteToServer(dr);
}

}

}

}

Let us know how it works.

semper fi...

fogofogo
06-20-2007, 03:11 PM
It worked - Javabits is a legend!

Thank you!!!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum