Optimal ways to read data from excel and save in sql server DB c#

Question

What is the optimal way to read data from excel and update in sql server DB. Column name in excel is different from column name in DB.

am currently using Oledb drivers to read data from excel and ef 5.0 for db operations.

is there a better way of doing it?

excel contains close to 100 thousands rows

SQL Server includes SSIS since version 7 in the 1990s. Entity Framework has nothing at all to do with bulk loading anyway — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 29, 2017 at 8:05
@rshah4u the duplicate's answer is awful - install Excel on the server? Or install another ETL tool, when SQL Server already includes one? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 29, 2017 at 8:08
You can also use Import Wizard to load the data and save the SSIS package. That package can be scheduled to run using SQL Server agent even if the SSIS service isn't configured. In fact, you can load the package in your code and execute it, as shown in Loading and Running a Local Package Programmatically — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 29, 2017 at 8:13
@PanagiotisKanavos Sql bulks copy works when column name in excel is same as column name in Db table. Please correct me if am wrong. — user1931544
– user1931544, Commented Jun 29, 2017 at 10:37

Krishnan · Accepted Answer · 2017-06-29 07:58:29Z

0

There are many libraries available to read excel data like SpreadsheetGear, NPOI, OLE DB, etc. You can choose any.
I prefer OLE DB, you can interact with excel data in similar way you interact with database.

To store the excel data to database you can use SqlBulkCopy class.
You can store the data read from excel file in System.Data.DataTable and then use WriteToServer(DataTable) method.
Or if you have large excel file you can implement IDataReader interface and use method WriteToServer(IDataReader).
The WriteToServer(IDataReader) is optimal in case of memory utilization.

edited Jun 29, 2017 at 7:58

answered Jun 29, 2017 at 7:48

Krishnan

7610 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Krishnan Over a year ago

@user1931544 You can specify destination table and column mapping to SQLBulkCopy stackoverflow.com/questions/17469349/…

Collectives™ on Stack Overflow

Optimal ways to read data from excel and save in sql server DB c#

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related