Excel to SQL Server - C#

Question

Idea:

Read a XLS and upload the data on a Microsoft SQL

Problem:

Only the first column is being upload to the DB

My 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");
   }
  }

Table on the DB:

CREATE TABLE [dbo].[Table1](
    [student] [varchar](50) NULL,
    [rollno] [int] NULL,
    [course] [varchar](50) NULL
) ON [PRIMARY]
GO

If you wanna access 2 images from examples of the XLS and DB: https://drive.google.com/drive/folders/0B98UpTa2n4XbeHZtOHU2cVotUms?usp=sharing

Finally, most part of this code I took from: https://code.msdn.microsoft.com/office/Import-Excel-Spreadsheet-2b7ca7cf#content I changed a little bit but most ideas come from these links.

Any help is welcome! Thank you.

oerkelens · Accepted Answer · 2017-06-16 13:09:08Z

5

A bit of a guess, but you have:

string myexceldataquery = "select student from [Sheet1$]";

And then you say

Only the first column is being upload to the DB

That seems to be completely expected if you only select on column.

Maybe select the other field you want as well (I'm guessing them!):

string myexceldataquery = "select student, rollno, course from [Sheet1$]";

answered Jun 16, 2017 at 13:09

oerkelens

5,1711 gold badge25 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FFLS Over a year ago

Wow, that was juvenile from my part! Yes, your guess was the problem. Thank you!

B Minster · Accepted Answer · 2017-06-16 13:27:03Z

0

It would be much easier on you if you did a Save As on your excel file and turned it into a .csv file. Then you could use TextFieldParser and set the delimiter as the comma.

using (TextFieldParser parser = new TextFieldParser(csvFilePath))
{
    Dictionary<string, CustomRecord> csvDictionary = new Dictionary<string, CustomRecord>();

    parser.TextFieldType = FieldType.Delimited;
    parser.SetDelimiters(",");
    while (!parser.EndOfData)
    {
        //write to custom record
    }

Later just write the CustomRecord to the DB.

foreach (KeyValuePair<string, CustomRecord> kvp in CustomRecord)
{
    //sql insert statement here using the key values from the dictionary as your sql values)
}

answered Jun 16, 2017 at 13:27

B Minster

3413 silver badges18 bronze badges

Collectives™ on Stack Overflow

Excel to SQL Server - C#

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related