Click here to Skip to main content
15,940,430 members
Please Sign up or sign in to vote.
2.33/5 (3 votes)
See more:
Hi,

I want to implement the excel file upload into database table functionality , but excel file should contain 65K Rows & 40 columns.
so pls tell me the best/optimal way to do this functionality.
Posted
Updated 26-Oct-19 3:54am
v3
Comments
Nelek 30-Aug-13 4:59am    
Not really clear, could you use the "improve question" widget and explain better?
phil.o 30-Aug-13 5:26am    
What is a lakh ?
phil.o 30-Aug-13 5:49am    
OK, a lakh is one million record in Excel. I'd never thought MS had invented their own words for quantities. Thanks :)
Luc Pattyn 27-Oct-19 18:07pm    
Actually lakh is an Indian word meaning "one hundred thousand".

ref

I think use should use million instead of lakhs. :)

I suggest you look at SQL Server Express. A million does not say much, a million with one field (column) is one thing, a million with 100 fields is another. You should be able to connect directly to Excel.

I would not recommend excel vba. I think the limit is million record in excel (2013- 100 million rows)
 
Share this answer
 
http://hemgoogali.wordpress.com/2012/10/19/import-excel-to-sqlserver2005/[^]


Please follow this step. Open the snapshots. It will help you but this is manually works.
 
Share this answer
 
v2
If new version of excel support more than 65000 rows to export then you can

C#
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\testing.xlsx;Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;'");
        OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]");
        if (conn.State == System.Data.ConnectionState.Open)
        {
            conn.Close();
        }
        conn.Open();
        OleDbDataAdapter adap = new OleDbDataAdapter(cmd);
        DataSet ds = new DataSet();
        adap.Fill(ds);
        this.GridView1.DataSource = ds.Tables[0].DefaultView;
        conn.Close();

another way


C#
string[] array2 = Directory.GetFiles(@"D:\doc", "*.xls");
 
foreach (string name in array2)
{
    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""";
    excelConnectionString=string.Format(excelConnectionString,name).Trim();
    
    Console.WriteLine(excelConnectionString);
    
    // Create Connection to Excel Workbook
    using (OleDbConnection connection =new OleDbConnection(excelConnectionString))
    {
        OleDbCommand command = new OleDbCommand("Select Field1,Field2,Field3 FROM [Sheet1$]", connection);
    
        connection.Open();
        
        // Create DbDataReader to Data Worksheet
        using (DbDataReader dr = command.ExecuteReader())
        {
            // SQL Server Connection String
            string sqlConnectionString = @"Data Source=Machine007\SQLEXPRESS;Initial Catalog=BankDB;Integrated Security=True";
    
            // Bulk Copy to SQL Server
            using (SqlBulkCopy bulkCopy =
                    new SqlBulkCopy(sqlConnectionString))
            {
                bulkCopy.DestinationTableName = "BankCodes";
                bulkCopy.WriteToServer(dr);
            }
        }
    }
 
}
 
Share this answer
 
v2
Comments
nira.parmar 30-Aug-13 5:50am    
Hi thanks for your suggestion, but I want to upload millions of record into database.
Pls tell the optimal way?
Herman<T>.Instance 30-Aug-13 5:57am    
use BCP
nira.parmar 30-Aug-13 6:05am    
BCP?
Herman<T>.Instance 30-Aug-13 7:11am    
BulkCopy commandline tool. Can be called via Stored procedures and via C#
nira.parmar 30-Aug-13 9:33am    
pls tell me how to use it in c# for import excel file in to database
SqlBulkCopy is probably the best solution for your problem.
You will find loads of tutorial on

SqlBulkCopy
[^]
 
Share this answer
 
v2
Comments
arbaaz jalil 30-Aug-13 8:39am    
updated the link
BulkCopy Command is possible way for this ...
link
Transferring Data Using SqlBulkCopy[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900