使用Vbscript处理和导入大型csv(500k条记录)到SQL Server的最佳方法是什么? [英] What is the best way to process and import a large csv (500k records) to SQL server using Vbscript?

问题描述

我有一个系统，需要通过CSV上传导入大量的名称和电子邮件地址(仅两个字段).

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?

使用经典ASP/SQL Server 2008.

Using Classic ASP / SQL server 2008.

请不要嘲笑经典的asp.

Please no jibes at the classic asp.

推荐答案

是否需要通过ASP应用程序执行此上传?如果不是这样，那么您最喜欢的一种脚本语言，并且可以用最少的编码时间来完成，是完成这项工作的最佳工具.如果您需要用户能够上载到经典ASP应用程序中并且具有可靠的过程将有效记录插入数据库并拒绝无效记录，则您的选择会更改.

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?

如果第二种情况是您要处理的，我将让asp应用程序简单地存储文件，并让另一个进程，.net服务或计划任务或其他任务执行导入并报告其进度在一个文本文件中，asp应用程序可以检查.这样一来，您便可以使用自己喜欢的任何脚本语言重新进行操作，而不必处理http请求超时的问题.

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.

这篇关于使用Vbscript处理和导入大型csv(500k条记录)到SQL Server的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！