使用 Vbscript 处理大型 csv(500k 记录)并将其导入 SQL 服务器的最佳方法是什么? [英] 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.

如果您在 Google 上搜索正则表达式有效电子邮件"，您可以找到各种用于识别无效电子邮件地址的正则表达式.

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 服务器的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！