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); 
            // 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(); 
                    IEnumerable<DataRow> RowsSkip5 = mydt.AsEnumerable().Skip(5); 

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


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s