Riddhi Valecha

Riddhi Valecha

  • 422
  • 3.3k
  • 416.8k

Insert Data From Excel To SQL Server On Button Click

Oct 13 2014 4:51 AM
Hi all,

I need help in making the following application.

My requirement is -
1. The user have an excel sheet (.xls or .csv) files.
2. The user logs in the system and uploads the file. (Using File Upload button).
3. User clicks "OK" Button.
4. On this button, I have to fire an UPDATE and INSERT Query.
-----------
Queries depend on the data in excel sheet.
-------
The excel sheet is as follows (with 4 columns) -
------------
ID
Serial Number
Item CodeItem Name
Assigned To
1
I1
C1
Mouse
Sunil
2
I2
C2
Keyboard
Ajinkya
3
I3
C3
Keyboard
Aditya

Now, on the button click, the system will fire the query as -
Update tablename set itemcode = Item Code, Item Name = Item Name, Assigned To = Assigned To where Serial No = Serial Number

If Serial Number is not there in the database table, then
Insert into temptable (Serial Number, Item Code, Item Name, Assigned To) values
(Serial Number, Item Code, Item Name, Assigned To)
-----------
Points:-
1. I have to use stored procedures. I cannot write queries in aspx.cs file.
2. File extension can be - xls, xlsx, or csv .

-----------
Please help.
Thanks a lot in advance...

Answers (7)

0
Ahmed JS

Ahmed JS

  • 0
  • 102
  • 756
Nov 10 2014 10:00 AM
0
Riddhi Valecha

Riddhi Valecha

  • 422
  • 3.3k
  • 416.8k
Oct 21 2014 3:31 AM
I got an error saying -

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
0
Riddhi Valecha

Riddhi Valecha

  • 422
  • 3.3k
  • 416.8k
Oct 16 2014 4:13 AM
Hi...

I implemented your method.

But, only first row got inserted.

Please provide the sample code if you have...

Thanks a lot in advance...

Please provide it as soon as possible, it is urgent..
0
Riddhi Valecha

Riddhi Valecha

  • 422
  • 3.3k
  • 416.8k
Oct 14 2014 1:39 AM
Hi Abhishek,

Thanks a lot...

Please provide the sample code if possible.

Thanks a ton in advance.

0
Abhishek Kumar

Abhishek Kumar

  • 280
  • 6.3k
  • 2.7m
Oct 13 2014 7:39 AM
Riddhi,

Below is steps you need to follow.

On button click after Login

Validation for xls, xlsx, or csv using below client site code


<script type ="text/javascript">

    var validFilesTypes=["xlsx", "csv", "xls"];

    function ValidateUpload()

 {

     var file = document.getElementById("<%=FileUpload1.ClientID%>");

      var label = document.getElementById("<%=Label1.ClientID%>");

      var path = file.value;

      var ext=path.substring(path.lastIndexOf(".")+1,path.length).toLowerCase();

      var isValidFile = false;

      for (var i=0; i<validFilesTypes.length; i++)

      {

        if (ext==validFilesTypes[i])

        {

            isValidFile=true;

            break;

        }

      }

      if (!isValidFile)

      {

        label.style.color="red";

        label.innerHTML="Invalid File. Please upload a File with" +

         " extension:\n\n"+validFilesTypes.join(", ");

      }

      return isValidFile;

     }

</script>


Read the data from fiel to data set

use below link

http://www.codeproject.com/Tips/300639/Import-Data-from-excel-to-SQL-server-using-Csharp

Then Write a code to check the SerialNumber from database

Then 

do

Update 

else 

Insert

Hope these steps will help.

Let me know if you want sample application or code for this.


Abhishek


-1
Jonathan Sterling

Jonathan Sterling

  • 0
  • 19
  • 0
Jun 29 2015 5:06 AM
You need a way to read all those different file formats and for that I would suggest you to try out this .NET's component for excel file formats.
Also here you can find a code sample that shows how to iterate through all cells in all rows and retrieve their values.