将 SQL Server 表转储为 CSV 的 VB 脚本 [英] VB Script to dump an SQL Server table to CSV

问题描述

我尝试制作一个 VB 脚本来将 SQL 服务器表转储到 CSV 文件

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

一切正常，但由于我的某些 SQL 表字段为 NULL 或空，我收到此错误:

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?

谢谢！

顺便说一句......，让它像这样工作:

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, this helped me out with a similar requirement, so I'm sharing what I did.

我对您的代码进行了一些更改:

I made some changes to your code:

1. 使用 VB.NET - 看起来您的代码是 VB6，但无论如何您可能会发现这很有用.
2. 解决了空值问题.
3. 插入了包含列名称的选项.
4. 使引号仅适用于文本列.您可以注释掉 IF 或修改条件.

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

这篇关于将 SQL Server 表转储为 CSV 的 VB 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！