ASP.NET - how to parse data from a Excel file and save to database.

Asked By aman on 01-Oct-11 02:22 AM
hi all,

i have created a form like below diagram to upload a file from the user.


 the above form will select the xlsx file extension only from the user and i wat to parse the data of that excel file to sql server database.

below is the code i have used to save the file to the server.

      protected void Button1_Click(object sender, EventArgs e)
      {
        if ((FileUpload1.PostedFile != null) && (FileUpload1.PostedFile.ContentLength > 0))
        {
          string filename = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
          string extension = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName);
          string location = Server.MapPath("UploadedFiles")+"\\"+filename;
          if (extension == ".xlsx")
          {
            try
            {
              FileUpload1.PostedFile.SaveAs(location);
              Label2.Text = "The File has been successfully uploaded";
            }
            catch (Exception ex)
            {
              Label2.Text = "Error : " + ex.Message.ToString();
            }
          }
          else
          {
            Label2.Text = "Please Select a .docx file only";
          }
        }
        else
        {
          Label2.Text = "Please Enter a File";
        }
      }

my requirement is to parse the selected xlsx file and save it to sql server data base.

please help me out ....

thanks and regards
Aman
Reena Jain replied to aman on 01-Oct-11 02:27 AM
hi,

http://support.microsoft.com/kb/321686 several methods:

  • SQL Server Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • SQL Server linked servers
  • SQL Server distributed queries
  • ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
  • ADO and the Microsoft OLE DB Provider for Jet 4.0

If the wizard (DTS) isn't working (and I think it should) you could try something like this http://www.devasp.net/net/articles/display/771.html which basically suggests doing something like

INSERT INTO [tblTemp] ([Column1], [Column2], [Column3], [Column4])

SELECT A.[Column1], A.[Column2], A.[Column3], A.[Column4]
FROM OPENROWSET 
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Excel.xls;HDR=YES', 'select * from [Sheet1$]') AS A;
Suchit shah replied to aman on 01-Oct-11 02:43 AM

Either read directly using stored proc

http://support.microsoft.com/kb/321686

Or through ado.net for that refer my article

http://www.aspsnippets.com/post/2009/02/04/Read-Excel-using-ADONet.aspx

dipa ahuja replied to aman on 01-Oct-11 07:01 AM
Untitled document
private void button2_Click(object sender, EventArgs e)
{
   string ExcelConstr = FileUplpad1.PostedFile;
 
   string SqlConstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;";
   SqlConstr += "Integrated Security=True;User Instance=True";
 
   using (OleDbConnection con = new OleDbConnection(ExcelConstr))
   {
     con.Open();
     OleDbCommand com = new OleDbCommand("Select * from [Sheet1$]", con);
     OleDbDataReader dr = com.ExecuteReader();
     using (SqlConnection sqlcon = new SqlConnection(SqlConstr))
     {
       sqlcon.Open();
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
       {
         bulkCopy.DestinationTableName = "Table1";
         bulkCopy.ColumnMappings.Add("srno", "srno");
         bulkCopy.ColumnMappings.Add("amount", "amount");
         bulkCopy.WriteToServer(dr);
       }
     }
     dr.Close();
     dr.Dispose();
   }
   MessageBox.Show("successfully imported!");
   //display the imported data in the datagrid
   SqlDataAdapter da = new SqlDataAdapter("select * from Table1", SqlConstr);
   DataTable dt = new DataTable();
 
   da.Fill(dt);
 
   dataGridViewX1.DataSource = dt;
}
Neha Garg replied to aman on 02-Oct-11 03:35 PM

Below is the code to read from an excel file to a dataset. After retrieving into the datset you can read from it and insert into database :

 Dim connectionString As String
       
          connectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=<<Path>>;Extended Properties=Excel 12.0;"
             Dim objConn As New OleDbConnection(connectionString)
        objConn.Open()
        Dim strConString As String = "SELECT * FROM [Sheet1$]"
        Dim objCmdSelect As New OleDbCommand(strConString, objConn)
        ' Create new OleDbDataAdapter that is used to build a DataSet
        ' based on the preceding SQL SELECT statement.
        Dim objAdapter1 As New OleDbDataAdapter()
        ' Pass the Select command to the adapter.
        objAdapter1.SelectCommand = objCmdSelect
        ' Create new DataSet to hold information from the worksheet.
        Dim objDataset1 As New DataSet()

        ' Fill the DataSet with the information from the worksheet.
        objAdapter1.Fill(objDataset1, "ExcelData")
        ' Clean up objects.
        objConn.Close()