Import Txt File From Web Form To Sqlserver Database Using Vbscript

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. I'd

Solution 1:

Some notes.

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

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

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.

EDIT re comments

Some notes on viewing CSV data:

Setcn= CreateObject("ADODB.Connection")
Setrs= 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 strconstrSQL="Select * From [Test.csv]"

rs.Open strSQL, cn

MsgBox rs.GetString