Import data from an Excel sheet into a SQL Server database

Question

How to import the data from an Excel sheet into SQL Server database in asp net?

Dim OleDbcon As New OleDbConnection((Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=") & path) + ";Extended Properties=Excel 12.0;")

Dim cmd As New OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon)
Dim objAdapter1 As New OleDbDataAdapter(cmd)

OleDbcon.Open()
Dim dr As DbDataReader = cmd.ExecuteReader()

Dim con_str As String = "Data Source=.;Initial Catalog=studentdetails;Integrated Security=True"

' Bulk Copy to SQL Server 
Dim bulkInsert As New SqlBulkCopy(con_str)
bulkInsert.DestinationTableName = "Table name"
bulkInsert.WriteToServer(dr)
OleDbcon.Close()e here

Answer 1

we will create a method data table in which we will take excel sheet info in data table or data set after that we will push that data into SQL database table using SQL bulk

protected void Button1_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection(@"Data 
Source=SANI2711\SQLEXPRESS;Initial Catalog=customer;Integrated 
Security=True;");
con.Open();
DataTable dt = new DataTable();
dt = DataExcel();
if (dt.Rows.Count > 0)
{
 for()
}
}
catch(Exception ex)
{
Response.Write(ex);
}
}
protected void Button2_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection(@"Data 
Source=SANI2711\SQLEXPRESS;Initial Catalog=customer;Integrated 
Security=True;");
con.Open();
DataTable dt = new DataTable();
dt = DataExcel();
if (dt.Rows.Count > 0)
{
 SqlBulkCopy objbulk = new SqlBulkCopy(con);
 objbulk.DestinationTableName = "customer1";
 objbulk.ColumnMappings.Add("CustomerID", "CustomerID");
 objbulk.ColumnMappings.Add("City", "City");
 objbulk.ColumnMappings.Add("Country", "Country");
  objbulk.ColumnMappings.Add("PostalCode", "PostalCode");
  objbulk.WriteToServer(dt);
  }
 }
 catch (Exception ex)
 {
 Response.Write(ex);
 }
 }
 protected DataTable DataExcel()
 {
 DataTable dt = new System.Data.DataTable();
 try
 {
 string filenname=@"C:\Users\sani singh\Documents\Excel03.xls";
string sWorkbook = "[Sheet1$]";
 string ExcelConnectionString=@"Provider=Microsoft.Jet.OLEDB.4.0;Data 
Source="+filenname+";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
 OleDbConnection OleDbConn = new OleDbConnection(ExcelConnectionString);
 OleDbConn.Open();
 OleDbCommand OleDbCmd = new OleDbCommand(("SELECT * FROM " + sWorkbook), 
 OleDbConn);
 DataSet ds = new DataSet();
 OleDbDataAdapter sda = new OleDbDataAdapter(OleDbCmd);
 sda.Fill(ds);
 dt = ds.Tables[0];
 OleDbConn.Close();
 }
 catch(Exception ex) 
 {
 Response.Write(ex);
 }
 return dt;
 }
 }
 }

Answer 2

Add a DataTable which can hold the Excel data generated via OLEDb.

string excelconnectionstring = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilepath + ";Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text;Jet OLEDB:Max Buffer Size=256;");

using (OleDbConnection oledbconn = new OleDbConnection(excelconnectionstring))
  {
                    using (OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn))
                    {
                        oledbconn.Open();
                        OleDbDataReader dr = oledbcmd.ExecuteReader();


                        dtBulkUpload.Load(dr);
}
}

Then serialize this DataTable to XML which can be sent to SQL Stored Proc. This approach is very useful if there are too many fields and you can send all in a single parameter

using (StringWriter strXML = new StringWriter())
{
   dtBulkUpload.TableName = "BulkUpload";
   dtBulkUpload.WriteXml(strXML, XmlWriteMode.IgnoreSchema, false);
   xmlString = strXML.ToString();
}                                     



using (SqlCommand cmd = new SqlCommand("Stored PROC Name"))
{
   cmd.Parameters.AddWithValue("@dtBulkUpload", bulkUploadData);
                        //SqlParameter returnParameter = cmd.Parameters.Add("@result", SqlDbType.NVarChar);
                        //returnParameter.Direction = ParameterDirection.Output;
                        cmd.Parameters.Add("@result", SqlDbType.NVarChar,3000);
                        cmd.Parameters["@result"].Direction = ParameterDirection.Output;

                        cmd.Connection = con;
                        cmd.CommandType = CommandType.StoredProcedure;

                        con.Open();
                        cmd.ExecuteNonQuery();
                        query = (string)(cmd.Parameters["@result"].Value.ToString());
                        con.Close();

Answer 3

Break this down into two steps:

1) Save the file somewhere - it's very common to see this:

string saveFolder = @"C:\temp\uploads"; //Pick a folder on your machine to store the uploaded files

string filePath = Path.Combine(saveFolder, FileUpload1.FileName);

FileUpload1.SaveAs(filePath); Now you have your file locally and the real work can be done.

2) Get the data from the file. Your code should work as is but you can simply write your connection string this way:

string excelConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 12.0";", filePath); You can then think about deleting the file you've just uploaded and imported.

To provide a more concrete example, we can refactor your code into two methods:

private void SaveFileToDatabase(string filePath)
{
    String strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename='C:\\Users\\Hemant\\documents\\visual studio 2010\\Projects\\CRMdata\\CRMdata\\App_Data\\Database1.mdf';Integrated Security=True;User Instance=True";

    String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
    //Create Connection to Excel work book 
    using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
    {
        //Create OleDbCommand to fetch data from Excel 
        using (OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection))
        {
            excelConnection.Open();
            using (OleDbDataReader dReader = cmd.ExecuteReader())
            {
                using(SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                {
                    //Give your Destination table name 
                    sqlBulk.DestinationTableName = "Excel_table";
                    sqlBulk.WriteToServer(dReader);
                }
            }
        }
    } 
}


private string GetLocalFilePath(string saveDirectory, FileUpload fileUploadControl)
{


    string filePath = Path.Combine(saveDirectory, fileUploadControl.FileName);

    fileUploadControl.SaveAs(filePath);

    return filePath;

}

You could simply then call SaveFileToDatabase(GetLocalFilePath(@"C:\temp\uploads", FileUpload1));

Consider reviewing the other Extended Properties for your Excel connection string. They come in useful!

Other improvements you might want to make include putting your Sql Database connection string into config, and adding proper exception handling. Please consider this example for demonstration only!