problems with sqlbulkcopy

[fogofogo]

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

[lmf232s]

Well I believe your first problem is that your table and the excel file do not match and thats because of your ID field in your table.

Have you tried to remove the ID out of the select statement.

Change
OleDbCommand command = new OleDbCommand("Select ID, AccountNo, Name, PhoneNo, Email FROM [Data$]", connection);
TO
OleDbCommand command = new OleDbCommand("Select AccountNo, Name, PhoneNo, Email FROM [Data$]", connection);

[fogofogo]

You are right thanks. But it wasn't working before that either.

This code got it working...


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);
}

}

}

}

[lmf232s]

So basically you mapped the DB Columns and the excel columns, makes sense.