Read Data from Excel in C#

Question

I have an excel file with data as follows
ID Name Contact No Address
1 ABC XXXXXXX ABCNM
2 XYZ xxxxxxx ASDFG

Now my requirement is read data from this excel sheet and den encrypt Contact No field for each row and den save data in database,how should it will be done I have to use some ready made available encryption algo for that. Inserting the whole data from excel to database is done bt how to encrypt only Contact No part is a problem. So plz help regarding that.

Vulpes · Answer

If the data has already been transferred from Excel to the database, couldn't you just write a SELECT query to read back the ID and Contact No columns for each record, encrypt the latter and then use UPDATE statements to write the encrypted Contact No's back to the database?

Vulpes · Answer

Vithal's code is basically doing two things: 1. It's reading the Excel file, as if it were a database table, into a DataSet with a single DataTable. 2. Having mapped the columns in the DataTable to the corresponding columns in the database table, it's then using SqlBulkCopy to copy the data to the database. All my code is doing is encrypting the 'Contact No' column of the DataTable after step 1. There's no reason why step 2 shouldn't work as it did before except now it will write the encrypted column to the database.

Aman Singh · Answer

@Vulpes; How to perform update operation by reading data from excel file, consider in same example as I have to update the contact No field by reading from excel file in that case sqlbulkcopy cant be used, den ho to do update in this type of scenario.

Vulpes · Answer

OK, you're going to need to edit the DataTable here before you can use SqlBulkCopy so the part of Vithal's code to focus on is this line in the InsertExcelRecords method: DataTable Exceldt = ds.Tables[0]; Suppose the method which you use to do the encryption is called Encrypt, takes a single string parameter and returns the encrypted string. Then just insert this code immediately after the above line: foreach(DataRow row in Exceldt.Rows) { string contactNo = row["Contact No"].ToString(); row["Contact No"] = Encrypt(contactNo); } You can then use the rest of Vithal's code 'as is'. Incidentally, I'm assuming here that the code will pick up the name of the DataColumn (i.e. 'Contact No') from the heading in the Excel file. If it doesn't, then you'll have to use the default name (probably 'Column3') instead in the above code.

Aman Singh · Answer

@Vulpes; I am using code given at this link to insert in DB, http://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-excel-file-records-into-sql-server-database-using/ given by Vithal Wadje for inserting data from excel. but now I have to insert after encryption, so if u have any idea about dis den plz help. And I have to use SQL Server as database.

Vulpes · Answer

What code have you used to read the Excel file and what type of database do you want to save it to after encryption?

Munesh Sharma · Answer

http://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-excel-file-records-into-sql-server-database-using/ related link http://dotnet-munesh.blogspot.in/2014/05/how-to-import-excel-file-to.html http://dotnet-munesh.blogspot.in/2014/05/create-excelxls-and-xlsx-file-from-c.html

Aman Singh · Answer

@Vulpes: actually I have to encrypt contact no data for each employee before inserting to database not after insert. So can u help hw dis will be achieved.