title: Excel to SQL Server - C#
body: <p>Idea:</p>
  
  <ul>
  <li>Read a XLS and upload the data on a Microsoft SQL</li>
  </ul>
  
  <p>Problem:</p>
  
  <ul>
  <li>Only the first column is being upload to the DB</li>
  </ul>
  
  <p>My code:</p>
  
  <pre><code>  private void button1_Click(object sender, EventArgs e) {
     // string path = @"XXXX\xls_test\Book1.xlsx";
     string path = @ "XXXX\xls_test\Book1.xlsx";
     ImportDataFromExcel(path);
    }
  
  
    public void ImportDataFromExcel(string excelFilePath) {
     //declare variables - edit these based on your particular situation 
     string ssqltable = "Table1";
     // 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 student 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; IMEX=1;\"";
      string sexcelconnectionstring = @ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= XXXX\xls_test\Book1.xlsx" + ";" + "Extended Properties=Excel 12.0";
  
      string ssqlconnectionstring = "XXXX";
      //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);
      }
      dr.Close();
      oledbconn.Close();
      MessageBox.Show("File imported into sql server.");
     } catch (Exception ex) {
      //handle exception
      MessageBox.Show(ex.ToString());
      MessageBox.Show("Enter exception");
     }
    }
  </code></pre>
  
  <p>Table on the DB:</p>
  
  <pre><code>CREATE TABLE [dbo].[Table1](
      [student] [varchar](50) NULL,
      [rollno] [int] NULL,
      [course] [varchar](50) NULL
  ) ON [PRIMARY]
  GO
  </code></pre>
  
  <p>If you wanna access 2 images from examples of the XLS and DB:
  <a href="https://drive.google.com/drive/folders/0B98UpTa2n4XbeHZtOHU2cVotUms?usp=sharing" rel="nofollow noreferrer">https://drive.google.com/drive/folders/0B98UpTa2n4XbeHZtOHU2cVotUms?usp=sharing</a></p>
  
  <p>Finally, most part of this code I took from:
  <a href="https://code.msdn.microsoft.com/office/Import-Excel-Spreadsheet-2b7ca7cf#content" rel="nofollow noreferrer">https://code.msdn.microsoft.com/office/Import-Excel-Spreadsheet-2b7ca7cf#content</a>
  I changed a little bit but most ideas come from these links.</p>
  
  <p>Any help is welcome! Thank you. </p>