Import txt file from web form to sqlserver database using vbscript

Question

Hi,

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 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.

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

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?

Hope that makes sense.

Thanks.

Derek

Answer 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:

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