C# .NET - Loading Excel data into sql server

Asked By dwarak kanniah on 13-Sep-09 07:29 AM

Hi,
Is it possible to load data from excel spreadsheet to sql server using c#
without giving names in column header

for example:The Spreadsheet looks like below(No Header Names)

A B C
================================
Mark Asst.Professor 25
Thomas SoftwareProfessional 30

Assiging Column Header values to sql server table called employeefields .
A = Name
B = Designation
C = Age

There will be table called Employees which contains columns such as Name,Designation,Age
Based on the fields chosen in Employeefields the records must be inserted in the Employees table in sqlserver

Thanks

I presume you use OLEDB.

[)ia6l0 iii replied to dwarak kanniah on 13-Sep-09 01:49 PM
If you don't specify the "HDR=Yes" in your oleDBConn string, it would read the values right from the first row.  
Sample snippet to read a excel worksheet into a DataTable.

OleDbDataAdapter oleDBAdapter = new OleDbDataAdapter();
OleDbCommand oleDBCommand = new OleDbCommand();

string connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fullfilepath + "; Extended Properties =Excel 8.0;";
OleDboleDBConn oleDBConn = new OleDboleDBConn(connString);
oleDBConn.Open();

DataTable dtExcelData = new DataTable();
oleDBCommand = new OleDbCommand("SELECT * FROM [Sheetname$]", oleDBConn);
oleDBAdapter.SelectCommand = oleDBCommand;

oleDBAdapter.Fill(dtExcelData);

You could then send this data to your webservice, and then send the DataTable to the SQL Server. 
However, if you need to read directly from the excel file and upload from your client itself, you could look at SQLBulkCopy

Sample snippet below:

string connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fullfilepath + "; Extended Properties =Excel 8.0;";

using (OleDboleDBConn oleDBConn =
             new OleDboleDBConn(connString))
{
     OleDbCommand oleDBCommand = new OleDbCommand("SELECT * FROM [Sheetname$]", oleDBConn);

     oleDBConn.Open();
    
    using (DbDataReader dr = oleDBCommand.ExecuteReader())
     {
         string sqloleDBConnString = "Data Source=;Initial Catalog=Test;Integrated Security=True";

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqloleDBConnString))
        {
             bulkCopy.DestinationTableName = "sqlservertablename";
             bulkCopy.WriteToServer(dr);
        }
     }
}
 

re

Web Star replied to dwarak kanniah on 14-Sep-09 12:09 AM
yes ,simply u can use this

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


method

public void WriteToServer(dataset ds)

(

//u will get all id that are faild to insert into database

//then u will do delete and leave the row from excel depends upon that left id

)