C# .NET - Bulk Upload from Excel sheet to Sql server

Asked By srikanth panneer selvam on 10-Jul-09 01:25 AM
Hi all
i am in need of Uploading bulk data's  from excel to tables in database.

Please can anyone help me to do the same also if any sample project is there means please kindly help me...
thanks in advance
i am Expecting in Asp.net with C#

Great samples for excel to sql bulk upload - Sakshi a replied to srikanth panneer selvam on 10-Jul-09 01:39 AM

Great samples for excel to sql bulk upload

http://www.dotnetspider.com/.../24908-Upload-excel-file-content-SQl-server-using-SQL.aspx

http://www.simple-talk.com/community/forums/thread/2307.aspx

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66231

Regards,

http://www.codecollege.NET

 

 

Alice J replied to srikanth panneer selvam on 10-Jul-09 01:57 AM

Hey, this is a one stop solution. Make use of it.

http://www.codeproject.com/KB/database/bulkupload.aspx

Bulk Upload from Excel sheet to Sql server - mv ark replied to srikanth panneer selvam on 10-Jul-09 02:30 AM

You can import Excel data to SQL Server by using:
  • SQL Server Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • SQL Server linked servers
  • SQL Server distributed queries
  • ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
  • ADO and the Microsoft OLE DB Provider for Jet 4.0
This article shows samples of each - http://support.microsoft.com/kb/321686

Using ASP.NET with C# to bulk upload would introduce an additional layer. IMHO, SSIS is the best bet.
RE - Ravenet Rasaiyah replied to srikanth panneer selvam on 10-Jul-09 02:34 AM
Hi

Here code for you.

public void importDataFromExcel(string excelFilePath)

{

//Declare Variables - Edit these based on your particular situation

string sSQLTable = "tDataMigrationTable";

// make sure your sheet name is correct, here sheet name is Sheet1, so you can change your sheet name if have different

string myExcelDataQuery = "Select StudentName,RollNo,Course from [Sheet1$]";

try

{

//Create our connection strings

string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";

string sSqlConnectionString = "SERVER=MyDatabaseServerName;USER ID=DBUserId;PASSWORD=DBUserPassword;DATABASE=DatabaseName;CONNECTION RESET=FALSE";

//Execute a query to erase any previous data from our destination table

string sClearSQL = "DELETE FROM " + sSQLTable;

SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);

SqlConn.Open();

SqlCmd.ExecuteNonQuery();

SqlConn.Close();

//Series of commands to bulk copy data from the excel file into our SQL table

OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);

OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);

OleDbConn.Open();

OleDbDataReader dr = OleDbCmd.ExecuteReader();

SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);

bulkCopy.DestinationTableName = sSQLTable;

while (dr.Read())

{

bulkCopy.WriteToServer(dr);

}

OleDbConn.Close();

}

catch (Exception ex)

{

//handle exception

}

}


More details here http://shaikhnizam.blogspot.com/2009/02/how-to-import-ms-excell-data-to-sql.html

Thank you
http://www.codegain.com
this is good one u can upload excel and fill datatabable and then bulk insert in to database
Web Star replied to srikanth panneer selvam on 10-Jul-09 04:58 AM
string _strExcelFilename = txt_filename.Text;
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                        @"Data Source=" + _strExcelFilename + ";" +
                        @"Extended Properties=" + Convert.ToChar(34).ToString() +
                        @"Excel 8.0;HDR=YES" + Convert.ToChar(34).ToString();
OleDbConnection  con = new OleDbConnection(ConnectionString);

                    try
                    {
                    string _strSheetName = "excel sheet name";
                        string ssql = "SELECT * FROM [" + _strSheetName + "$" + "]";


                        OleDbDataAdapter oleAdapter = new OleDbDataAdapter(ssql, con);
                        DataSet ds =new DataSet();
                        oleAdapter.Fill(ds, "details");
                      
           
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
               Boolean IsTrue = true;
               int count = 0;   
                           for(count = 0;count < ds.Tables[0].Rows.Count;count++)
               {
                //Check all the necessary condtions here
                                if(condtion == false)
                {
                   IsTrue = false;   
                                   //code to exit the for loop
                    break;      
                }
               }   
               if(((count + 1) == ds.Tables[0].Rows.Count) && IsTrue == true)
               {
                //Insert the excel sheet to your table using bulkcopy..
                SqlBulkCopy bulkCopy = new SqlBulkCopy("Connection String to database",                                 SqlBulkCopyOptions.TableLock);
                bulkCopy.DestinationTableName = "destination Table";
                bulkCopy.WriteToServer(ds.Tables[0]);

                }
                        }
                    }
                    catch (Exception ex)
                    {
                      
                        Utility.ThrowMessage (ex.Message );
                        return;
                    }
Filip Krnjic replied to srikanth panneer selvam on 10-Jul-09 08:00 AM
Hi,

easiest and fastest way to deal with data from Excel in .NET is to use 3rd party component like http://www.gemboxsoftware.com/GBSpreadsheet.htm which has free version for commercial use (limit is 150 rows).

Here you can see a list of reasons why http://www.gemboxsoftware.com/Excel-Automation.htm

Filip
GemBox Software - http://www.gemboxsoftware.com/