Import Excel Data Into SQL Server (Skip Rows)


I have included two snippets:
The first one is to use SqlBulkCopy:

protected void Button1_Click(object sender, EventArgs e) 
    {             // Connection String to Excel Workbook 
        string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\property.xls;Extended Properties=""Excel 8.0;HDR=YES;"""; 
 
        // Create Connection to Excel Workbook 
        using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(excelConnectionString)) 
        { 
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(" Select CountyCode,PropertyAppraiserName FROM [county$]", connection); 
 
            connection.Open(); 
 
            // Create DbDataReader to Data Worksheet 
            using (System.Data.OleDb.OleDbDataReader dr = command.ExecuteReader()) 
            { 
                // SQL Server Connection String 
                string sqlConnectionString = @"Data Source=myServer;Initial Catalog=mytest1;Integrated Security=True"; 
 
                // Bulk Copy to SQL Server 
                using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(sqlConnectionString)) 
                { 
                    
                    bulkCopy.DestinationTableName = "dbo.PropertyAppraiser"; 
 
 
                    //Define ColumnMappings: source –--destination  
 
                    bulkCopy.ColumnMappings.Add("CountyCode", "DesCountyCode"); 
                    bulkCopy.ColumnMappings.Add("PropertyAppraiserName", "DesPropertyAppraiserName"); 
                   
 
 
                    DataTable mydt = new DataTable(); 
 
                    mydt.Load(dr); 
 
                    IEnumerable<DataRow> RowsSkip5 = mydt.AsEnumerable().Skip(5); 
 
 
 
                    bulkCopy.WriteToServer(RowsSkip5.ToArray()); 
 
                    //bulkCopy.WriteToServer(dr); 
 
 
                } 
            } 
        } 
    }

The second one is to use OPENROWSET:


  protected void Button2_Click(object sender, EventArgs e) 
    { 
 
        // SQL Server Connection String  
        string sqlConnectionString = @"Data Source=myInstance;Initial Catalog=myDBName;Integrated Security=True"; 
        SqlConnection conn = new SqlConnection(sqlConnectionString); 
        string strSQL = @"INSERT INTO dbo.PropertyAppraiser (DesCountyCode, DesPropertyAppraiserName) SELECT F1, F2 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\property.xls;HDR=no', 'SELECT * FROM [county$a5:g]')"; 
        SqlCommand cmd = new SqlCommand(strSQL, conn); 
 
        conn.Open(); 
        cmd.ExecuteNonQuery(); 
        conn.Close(); 
 
    }

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s