Solved

How to properly perform a sql select into a csv file with VBscript

Posted on 2015-02-18
6
193 Views
Last Modified: 2015-02-19
Hello,

I need to perform to query out a csv file through Vbscript however the query select more than 2000 rows and the following code is not able to extract so many rows into a csv.

Could someone provide a Vbscript to make quickly a query out of large amount of rows or optimize the following code and include the headers.

On Error Resume Next

Dim data
Dim recordCount
Dim regEx

Set regEx = new RegExp
regEx.Pattern = "\r|\n|,|"""

Set con = CreateObject("ADODB.Connection")
con.Open "Driver={SQL Server};" & _
         "Server=;" & _
         "UID=;" & _
         "PWD=;" & _
         "Database=;"

wscript.echo "CONNECTION OK"

strQry = "SELECT * FROM Projects"

Set data = con.execute(strQry)

Set filsSysObj = CreateObject("Scripting.FileSystemObject")

If filsSysObj.FileExists("C:\test\CSVFile2.csv") Then filsSysObj.DeleteFile "C:\test\CSVFile2.csv"

Set csvFile = filsSysObj.OpenTextFile("C:\test\CSVFile2.csv", 8, True)

recordCount = data.Fields.Count

Do until data.EOF
    Separator = ""

    For i = 0 To data.Fields.Count - 1
        Column = data.Fields( i ).Value & ""

        If regEx.Test( Column ) Then
            Column = """" & Replace( Column, """", """""" ) & """"
        End If

        csvFile.Write Separator & Column
        Separator = ";"
    Next

    csvFile.Write vbNewLine
    data.MoveNext
Loop

wscript.echo "Done"

Open in new window

0
Comment
Question by:LD16
  • 3
  • 2
6 Comments
 
LVL 53

Assisted Solution

by:Bill Prew
Bill Prew earned 250 total points
ID: 40616393
Can't test this easily here, but here are some thoughts.  Try using an actual recordset object for the results, that may help.

Also, I tried to add the logic to write out the comumn names as a header line in the CSV, may need a little tweeking.

On Error Resume Next

Dim data
Dim recordCount
Dim regEx

Set regEx = new RegExp
regEx.Pattern = "\r|\n|,|"""

Set con = CreateObject("ADODB.Connection")
con.Open "Driver={SQL Server};" & _
         "Server=;" & _
         "UID=;" & _
         "PWD=;" & _
         "Database=;"

wscript.echo "CONNECTION OK"

strQry = "SELECT * FROM Projects"

Set data = CreateObject("ADODB.Recordset")
Set data.open strQry, con

Set filsSysObj = CreateObject("Scripting.FileSystemObject")
If filsSysObj.FileExists("C:\test\CSVFile2.csv") Then filsSysObj.DeleteFile "C:\test\CSVFile2.csv"
Set csvFile = filsSysObj.OpenTextFile("C:\test\CSVFile2.csv", 8, True)

recordCount = data.Fields.Count

' Write column headers
hdr = ""
Separator = ""
for i = 0 To data.Fields.Count - 1
    hdr = Separator & """" & data.Fields( i ).Name & """"
    Separator = ";"
Next
csvFile.WriteLine hdr

Do until data.EOF
    Separator = ""

    For i = 0 To data.Fields.Count - 1
        Column = data.Fields( i ).Value & ""

        If regEx.Test( Column ) Then
            Column = """" & Replace( Column, """", """""" ) & """"
        End If

        csvFile.Write Separator & Column
        Separator = ";"
    Next

    csvFile.Write vbNewLine
    data.MoveNext
Loop

csvFile.Close
data.Close
con.Close

wscript.echo "Done"

Open in new window

~bp
0
 

Author Comment

by:LD16
ID: 40616981
Hello Bill,

I have the same problem, I think this code suit just for filtered sql request.
Can we contemplate the option of powershell or bat?

Regards,
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 250 total points
ID: 40618233
Hi, I haven't tested this at all, but can you see what it does for you?

Regards,

Rob.

Dim data
Dim recordCount
Dim con
Dim strQuery
Dim filsSysObj
Dim csvFile
Dim strHeader
Dim Separator
Dim i
Dim strLine
Dim Column

Set con = CreateObject("ADODB.Connection")
con.Open "Driver={SQL Server};" & _
         "Server=;" & _
         "UID=;" & _
         "PWD=;" & _
         "Database=;"

wscript.echo "CONNECTION OK"

strQry = "SELECT * FROM Projects"
Set data = con.execute(strQry)

Set filsSysObj = CreateObject("Scripting.FileSystemObject")
Set csvFile = filsSysObj.CreateTextFile("C:\test\CSVFile2.csv", True)

recordCount = data.Fields.Count

strHeader = ""
Separator = ";"
For Each objField In data.Fields
	If strHeader = "" Then
		strHeader = objField.Name
	Else
		strHeader = strHeader & Separator & Replace(Replace(Replace(Replace(Replace(objField.Name, vbCrLf, ""), vbCr, ""), vbLf, ""), ",", ""), """", "")
	End If
Next
csvFile.WriteLine strHeader

Do until data.EOF
	strLine = ""
    For i = 0 To data.Fields.Count - 1
        Column = Replace(Replace(Replace(Replace(Replace(data.Fields( i ).Value, vbCrLf, ""), vbCr, ""), vbLf, ""), ",", ""), """", "")
        If strLine = "" Then
        	strLine = Column
        Else
        	strLine = strLine & Separator & Column
        End If
    Next

    csvFile.WriteLine strLine
    data.MoveNext
Loop

wscript.echo "Done"

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:LD16
ID: 40618427
Hello Rob,

I got an error message on line 22 char 15.

Thank you in advance for your help.
0
 

Author Comment

by:LD16
ID: 40618434
My mistake, the last code works perfectly.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 40618442
Great. Thanks for the grade. I'm not entirely sure of the difference but I think your Write calls must have been confusing the script, or using too many resources. I prefer to get data as a full line before writing it.

Regards,

Rob.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Novice question on VBScript 8 61
Script is being strange 8 70
Protecting vb6 & .Net code Obfuscation 18 126
VBScript Issues 8 35
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question