VB Script to dump an SQL Server table to CSV

Question

I try to make a VB script to dump an SQL server table to a CSV file

Option Explicit
Dim rs,fieldVals,dbConnIn
Dim connectString,shell,tmp,fso,ts,line

Const adOpenDynamic=2
Const adLockPessimistic=2
Const adCmdTable=2
Const adOpenForwardOnly=0
Const adOpenStatic=3
Const adLockReadOnly=1

connectString="Provider=SQLOLEDB;Server=192.168.168.4;Database=MYDB;Uid=sa;Pwd=myPassword;"
Set dBConnIn = CreateObject("ADODB.Connection")
dBConnIn.CommandTimeout = 300
dBConnIn.Open connectString
' This is just a simple way of getting a record set from and SQL Query
Set rs=CreateObject("ADODB.RecordSet")
rs.Open _
   "TITULARES", _
   dbConnIn, _
   adOpenStatic, _
   adLockReadOnly, _
   adCmdTable

Set shell=CreateObject("WScript.Shell")
Set fso=CreateObject("Scripting.FileSystemObject")
Set ts=fso.OpenTextFile("E:\TITULARES.csv",2,TRUE)
line=""
For Each tmp In rs.Fields
   line=line & tmp.Name & ","
Next
ts.WriteLine Left(line,Len(line)-1)
While Not rs.EOF
 line=""
 For Each tmp In rs.Fields
  line=line & """" & Replace(tmp.Value,"""","""""") & ""","
 Next
 ts.WriteLine Left(line,Len(line)-1)
 rs.MoveNext
Wend

rs.Close
ts.close

All well, but because some of my SQL table fields are NULL or empty I'm getting this error:

d.vbs(39, 2) Microsoft VBScript runtime error: Invalid procedure call or argument: 'Left'

Any ideas on how to solve this?

Thanks!

BTW..., made it work like this:

Set dBConnIn = CreateObject("ADODB.Connection")
dBConnIn.CommandTimeout = 300
dBConnIn.Open connectString
' This is just a simple way of getting a record set from and SQL Query
Set rs=CreateObject("ADODB.RecordSet")
rs.Open _
   "TITULARES", _
   dbConnIn, _
   adOpenStatic, _
   adLockReadOnly, _
   adCmdTable

Set shell=CreateObject("WScript.Shell")
Set fso=CreateObject("Scripting.FileSystemObject")
Set ts=fso.OpenTextFile("E:\TITULARES.csv",2,TRUE)
line=""
For Each tmp In rs.Fields
   line=line & tmp.Name & ","
Next
ts.WriteLine Left(line,Len(line)-1)
While Not rs.EOF
 line=""
 For Each tmp In rs.Fields
  If IsNull(tmp.Value) Then
   line=line & """" & Replace(tmp.Value,"""","""""") & ""","
  Else
   line=line & """" & tmp.Value & ""","
  End If
 Next
 ts.WriteLine Left(line,Len(line)-1)
 rs.MoveNext
Wend

rs.Close
ts.close

Thanks all!

Answer 1

Thanks, this helped me out with a similar requirement, so I'm sharing what I did.

I made some changes to your code:

1. Using VB.NET - looks like your code is VB6 but you might find this useful anyway.
2. Addressed the issue with null values.
3. Inserted the option to include column names.
4. Made the quotes conditional only for text columns. You can remark out that IF or modify the conditions.

Imports System.StringComparison
Imports Microsoft.VisualBasic.Strings

Private Function ExportToCSV(ByVal stTable As String, ByVal stFile As String, ByVal bColumnNames As Boolean)

    Dim shell, tmp, fso, ts, line

    Dim Conn As ADODB.Connection = New ADODB.Connection
    Dim RS As ADODB.Recordset = New ADODB.Recordset
    Dim connectstring As String = My.Settings.connSQL 'your connection string here
    Conn.ConnectionString = My.Settings.connSQL
    Conn.Open()
    RS.ActiveConnection = Conn
    RS.Open("Select * from " & stTable, Conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)

    shell = CreateObject("WScript.Shell")
    fso = CreateObject("Scripting.FileSystemObject")
    ts = fso.OpenTextFile(stFile, 2, True)
    line = ""
    If bColumnNames = True Then
        For Each tmp In RS.Fields
            line = line & tmp.Name & ","
        Next
        ts.WriteLine(Microsoft.VisualBasic.Strings.Left(line, Len(line) - 1))
    End If

    While Not RS.EOF
        line = ""
        For Each tmp In RS.Fields
            If IsDBNull(tmp.Value) Then
                line = line & ","
            Else
                If RS.Fields(tmp.name).Type = ADODB.DataTypeEnum.adVarChar Or RS.Fields(tmp.name).Type = ADODB.DataTypeEnum.adChar Or RS.Fields(tmp.name).Type = ADODB.DataTypeEnum.adVarWChar Or RS.Fields(tmp.name).Type = ADODB.DataTypeEnum.adWChar Then
                    line = line & """" & tmp.Value & ""","
                Else
                    line = line & tmp.value & ","
                End If
            End If
        Next
        ts.WriteLine(Microsoft.VisualBasic.Strings.Left(line, Len(line) - 1))
        RS.MoveNext()
    End While

    RS.Close()
    ts.close()
End Function

Answer 2

A small helper function to escape special characters

'http://en.wikipedia.org/wiki/Comma-separated_values#Basic_rules
Public Function EscapeCsvData(ByVal data As String) As String
    If (data.Contains("")) Then
        data = data.Replace("""", """""")
    End If

    If (data.Contains(",")) Then
        data = String.Format("""{0}""", data)
    End If

    If (data.Contains(System.Environment.NewLine)) Then
        data = String.Format("""{0}""", data)
    End If

    Return data
End Function

Answer 3

There are better ways to export SQL Server tables to .CSV: see here or here; think of SELECT/INSERT INTO", .GetRows(), or .GetString().

A solution with minimal impact on your code is using a function like:

Function SaveValue(oFld)
  If IsNull(oFld.Value) Then
     SaveValue = "" ' or whatever you like
  Else
     SaveValue = oFld.Value
  End If
End Function