0

-Hello, World!-

I have an excel file that has data in it that I need to store in a database.

Quick background on this part of the application. A user can download an Excel document with a single worksheet that has a table with headers. They can then paste in the data they have to the template, and upload the document to the website. From there, I need to get it into database.

The problem is that I am unsure how to process the data to get it into the proper form to insert it into the database. I am aware this question has been asked numerous times. However, every example I have found requires the developer to have one of the following conditions: Convert it to a CSV (it must be in an Excel document), create a model to wrap the data in (it can be for any table, and each table can be added dynamically), or upload the Excel document to SQL Server and have it processed there (it must be capable of running on seperate servers).

My question is, what is the best way to get the data from the Excel document, into the database? My first thought is to just create a ton of INSERT statements, but I feel like there must be a faster way than that. Alternatively, I could use System.Data.SqlClient.SqlBulkCopy, but I am unsure if that will work for DB2 and Oracle as well as SQLServer, and I am also worried about performance problems that come from having to load all the data out of the Excel document into memory first.

Does anyone have any insight that might help me out?

Thanks!

Edit/Clarification: The code necessary to load things from the Excel document and generate INSERT statements is fairly straightforward and doesn't even require a lot of effort, especially since I am using EPPlus to get at the data itself. My concern is primarily that I stay within the C#/ASP.NET environment, and that the code I execute is up to industry standards in regards to performance.

Michael Ziluck
  • 599
  • 6
  • 19
  • It is very simple code, and I am more than happy to write it. My concern is more about the fact that I want to make sure that it is 1) Still within the C# and ASP.NET environment, and 2) Up to industry standards when it comes to performance. – Michael Ziluck Jul 10 '18 at 19:32
  • there is no industry standard when writing your own xcel parser I would suggest you try some of the links on stackoverflow I have written several excel parsers excel to datatable for example or create a class that is of the same structure / layout of your excel columns. there are many options you can utilize here.. you just need to pick one that best fits your use case.. – MethodMan Jul 10 '18 at 19:58

1 Answers1

1

The following link will show how you can embed an SSIS package within your C# code. https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-dotnet?view=sql-server-2017 This will allow you to make any transformations with SSIS, and then execute the package from C#.
Other than this, you can do all of this in c# reading in row by row with corresponding Insert statements into the database if you are concerned that the datasets may vary too much to load into memory. Also, here is a good link for using SQLBulkCopy with a code sample: Insert 2 million rows into SQL Server quickly

StevenJe
  • 82
  • 2
  • 14
  • Hmm. Okay, thanks! My company has and uses SSIS, so I can talk to my boss about getting it integrated into the application. I'm going to give an upvote but not mark this as the accepted answer in case someone else knows of a more streamline method. Cheers! – Michael Ziluck Jul 10 '18 at 20:09