CODEWITHSUNDEEP
sqlsql-serversql-server-2008asp-classicvbscript
chris
chris

Reputation: 605

What is the best way to process and import a large csv (500k records) to SQL server using Vbscript?

I have a system that requires a large amount of names and email addresses (two fields only) to be imported via CSV upload.

I can deal with the upload easily enough, how would I verify the email addresses before I process the import.

Also how could I process this quickly or as a background process without requiring the user to watch a script churning away?

Using Classic ASP / SQL server 2008.

Please no jibes at the classic asp.

Upvotes: 3

Views: 627

Answers (2)

webaware
webaware

Reputation: 2841

In a former life, I used to do this sort of thing by dragging the file into a working table using DTS and then working that over using batches of SQL commands. Today, you'd use Integration Services.

This allows you to get the data into SQL Server very quickly, and prevent the script timing out, then you can use whatever method you prefer (e.g. AJAX-driven batches, redirection-driven batches, etc.) to work over discreet chunks of the data, or schedule it to run as a single batch (an SQL Server job) and just report on the results.

You might be lucky enough to get your 500K rows processed in a single batch by your upload script, but I wouldn't chance it.

Upvotes: 0

Cedar Bristol
Cedar Bristol

Reputation: 119

Do you need to do this upload via the ASP application? If not, whatever kind of scripting language you feel most comfortable with, and can do this with the least coding time is the best tool for the job. If you need for users to be able to upload into the classic ASP app and have a reliable process to insert the valid records into the database and reject the invalid ones, your options change.

Do you need to provide feedback to the users? Like telling them exactly which rows were invalid?

If that second scenario is what you're dealing with, I would have the asp app simply store the file, and have another process, a .net service, or scheduled task or something, do the importing and report on its progress in a text file which the asp app can check. That brings you back to doing it in whatever scripting language you are comfortable with, and you don't have to deal with the http request timing out.

If you google "regex valid email" you can find a variety of regular expressions out there for identifying invalid email addresses.

Upvotes: 2

Related Questions