CODEWITHSUNDEEP
c#asp.netfile-uploadcsv
user710502
user710502

Reputation: 11469

Inserting large csv files into a database

We have an application on the web that must allow the user to upload files with zip codes, these files are .csv's files. Any user will be able to upload the file from their computer, the issue is that the file may contain thousands of records. Right now i am getting the file, making sure it has the right headers but I am pushing the records one by one into the database.

I am using c# asp.net, is there a better way to do this?, more efficient from the code?. We cant use any external importers or data importers or tools like sql server business intelligence. How can I do this?, i was reading something about putting it in memory and then push it to the database?. Any urls, examples or suggestions would be much appreciated.

Regards

Upvotes: 1

Views: 1405

Answers (3)

gudatcomputers
gudatcomputers

Reputation: 2882

Since these records are in the same table and would then not be related to each other, Parallel.ForEach may be a valid answer here. Assuming you have a static method (may not necessarily need to be static) that inserts an individual record into the db, you can run Parallel.ForEach loop over an array where each index of the array represents a line of the CSV.

This assumes that uploading the large file to the server isn't the initial issue. If that is also part of the issue I would reccomend zipping the file and then using something like SharpZipLib to unzip it once it is uploaded. Since text compresses very well this may be the biggest boon to performance from the user's perspective.

Upvotes: 1

Maxim Gershkovich
Maxim Gershkovich

Reputation: 47209

Firstly, I'm pretty sure that what you are asking is actually "How do you process a large file and insert the processed data into the database?".

Now assuming I am correct I would say the question is akin to 'how long is a piece of string?'. The reality is that an implementation for processing large files into a database is highly specific to your requirements.

However, at the simplest end of the spectrum you could simply upload the file straight into a table (or folder) and create a windows service that runs every x minutes, traverses through the table, picks each file and processes your data using bulk inserts and the prepare method (which may give you some performance benefits).

Alternatively you could look at something like MSMQ (Microsoft Message Queuing) and save any uploaded files direct to a queue which is then completely independent of your application and can be processed at any point in time along with easily scaled out.

At the end of the day though, honestly I don't think anyone here can give you a 'correct' answer to your question cause there really isn't one and you'll only be able to find improvements to your implementation by experimentation.

Upvotes: 1

Juvil
Juvil

Reputation: 490

if this contains up to a million record, best to do this is to create a service to manage the inserting of records into the database to avoid timeout and prevent the web iis stress.

if you make it a windows service you can notify the service to process the zip files in certain directory where it was uploaded.

also, i would suggest to use bulk insert for more faster database transactions.

if there are validation you can probably stage the data into a different database and validate the data then push to the final database.

Upvotes: 1

Related Questions