parse excel sheet from the client computer and insert in the database

[Jean Gwyneth]

Hi there,

I am trying to create an upload functionality for my application.
User can only upload an excel format file.
I already have a standard formatted excel (which means i don't have to worry about the column fields,file extension, excel version etc..). User just have to fill the cells.

Well, it's not really uploading the excel file because what i wanted to do is just parse the excel sheet, get its data and insert it in the sql server database.

The reason for this is that I only need the data from the excel file and not the excel file itself.

I am able to do this when run in my local computer but once online, I get an error of
"The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data."

I have properly set up the permissions and 100% sure that the file is not open anywhere.

Also tried putting the excel file in the server and read from there but found no luck at all.

Now, my question is, Is my approach really possible ( to parse the excel from the remote computer and get its data) then insert in the database?

Here's how I do it:

filePath = IO.Path.GetFullPath(FileUpload1.PostedFile.FileName)

           
          
       try
            'start parsing

                Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= """ + filePath + """;Extended Properties=Excel 8.0;"

                Dim rawTable As DataTable = New DataTable("ExcelTable")

                ' Parse the excel file.
                sheetname = "Sheet1"


                Using adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM[" + sheetname + "$]", connStr)
                    adapter.Fill(rawTable) ' Fill to get the column headers.
                End Using


                Using scope As TransactionScope = New TransactionScope()

                    'Dim _err As Integer = 0
                    For i As Integer = 0 To rawTable.Rows.Count - 1

                       obj.Insert(rawTable(i)("Title").ToString,             rawTable(i)("URL").ToString)
                    
Next

If Err.Number <> 0 Then

                        
                            Transaction.Current.Rollback()
                    Else
                        
                       

                        scope.Complete()

                    End If

                End Using
            Catch ex As Exception
                

            End Try

What am i missing?

Thanks in advance for any help.

-rikuna

[dnanetwork]

{Fixing This Issue}
http://forums.asp.net/t/1360895.aspx
{Tutorial}
http://www.dotnetcurry.com/ShowArticle.aspx?ID=138&AspxAutoDetectCookieSupport=1

Hope that helps!

[Jean Gwyneth]

hi dnanetwork,

thanks for the response.!

I've already tried these links and found them very useful.

however, i'm still in the issue of making it run online.

i am able to make this run in my localhost but no luck in the live one.

thanks for the help anyway! :)

[Jean Gwyneth]

Hi All,

In case someone gets trapped in the same situation i've posted above,

First, you need to upload the file in any folder in the server so the system can parse it.
The server won't be able to parse the file if it is in the client computer.
In my case, I saved it in the App_Data folder.

filePath = "D:\testSite\App_Data\test.xls"
FileUpload1.PostedFile.SaveAs(filePath)

           
          
       try
            'start parsing

                Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= """ + filePath + """;Extended Properties=Excel 8.0;"

                Dim rawTable As DataTable = New DataTable("ExcelTable")

                ' Parse the excel file.
                sheetname = "Sheet1"


                Using adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM[" + sheetname + "$]", connStr)
                    adapter.Fill(rawTable) ' Fill 
                End Using


                Using scope As TransactionScope = New TransactionScope()

                   
                    For i As Integer = 0 To rawTable.Rows.Count - 1

                       obj.Insert(rawTable(i)("Title").ToString,             rawTable(i)("URL").ToString)
                    
Next

If Err.Number <> 0 Then

                        
                            Transaction.Current.Rollback()
                    Else
                        
                       

                        scope.Complete()

                    End If

                End Using
            Catch ex As Exception
                

            End Try

after doing this, parsing is fixed.

However, I got another error of :

"Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"

This is a different story, but just for everyone's info,

Basically, it is the Transaction that's causing this problem.

The transaction should be kept as short as possible and should only contain queries (insert, update etc..) and no other form interactions.

To sort out this problem, remove everything inside the transaction code and leave only the insert.

put the transaction inside a try and catch block to the handle exceptions.

And voila! a cleaner working code!

-rikuna

[mono_jit23]

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;

public partial class Transaction_Excel : System.Web.UI.Page
{
    SqlConnection connect = new SqlConnection(ConfigurationManager.AppSettings["SqlCon"].ToString());

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        string NewFileName = "Transaction.xls";
        HttpPostedFile ObjFile;
        ObjFile = Request.Files[0];

        if (ObjFile.FileName != "" && ObjFile.ContentLength != 0)
        {
            try
            {
                ObjFile.SaveAs(Request.MapPath("Excel/") + NewFileName);
                Label1.Text = "File Upload Completed";
                Response.Write(CodeHelper.CodeHelper.ShowMessage("File Uploaded"));
                Session.Add("ExcelFileName", NewFileName);
            }
            catch (Exception ex)
            {
                Label1.Text = "File Could Not Be Uploaded";
                Response.Write(CodeHelper.CodeHelper.ShowMessage("File Could Not Be Uploaded"));
            }
        }
        else
        {
            Response.Write(CodeHelper.CodeHelper.ShowMessage("You Must Select A File Before Upload"));
        }
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (Session["ExcelFileName"] != "" && Session["ExcelFileName"]!=null)
        {
            //string path = "Excel\\Test.xls";//Here i use Fileupload control in DOTNET2005
            OleDbConnection con;
            System.Data.DataTable dt = null;
            //Connection string for oledb
            string ExcelFileName = Session["ExcelFileName"].ToString();
            string conn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + ConfigurationManager.AppSettings["ExcelPath"].ToString() + ExcelFileName +
                                                                                "; Extended Properties=Excel 8.0;";

            if (connect.State == ConnectionState.Open)
            {
                connect.Close();
            }
            connect.Open();
            con = new OleDbConnection(conn);
            try
            {
                //truncating Transfer table.
                SqlCommand truncateCommand = new SqlCommand("truncate table Transaction_Excel", connect);
                truncateCommand.ExecuteNonQuery();

                con.Open();
                //get the sheet name in to a table
                dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                String[] excelsheets = new String[dt.Rows.Count];
                int i = 0;
                //using foreach get the sheet name in a string array called excelsheets[]
                foreach (DataRow dr in dt.Rows)
                {
                    excelsheets[i] = dr["TABLE_NAME"].ToString();
                    i++;
                }
                string temp = excelsheets[0].ToString();
                //string temp = dt.Rows[0][3].ToString();
                DataSet ds = new DataSet();
                //foreach (string temp in excelsheets)
                //{

                // Query to get the data for the excel sheet 
                //temp is the sheet name
                string query = "select * from [" + temp + "]";
                OleDbDataAdapter adp = new OleDbDataAdapter(query, con);
                adp.Fill(ds, temp);//fill the excel sheet data into a dataset ds
                DataTable ledger = new DataTable();
                ledger = ds.Tables[0];
                //connect.Open();
                SqlBulkCopy bulk = new SqlBulkCopy(connect);

                bulk.DestinationTableName = "Transaction_Excel";
                bulk.BatchSize = 100;
                bulk.BulkCopyTimeout = 100;
                bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulk_SqlRowsCopied);
                //bulk.BatchSize = 900;
                bulk.ColumnMappings.Add("Financial Year", "Financial_Year");
                bulk.ColumnMappings.Add("Business Unit ID", "Bussiness_Unit_ID");
                bulk.ColumnMappings.Add("Type of Entry", "type_Of_Entry");
                bulk.ColumnMappings.Add("Transaction ID", "Transaction_ID");
                bulk.ColumnMappings.Add("Transaction Date", "Transaction_Date");
                bulk.ColumnMappings.Add("Account ID", "Account_ID");
                bulk.ColumnMappings.Add("Control Account ID", "Control_Account_ID");
                bulk.ColumnMappings.Add("Amount", "Amount");
                bulk.WriteToServer(ledger);

                ds.Clear();
                con.Close();
                connect.Close();
                //}
                Response.Write(CodeHelper.CodeHelper.ShowMessage("Data Transfer Completed"));
                Label1.Text = "Data Transfer Successful";
                Session.Contents.Remove("ExcelFileName");
            }

            catch (Exception ex)
            {
                Response.Write(CodeHelper.CodeHelper.ShowMessage("Uploaded File Doesn't Match"));
                con.Close();
                connect.Close();
            }
            finally
            {
                con.Close();
                connect.Close();
            }
        }
        else
        {
            Response.Write(CodeHelper.CodeHelper.ShowMessage("You Must Upload File Before Transfer"));
        }
    }

    private static void bulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
    {
      // Console.WriteLine(CodeHelper.CodeHelper.ShowMessage("[{0}] {1} have been copied to the database"));
    }
}