Thursday, December 31, 2009

VBScript ADO with XML and CSV Data Files

I was poking around to find a way to read CSV, TXT (various delimiters) through VBScript for use in scripted data migrations, as well as to render the data in ASP, and found some interesting things.  I have to give credit to [] for the XML example.  I only modified it slightly to fit in with how I tend to code ADO processes.  I also would like to applaud the efforts of Connection for their fantastic reference site for connection information for almost anything that can possibly store information.

Reading XML Data

Const dsn = "Provider=MSDAOSP;Data Source=MSXML2.DSOControl;"
Const adChapter = 136

Dim conn, rs
Dim iLevel : iLevel = 0

Set conn = CreateObject( "ADODB.Connection" )
Set rs = CreateObject( "ADODB.Recordset" )

conn.Open dsn

rs.Open CreateObject( "WScript.Shell" ).CurrentDirectory + _
"\test.xml", conn

WalkHier iLevel, rs


Sub WalkHier(ByVal iLevel, ByVal rs)
iLevel = iLevel + 1
Dim PriorLevel : PriorLevel = iLevel
Dim i, adoChildRS
While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
If rs.Fields(i).Name <> "$Text" Then
If rs.Fields(i).Type = adChapter Then
Set adoChildRS = rs.Fields(i).Value
WalkHier iLevel, adoChildRS
wscript.echo iLevel & ": rs.Fields(" & i & ") = " & _
rs.Fields(i).Name & " = " & rs.Fields(i).Value
End If
End If
iLevel = PriorLevel
End Sub

Reading CSV Data

dsn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='s:\scripts\';Extended Properties=""text;HDR=Yes;FMT=Delimited(,);"""

query = "SELECT * FROM test.csv"

Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")

conn.Open dsn

rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly

Set cmd.ActiveConnection = conn

cmd.CommandType = adCmdText
cmd.CommandText = query
rs.Open cmd

If Not(rs.BOF And rs.EOF) Then
Do Until rs.EOF
For i = 0 to rs.Fields.Count - 1
wscript.echo rs(i).name & " = " & rs(i).value
wscript.echo "error: no records found"
End If
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing

No comments:

Post a Comment