Monday, February 2, 2009

How to import MS Excel data to SQL Server table using c#.net

If you already have data in MS Excel file, and want to migrate your MS Excel data to SQL Server table, follow below steps

1. Lets take an example to import the data to SQL Server table, I am going to import student information data from ms excel sheet to tStudent SQL table,

My Excel sheet structure is looks like


2. Now design a tStudent table in SQL server
Create Table
(
StudentName varchar(64),
RollNo varchar(16),
Course varchar(32),
)

your ms excel sheet and SQL table is ready, now its time to write c# code to import the excel sheet into tStudent table

3.
Add these two name space in your class file

using System.Data.OleDb;

using System.Data.SqlClient;


Use following code

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

}

}


In above function you have to pass ms excel file path as a parameter, if you want to import your data by providing client an access to select the excel file and import, then you might have to use asp.net file control, and upload the excel file on the server in some temp folder, then use the file path of the upload excel file and pass the path in above function. Once data import is completed then you can delete temporary file.


The above method , first delete the existing data from the destination table, then import the excel data into the same table.


13 comments:

  1. Sir,

    Iam using File Upload control in asp.net,c#.Iam able to upload in my local system but not able to upload over VSS(webserver).Please help me in doing this work.

    Thanks and Regards
    ramarao kurapati

    ReplyDelete
  2. Hey!.. you have to make sure you are providing correct path of the directory where you are uploading the file.

    You upload Path should be.
    //ServerName/SourceDirectory:/UploadDirectoryFolderName/

    Please shere your code here, so that i can help you.

    ReplyDelete
  3. I'm using your codes above. It works well except the first record in Excel is missing after the import is done. Do you know why? I look forward to your answer. Thank you.

    ReplyDelete
  4. Hi, I have found the solution. The first row below the header row in excel should be blank. Good codes, great Job!

    ReplyDelete
  5. Yesterday when I worked with excel files I saved it and went to bed,next morning I saw that my file corrupted,but in some hours to me came a friend,he advised me-repairing Excel,as he said this tool is reliable and has free status,it repairing Excel files, except the possibility to export recovered data into a new Microsoft Excel file,repairing Microsoft Excel files,will learn more about this problem and about how to repair an Excel file,contains the most features of registered program for repairing Excel files, except the possibility to export recovered data into a new Microsoft Excel file.

    ReplyDelete
  6. Dear Friends,

    I hope you are doing well. I have launched a web site www.codegain.com and it is basically aimed C#,JAVA,VB.NET,ASP.NET,AJAX,Sql Server,Oracle,WPF,WCF and etc resources, programming help, articles, code snippet, video demonstrations and problems solving support. I would like to invite you as an author and a supporter. Looking forward to hearing from you and hope you will join with us soon.


    Please forward this email to all of your friends who are related IT. Send to us your feed about site also.


    Thank you
    RRaveen
    Founder CodeGain.com

    ReplyDelete
  7. Once I were calculating your data in MS Excel,and after that something happened...My data were lost.But myself helped-Excel restore.It is free as far as I remember,moreover utility work with critical data in Microsoft Excel format.

    ReplyDelete
  8. Yesterday I worked with excel sheet and saved all info.But today all data were lost.And I used-Excel repairs,this tool I saw in one forum.It is free as far as I know,besides that tool can recovered contents and export of recovered data into a new document in Microsoft Excel format.

    ReplyDelete
  9. what if there will different files names, but the data in them has the same fields. I am working on something where there are excel sheet coming in from the client, we need to upload them and extract there data and validate it.

    ReplyDelete
  10. Hi Shaikh,
    I tried your code to upload an excel file to SQL Server. It worked but the first record of the excel file was not there in the SQL Server table. I used a DO...WHHILE loop instead and then all the rows of the excel file were copied to the SQL Server including the firt record.
    Regards,
    Richard

    ReplyDelete
  11. Hi Shaikh,
    I tried your code using excel data into sql server 2005 table but it conection string problem.
    Plz solve me.


    Regards,
    Prince

    ReplyDelete
    Replies
    1. Hi,
      I hope you are setting proper connection string here
      string sSqlConnectionString = "SERVER=MyDatabaseServerName;USER ID=DBUserId;PASSWORD=DBUserPassword;DATABASE=DatabaseName;CONNECTION RESET=FALSE";

      You need to check whether your sql server allows sql authentication, if so you should first try connecting to sql server from ssms manually just to verify the credential you are providing are correct.

      Delete
  12. In my residence when I take bored, afterward I simply ON my PC and open YouTube web page to watch the YouTube video lessons. But I didn't find any there, Thank to your post after searching on google in found your post and I get my solution.

    Optimized360 most reliable medical website design group in U.S

    ReplyDelete