How to import data from excel to sql server

Question

0.00/5 (No votes)

See more:

Hello,
I have requirement that i need to fill an amount coloumn from excel to sql server if employee exist in sqltable..please tell me how can i do this ..
What i have tried now has pasted below..

C#

protected void UploadExcel(object sender,EventArgs e)
  {
      string Constr = System.Configuration.ConfigurationManager.ConnectionStrings["HRGold"].ConnectionString;
      string path = fplUploadExcel.PostedFile.FileName;
      string ExcelConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
      OleDbConnection excelConnection = new OleDbConnection(ExcelConstr);
      OleDbCommand oldbcmd = new OleDbCommand("Select [EmployeeId],[EmployeeName],[Amount] From [Sheet1$] where EmployeeReferenceId='" + ddlEmployeeId.SelectedItem.Value + "'", excelConnection);
      excelConnection.Open();
      OleDbDataReader reader = oldbcmd.ExecuteReader();
      SqlBulkCopy sqlBulk = new SqlBulkCopy(Constr);

      while (reader.Read())
      {
          if (ddlEmployeeId.SelectedItem.Value.Equals(reader))
          {
              sqlBulk.DestinationTableName = "PFDummyExcel";
              sqlBulk.WriteToServer(reader);
          }
          else
          {
              lblError.Text = "Employee Not Found";
          }
      }
      excelConnection.Close();
  }

Posted 4-Dec-13 23:29pm

ajays3356

Updated 4-Dec-13 23:43pm

ridoy

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Vi(ky · Answer 1 · 2013-12-05T05:24:00

I done this few weeks ago in my project,some code of my project to upload excel data in Table.I hope my code help you.

C#

public class RecordImport
 {

     List<string> ErrorList = new List<string>();

     public string UploadFile(string tPath, int tId)
     {
         string extnsn = Path.GetExtension(tPath).ToString().ToUpper().Trim();

         string connString = "";
         string strFileType = ".xlsx";
         string path = tPath;
         string query = "";

         OleDbConnection conn;


         if (extnsn == ".CSV")
         {
             connString = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""", Path.GetDirectoryName(tPath));
             query = "SELECT * FROM [" + Path.GetFileName(tPath) + "]";
         }
         else
         {
             if (strFileType.Trim() == ".xls")
             {
                 connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
             }
             else if (strFileType.Trim() == ".xlsx")
             {
                 connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
             }

             //code to get list of all worksheets in excel file
             conn = new OleDbConnection(connString);
             conn.Open();
             DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); ;
             string[] excelSheets = { };
             if (dt != null)
             {
                 excelSheets = new string[dt.Rows.Count];

                 int i = 0;
                 foreach (DataRow row in dt.Rows)
                 {
                     excelSheets[i] = row["TABLE_NAME"].ToString();
                     i++;
                 }
             }
             conn.Close();
             query = "SELECT * FROM [" + excelSheets[0].ToString().Trim() + "]";
         }


         conn = new OleDbConnection(connString);
         conn.Open();

         OleDbCommand cmd = new OleDbCommand(query, conn);
         OleDbDataReader reader = cmd.ExecuteReader();

         var context = new MountSinai_SysEntities1();

         string tableName = context.Table.Find(tId).tableName;
         var tableFieldList = from a in context.TablesField
                              where a.tableId == tId
                              select a.fieldName;

         var fieldListData = from b in context.TablesField
                             where b.tableId == tId
                             select b;

         //1- SQL Bulk Copy  OK
         try
         {
             string excelConString = System.Configuration.ConfigurationManager.ConnectionStrings["NewConnection"].ConnectionString;

             SqlBulkCopy bulkcopy = new SqlBulkCopy(excelConString);
             {
                 bulkcopy.DestinationTableName = tableName;
                 bulkcopy.BulkCopyTimeout = 120;
                 bulkcopy.WriteToServer(reader);
             }
         }

         //2 - when bulk copy failed
         catch (Exception e)
         {
             reader.Dispose();
             reader.Close();

             ErrorList.Add("SQL Bulk Copy Fail : " + e.Message.ToString());
             ArrayList validList = new ArrayList();
             ArrayList validListDataTypeId = new ArrayList();

             var reader1 = cmd.ExecuteReader();

             int b = 0;
             //check coulmn name is same or not
             foreach (var field in fieldListData)
             {
                 string xlsColumnName = reader1.GetName(b).ToUpper().Trim();

                 if (field.fieldName != "Id")
                 {
                     if (field.fieldName.ToString().ToUpper() == xlsColumnName)
                     {
                         validList.Add(field.fieldName.ToString().ToUpper().Trim());
                         validListDataTypeId.Add(field.displayFieldTypeId.ToString().ToUpper().Trim());
                     }
                     else
                     {
                         return "column name or number not Match";
                     }
                     b++;
                 }
             }



             string cellValue;
             while (reader1.Read())
             {
                 int keyFlag = 0;
                 StringBuilder queryBuilder = new StringBuilder("Insert into " + tableName + " Values (");
                 StringBuilder queryBuilderCopy = new StringBuilder("Insert into " + tableName + "_Temp Values (");

                 for (int a = 0; a < validList.Count; a++)
                 {
                     cellValue = reader1[a].ToString().Trim();
                     if (cellValue != "")
                     {
                         if ( Convert.ToInt32( validListDataTypeId[a]) == 21)
                         {
                             if (keyFlag == 0)
                             {
                                 queryBuilder.Insert(0, "OPEN SYMMETRIC KEY WDCSKey DECRYPTION BY CERTIFICATE WDCSCert ");
                                 keyFlag = 1;
                             }

                             queryBuilder.Append("ENCRYPTBYKEY(KEY_GUID('WDCSKey'), '" + cellValue + "'),");
                         }
                         else
                         {
                             queryBuilder.Append("'"+cellValue+"',");
                         }
                     }
                 }
                 queryBuilder.Remove(queryBuilder.Length - 1, 1);
                 queryBuilder.Append(")");

                 try
                 {
                     var Dbcontext = new DBEntities();
                     var returnValue = Dbcontext.Database.SqlQuery<int32>(queryBuilder.ToString()).First();
                     queryBuilder.Clear();
                 }
                 catch(Exception ex)
                 {
                     queryBuilder.Clear();
                 }
             }</int32></string></string>

Ganesh Raja · Answer 2 · 2013-12-04T23:52:00

Try this below code

C#

string Connectionstring = string Connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file_path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";// Change the connection string acording to ur excel version
OleDbConnection myXLConnection = new OleDbConnection(Connectionstring);
OleDbCommand myXLCommand = new OleDbCommand("select * from [Sheet1$]", myXLConnection);
OleDbDataAdapter myXLAdapter = new OleDbDataAdapter(myXLCommand);
DataTable dt = new DataTable();
myXLAdapter.Fill(dt);
SqlBulkCopy copy = new SqlBulkCopy(Connection_string,SqlBulkCopyOptions.FireTriggers); //Connection_string=mssql connection string
copy.DestinationTableName = tablename;
copy.WriteToServer(dt);

How to import data from excel to sql server

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0