Click here to Skip to main content
12,075,177 members (67,605 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VBScript
I really appreciate if some one is willing to shade some light on this.

I wanted to write a function using VBScript (Qtp's preferred Lang )
Connect to a sql server using applicable credentials and run a Query,in which the result can be accessed through ADO DB object - recordset and later save to an csv file.

My biggest challenge is to create a .CSV file in C:\ drive and save the Query result to the CSV file. using vb script on QTP HP Tool.

code
*************************************************************************************************


Function RecordsetToCSV()

str_query = "SELECT top 20 A.BusinessID, A.dbaName, DM.Tier ,DM.Yield, DM.Frequency FROM T2Businesses A" _
& " LEFT OUTER JOIN TOptionList OL ON A.Status = OL.Value AND OL.ListName = 'DealerDeveloperStatus'" _
& " LEFT OUTER JOIN TDealerMetrics DM ON A.BusinessID = DM.BusinessID" _
& " LEFT OUTER JOIN TEntity E ON A.BusinessID = E.EntityID" _
& " WHERE E.isdeleted=0 and (A.IsVAR = 1 or (A.IsVAR = 1 OR A.IsPOSDeveloper = 1) or A.IsPOSDeveloper = )" _
& " and DM.Yield is not null order by A.dbaName desc"

Set objdbconnection = CreateObject("ADODB.Connection")
Set rsTemp = CreateObject("ADODB.Recordset")
strdbserver = "xyz.prod.qa.dev"
strdbuserId = "QTP"
strdbpwd = "P@ssword!"
strdbname = "dbm_database"

objdbconnection.ConnectionString = "DRIVER=SQL Server;SERVER=" & strdbserver & ";Initial Catalog=" & strdbname & " ;User Id=" & strdbuserId & ";Password=" & strdbpwd & ";"
objdbconnection.Open , ConnectionString
rsTemp.Open str_query, objdbconnection, adOpenStatic, adLockOptimistic

CSVData = DataToCSV(rsTemp)

Set fso = CreateObject("Scripting.FileSystemObject")
fso.Open "C:\MyFileName.csv" For Binary Access Write As #1
Put #1, , CSVData
Close #1

rsTemp.Close
Set rsTemp = Nothing

End Function




Public Function DataToCSV(rsData )
 
    If ShowColumnNames Then
        For K = 0 To rsData.Fields.Count - 1
            RetStr = RetStr & ",""" & rsData.Fields(K).Name & """"
        Next
 
        RetStr = Mid(RetStr, 2) & vbNewLine
    End If
 
    RetStr = RetStr & """" & rsData.GetString(adClipString, -1, """,""", """" & vbNewLine & """", NULLStr)
    RetStr = Left(RetStr, Len(RetStr) - 3)
 
    DataToCSV = RetStr
End Function





Thanks,
James.
Posted 6-Jul-12 12:18pm
(unknown member)
Edited 9-Jul-12 7:27am
v3
Comments
Wes Aday 6-Jul-12 17:27pm
   
I am afraid that you are going to have to be way more specific. What is it that you need help with? Writing a function in VB script? Writing script in Quick Test Pro? Connecting to a database? What database? What query? This is a very broad set of questions that probably cannot be easily answered in a forum posting.
ryanb31 11-Jul-12 15:10pm
   
So, what do you need help with?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160208.1 | Last Updated 9 Jul 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100