ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,397 questions
How would you check an Excel columns in order to compare with a data in the database table?
Cenk
991
Reputation points
Hello folks,
I am working on a Blazor Server application. I would like to upload an excel file, consisting of a couple of columns. (ReferenceId-guid, Order Date-datetime, Product Code-string, and Product Description-string) This excel file roughly has 200000 rows of data.
I am gonna use EPPlus non-commercial package. I will compare the referenceId's from the excel with the database but there might be duplicate referenceId's. So I am somehow grouping those referenceId's in excel and then iterating it comparing them with the database records.
One last requirement is to write down both matching and nonmatching referenceId's in an excel file. I think when comparing the referenceId's adding those values into two separate lists (matching, non-matching) and then writing those records inside of those lists in excel with EPPlus will solve my problem.
Can I have your comments about this solution? Can it be improved?
Thank you.
ASP.NET Core
Blazor
Blazor
A free and open-source web framework that enables developers to create web apps using C# and HTML being developed by Microsoft.
1,499 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,687 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,640 questions
{count} votes
-
AgaveJoe 27,686 Reputation points2023-01-11T12:28:03.64+00:00 It seems to me this is a database question.
At a high level, you are either processing the data on a web server or in a database. In my opinion it is far more efficient to use database tools to process data. I recommend moving the data processing to the database rather than moving the data to the web server and processing the data on the web server in C#.
-
Cenk 991 Reputation points
2023-01-12T05:32:50.03+00:00 So you are suggesting inserting the related excel data into a database table and then comparing them, right?
-
AgaveJoe 27,686 Reputation points
2023-01-12T10:59:52.81+00:00 So you are suggesting inserting the related excel data into a database table and then comparing them, right?
Not exactly. I recommend loading the Excel data into a database table. Use T-SQL to process the data and create an output. To get the data into the table use ADO.NET bulk copy or the BCP utility.
I assume your current approach must loop over 200,000 Excel row and makes 200,000 calls to the database or copy an unknow database table to the web server.
Keep in mind, you were given the same advice in a recent thread. Ultimately, it is up to you. Process the data on the web server if you are more comfortable with C#.
Sign in to comment