Import 100,000 rows of data from Excel to database using C# [duplicate]

Question

I am trying to import data from Excel into SQL Server 2008 R2. Using the OLEDB provider. I am using Visual Studio 2010. ACE.OLEDB provider is not registered with my Visual Studio. I have 100,000 records in my Excel but it is copying only 65,535. The following is my code:

namespace WebApplication1
{
public partial class Upload_page : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        Response.Write("Working");
        string path = "path";
        OleDbConnection connection = null;
        if (FileUpload1.HasFile)
        {
            path = string.Concat(Server.MapPath("~/Uploaded Folder/" + FileUpload1.FileName));
            FileUpload1.SaveAs(path);
        }
        path = "C:\\Users\\temp\\Documents\\visual studio 2010\\Projects\\WebApplication1\\WebApplication1\\Uploaded Folder\\TempReport.xls";
        try
        {
            string excelConnectionString = string.Format("Provider=Microsoft.jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", path);
            connection = new OleDbConnection();
            connection.ConnectionString = excelConnectionString;
            OleDbCommand command = new OleDbCommand("select * from [ISO$]", connection);
            connection.Open();
            // Create DbDataReader to Data Worksheet
            DbDataReader dr = command.ExecuteReader();
            string sqlConnectionString = @"Initial Catalog=CensusTrack;Data Source=CWVQ-2K12E1D;Integrated Security=SSPI;";

            // Bulk Copy to SQL Server 
            SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
            bulkInsert.BulkCopyTimeout = 0;
            bulkInsert.BatchSize =0;
            bulkInsert.DestinationTableName = "[dbo].[2015_ISO]";
            bulkInsert.WriteToServer(dr);
            connection.Close();
            Response.Write("Completed");
            //Label1.Text = "Ho Gaya";
        }

        catch (Exception obj)
        {
            connection.Close();

        }
    }

}
}

Can any one help me so that I can copy all the data from the Excel to the database table in one go?

Community · Accepted Answer · 2017-05-23 12:04:03Z

0

You can directly use SQL Mgmt studio for importing excel sheets.

Right click Select Tasks -> Import Data

Please see https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/

Also see this question Import Excel spreadsheet columns into SQL Server database

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Dec 13, 2015 at 8:39

Amitd

4,8598 gold badges58 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Swapna Over a year ago

But the issue is it is importing only 65535records remaining are not getting imported

Amitd Over a year ago

have you tried with mgmt tool?

Collectives™ on Stack Overflow

Import 100,000 rows of data from Excel to database using C# [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related