Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Sep 2005
    Posts
    89
    Thanks
    2
    Thanked 0 Times in 0 Posts

    problems with sqlbulkcopy

    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...

    Code:
        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

  • #2
    New Coder
    Join Date
    May 2007
    Location
    SF, CA
    Posts
    55
    Thanks
    0
    Thanked 4 Times in 4 Posts
    I think you just need to use ColumnMappings. See below.

    Code:
    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...

  • #3
    New Coder
    Join Date
    Sep 2005
    Posts
    89
    Thanks
    2
    Thanked 0 Times in 0 Posts
    It worked - Javabits is a legend!

    Thank you!!!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •