0

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

user1931544
  • 43
  • 6
  • What do you mean by "lac"? – Palle Due Jun 29 '17 at 07:16
  • SQL Server includes SSIS since version 7 in the 1990s. Entity Framework has nothing at all to do with bulk loading anyway – Panagiotis Kanavos Jun 29 '17 at 08: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 Jun 29 '17 at 08: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](https://learn.microsoft.com/en-us/sql/integration-services/run-manage-packages-programmatically/loading-and-running-a-local-package-programmatically) – Panagiotis Kanavos Jun 29 '17 at 08: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 Jun 29 '17 at 10:37
  • You can define the mappings from the input DataTable/DataReader to the target columns. – Panagiotis Kanavos Jun 29 '17 at 14:22
  • @PanagiotisKanavos, Thank you i did the same. it works – user1931544 Jun 30 '17 at 12:06

1 Answers1

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.

Krishnan
  • 66
  • 10
  • @user1931544 You can specify destination table and column mapping to SQLBulkCopy https://stackoverflow.com/questions/17469349/mapping-columns-in-a-datatable-to-a-sql-table-with-sqlbulkcopy – Krishnan Jun 30 '17 at 11:44