Import Data From Excel

Jagjit Saini

4y1.1kLearn iOS Programming

I want to import data from excel into Sql.

I have 3 fields - PersonId , Name , Salary. I want PersonId should not be duplicate. How we can do this

Thanks

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari4y ago
Perform the following steps to achive this:
1. Create a temporary table to import data from Excel
CREATE TABLE UploadExcelData
(
   PersonId INT NOT NULL,
   Name varchar(500),
   Salary INT
)
2. Import your data in Excel to your SQL into UploadExcelData table
3. Check if data exists in your main table, if not insert into your mail with below query
CREATE TEMPORARY TABLE #matchingRecords (PersonId INT, Name varchar(500), Salary INT)

INSERT INTO #matchingRecords
select u.PersonId, u.Name, u.Salary
from main_table m
INNER JOIN UploadExcelData u on u.PersonId = m.PersonId
                       and u.Name = m.Name
                       and u.Salary = m.Salary

INSERT INTO main_table (PersonId, Name, Salary)
select PersonId, Name, Salary
FROM UploadExcelData
WHERE ID NOT IN (SELECT PersonId FROM #matchingRecords)
4. Now, delete/drop UploadExcelData table because it's not needed now.
+1
Jignesh Kumar4y ago
Hi,

you can remove duplicate from your datatable using this,
1. DataTable distinctTable = dtPersion.DefaultView.ToTable(true, "PersonId");
+1
Jagjit Saini4y ago
Hi

I am able to import data from excel first by putting in Datatable. Then after looping datatable i am inserting record in table.
Here i want to check that PersonId should not be existing in table. PersonId is normal fields . It willbe user entered field.

Thanks
+1
Jignesh Kumar4y ago
Hi, Refer this thread, https://www.c-sharpcorner.com/article/how-to-import-data-from-excel-data-into-sql-table/
+1
Leon D4y ago
You can use Free Spire.XLS to import data from excel to database.
https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Excel-Import/Export-Import-Data-from-Excel-to-Datatable.html
+1
Srinivasan Ramamoorthi4y ago
You can use sql server export import wizard to do it. Simple copy paste also will work in table edit mode
+1
Satya Karki4y ago
For Excel data import you can use EP PLUS. You can check the below thread:

https://www.c-sharpcorner.com/forums/how-to-get-the-last-nonempty-row-number-using-epplus-amp-c-sharp
+1
Satya Karki4y ago
Hi, to make unique PersonId, you can achieve in two ways:
1. make your PersonId field auto increment in your sql table. it handles automatically you don't need to provide value.
2. Can do everytime before inserting data
New PersonId= GetMaximum PersonId from your table +1
+1
Md Sarfaraj4y ago
Hello Jagjit,
Please check out the below links, maybe that will help you.

Link1: https://www.c-sharpcorner.com/article/how-to-import-excel-data-into-sql-server-2019/
Link2: https://stackoverflow.com/questions/39610133/how-can-i-import-an-excel-file-into-sql-server
+1