使用vbscript将txt文件从Web窗体导入到sqlserver数据库 [英] Import txt file from web form to sqlserver database using vbscript

问题描述

我被要求构建一个web应用程序来报告存储在另一个系统中的信息。其他系统被锁定，但将允许我将数据导出为csv文件。

I have been asked to build a web application to report on information stored in another system. The other system is locked down but will allow me to export data as a csv file.

我想在我的应用程序上使用html表单，以便人们（夜班！）可以将数据从其他系统导入我的Web应用程序。

I'd like to use an html form on my application so that people (the night shift!) can import data from the other system to my web application.

允许其他人了解我的代码我一直在使用vbscript并试图在所有数据库操作中使用以下模式：

To allow other people to understand my code I've been using vbscript and trying to use the following pattern in all database operations:

打开连接
生成查询
执行查询
执行具有结果的操作（如果合适）
关闭连接

Open Connection Build Query Execute Query Do something with results (if appropriate) Close Connection

是否可以使用此模式导入txt文件？即。纯粹基于sql而不使用存储过程？

Is it possible to use this pattern to import a txt file? ie. purely sql based without using store procedures?

有没有人有代码示例？

Does anyone have a code example?

希望有意义。

谢谢。

Derek

推荐答案

有些笔记。

Set cn = CreateObject("ADODB.Connection")
''SQL Server Express and ODBC, more connection strings: 
''http://www.connectionstrings.com/sql-server-2008
''
strcon = "ODBC;Description=Test;DRIVER=SQL Server;SERVER=Server\SQLEXPRESS;"
strcon = strcon & "Trusted_Connection=Yes;DATABASE=Test"

cn.Open strcon
strSQL = "SELECT * INTO NewCSV "
strSQL = strSQL & "FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',"
strSQL = strSQL & "'Text;HDR=Yes;FMT=Delimited;DATABASE=c:\docs\', "
strSQL = strSQL & "'SELECT * FROM [Test.csv]');"

cn.Execute strSQL, RecordsAffected
MsgBox RecordsAffected

可能必须启用即席查询： http://technet.microsoft.com/en -us / library / ms187569.aspx
也可以对文本使用ACE提供程序，但它可能变得复杂： http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/ace

You may have to enable ad hoc queries: http://technet.microsoft.com/en-us/library/ms187569.aspx It is also possible to use the ACE provider for text, but it may get complicated: http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/ace

CSV中的列中的混合数据可能有问题。

Mixed data in columns can be a problem with CSV. IMEX can help, but only if the range checked, which is set in the registry, is suitable.

修改注释

有关查看CSV数据的一些注意事项：

Some notes on viewing CSV data:

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Docs\;"
strcon = strcon & "Extended Properties=""Text;FMT=Delimited;HDR=Yes;IMEX=1"";"

cn.Open strcon

strSQL = "Select * From [Test.csv]"

rs.Open strSQL, cn

MsgBox rs.GetString

这篇关于使用vbscript将txt文件从Web窗体导入到sqlserver数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！