ADO RECORDSETS

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

ADO RECORDSETS

Logged in as: Guest

arrSession:exec spGetSession 2,2,31807

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> ADO RECORDSETS
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

ADO RECORDSETS - 3/2/2006 11:05:17 PM

vbs2day

Posts: 13
Score: 0
Joined: 2/15/2006
Status: offline

I have a table in SQL2K database with 6 fields and 85 records. I am using the getstring function from ADO to retrive the data which gets and displays the table very fast. I would like to output the file to an excel spreadsheet as a .csv file. My problem is formatting. When I get the file to write it puts all the data a column A. I would like for 1st row of data to have 6 columns of data, then vbCR next record and so on till EOF.

How does one accomplish this?

vbs2day

Post #: 1

RE: ADO RECORDSETS - 3/2/2006 11:53:49 PM

ginolard

Posts: 1082
Score: 21
Joined: 8/10/2005
Status: offline

    It would be easier if you posted some code but if I understand correctly you just wish to read each record and have that record as a single line in a CSV file? That's easy enough. Here's a rough example - I've not included the SQL connection code as I presume you aready have this

   Set rs=CreateObject("ADODB.recordset")
   rs.CursorLocation=3 ' adUseClient
   rs.CursorType=1     ' adOpenKeyset
   rs.LockType=3         ' adLockOptimistic
   rs.Open "Select * from table, conn

   Do Until RS.Eof
   strcsv=strcsv & rs(0) & "," & rs(1) & "," & rs(2) & "," & rs(3) & "," & rs(4) & "," & rs(5) & vbcrlf
   rs.MoveNext
   Loop

Then write strcsv to a file.

_____________________________

Author of ManagePC - http://managepc.net
AD Query Template - http://www.visualbasicscript.com/m_40609/tm.htm
Consolidated Scripting Framework - http://www.visualbasicscript.com/m_59109/tm.htm

(in reply to vbs2day)

Post #: 2

RE: ADO RECORDSETS - 3/3/2006 12:38:38 AM

vbs2day

Posts: 13
Score: 0
Joined: 2/15/2006
Status: offline

Thank You--

I'll post code next week when I get back to my office. In your code, how does each field know to tab so that you have individual columns?

(in reply to vbs2day)

Post #: 3

RE: ADO RECORDSETS - 3/3/2006 12:42:01 AM

ginolard

Posts: 1082
Score: 21
Joined: 8/10/2005
Status: offline

Actually, I think that code is wrong so don't try it yet,.

Post your code (plus an example of the SQL table structure) and I'll take a look

_____________________________

Author of ManagePC - http://managepc.net
AD Query Template - http://www.visualbasicscript.com/m_40609/tm.htm
Consolidated Scripting Framework - http://www.visualbasicscript.com/m_59109/tm.htm

(in reply to vbs2day)

Post #: 4

RE: ADO RECORDSETS - 3/3/2006 1:44:28 AM

Country73

Posts: 735
Score: 10
Status: offline

Just wanted to add a little information to what ginolard has posted.
When writing to a CSV file the "," is what is used to tab to the next cell.

(in reply to vbs2day)

Post #: 5

RE: ADO RECORDSETS - 3/3/2006 1:48:36 AM

vbs2day

Posts: 13
Score: 0
Joined: 2/15/2006
Status: offline

Ok-- Next Monday Night

(in reply to Country73)

Post #: 6

RE: ADO RECORDSETS - 3/6/2006 9:38:06 AM

vbs2day

Posts: 13
Score: 0
Joined: 2/15/2006
Status: offline

Thanks Ginolard!

I have an SQL SP that creates the table below. I then query it via an SQL-VIEW from a vbs file which includes your code. It worked great! Thank You very much as this has helped me build a csv file very quickly.

Is there a way to spit the TimeDate field out in the spreadsheet as: Time, Date so they are sparate?

Dim cnn
Dim rst
Dim fld
Dim strHeaders
Dim strResults
Dim strConnect
Dim txtSQL

Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
Set fld = CreateObject("ADODB.Recordset")
Set fs = CreateObject("Scripting.FileSystemObject")
Set TestFile = fs.CreateTextFile("c:\fileoutput\.csv", True)

strConnect="DSN=;UID=;PWD="
txtSQL="Select * FRom "

   ' Open Connection and load Recordset
      cnn.Open strConnect

   ' Open recordset
      rst.Open CStr(txtSQL), cnn, adOpenForwardOnly

   ' Create column headers
      For Each fld In rst.Fields
      strHeaders = strHeaders & UCase(fld.Name)

   Next

   ' Use the GetString method to retrieve recordset text
     strResults = rst.GetString

   ' Return header and data to results pane
     txtResults = strHeaders & vbCrLf & strResults
     trim txtResults
     Wscript.Echo txtResults
     TestFile.WriteLine strResults


   ' If there was an error then replace the output
   ' text with the description of the error.
   If Err.Number > 0 Then txtResults = Err.Description


   ' Clean up
   rst.Close
   Set rst = Nothing
   cnn.Close
   Set cnn = Nothing

Data_ID DateTime WindSpeed_S_WVT WD_D1_WVT WD_SD1_WVT Battery_AVG
32 3/2/2006 6:32:00 PM 4.359 344.7 9.94 12.52
32 3/2/2006 6:32:00 PM 4.359 344.7 9.94 12.52
32 3/2/2006 6:32:00 PM 4.359 344.7 9.94 12.52
32 3/2/2006 6:32:00 PM 4.359 344.7 9.94 12.52
32 3/2/2006 6:32:00 PM 4.359 344.7 9.94 12.52
33 3/2/2006 6:32:00 PM 5.937 341.1 17.94 12.46
33 3/2/2006 6:32:00 PM 5.937 341.1 17.94 12.46
33 3/2/2006 6:32:00 PM 5.937 341.1 17.94 12.46
33 3/2/2006 6:32:00 PM 5.937 341.1 17.94 12.46
33 3/2/2006 6:32:00 PM 5.937 341.1 17.94 12.46
34 3/2/2006 6:32:00 PM 5.473 1.184 12.41 12.48
34 3/2/2006 6:32:00 PM 5.473 1.184 12.41 12.48
34 3/2/2006 6:32:00 PM 5.473 1.184 12.41 12.48
34 3/2/2006 6:32:00 PM 5.473 1.184 12.41 12.48
34 3/2/2006 6:32:00 PM 5.473 1.184 12.41 12.48
35 3/2/2006 6:32:00 PM 2.167 311.2 14.23 12.61
35 3/2/2006 6:32:00 PM 2.167 311.2 14.23 12.61
35 3/2/2006 6:32:00 PM 2.167 311.2 14.23 12.61
35 3/2/2006 6:32:00 PM 2.167 311.2 14.23 12.61
35 3/2/2006 6:32:00 PM 2.167 311.2 14.23 12.61
36 3/2/2006 6:32:00 PM 1.761 345.7 22.36 12.58
36 3/2/2006 6:32:00 PM 1.761 345.7 22.36 12.58
36 3/2/2006 6:32:00 PM 1.761 345.7 22.36 12.58
36 3/2/2006 6:32:00 PM 1.761 345.7 22.36 12.58
36 3/2/2006 6:32:00 PM 1.761 345.7 22.36 12.58
36 3/2/2006 6:32:00 PM 1.761 345.7 22.36 12.58
37 3/2/2006 6:33:00 PM 3.593 346.5 13.42 12.37
37 3/2/2006 6:33:00 PM 3.593 346.5 13.42 12.37
37 3/2/2006 6:33:00 PM 3.593 346.5 13.42 12.37
37 3/2/2006 6:33:00 PM 3.593 346.5 13.42 12.37
37 3/2/2006 6:33:00 PM 3.593 346.5 13.42 12.37
38 3/2/2006 6:32:00 PM 5.485 5.467 13.73 12.38
38 3/2/2006 6:32:00 PM 5.485 5.467 13.73 12.38
38 3/2/2006 6:32:00 PM 5.485 5.467 13.73 12.38
38 3/2/2006 6:32:00 PM 5.485 5.467 13.73 12.38
38 3/2/2006 6:32:00 PM 5.485 5.467 13.73 12.38
39 3/2/2006 6:32:00 PM 5.677 348.9 11.27 12.39
39 3/2/2006 6:32:00 PM 5.677 348.9 11.27 12.39
39 3/2/2006 6:32:00 PM 5.677 348.9 11.27 12.39
39 3/2/2006 6:32:00 PM 5.677 348.9 11.27 12.39
39 3/2/2006 6:32:00 PM 5.677 348.9 11.27 12.39

(in reply to vbs2day)

Post #: 7

RE: ADO RECORDSETS - 3/6/2006 6:00:10 PM

ginolard

Posts: 1082
Score: 21
Joined: 8/10/2005
Status: offline

Yes. There are two ways really. You can either use the Split function to put them into an array or, alternatively, use string manipulation like Mid and Left to extract the two parts.

_____________________________

Author of ManagePC - http://managepc.net
AD Query Template - http://www.visualbasicscript.com/m_40609/tm.htm
Consolidated Scripting Framework - http://www.visualbasicscript.com/m_59109/tm.htm

(in reply to vbs2day)

Post #: 8

RE: ADO RECORDSETS - 3/7/2006 8:59:46 AM

vbs2day

Posts: 13
Score: 0
Joined: 2/15/2006
Status: offline

When you say read into an array, would I have to do this when reading the recordset. I am a beginner. When you say string manipulation? What is the easiest way or is there?

(in reply to ginolard)

Post #: 9

RE: ADO RECORDSETS - 3/7/2006 7:54:27 PM

ginolard

Posts: 1082
Score: 21
Joined: 8/10/2005
Status: offline

Ok, let's explain the two methods. For the sake of argument, I will assume that the date/time field is RS(1)

SPLIT METHOD

DateTimeArray=Split(RS(1)," ")
Wscript.echo DateTimeArray(0)
WScript.echo DateTimeArray(1) & " " & DateTimeArray(2)

STRING MANIPULATION METHOD

Wscript.echo Left(RS(1),InStr(mydate," "))
WScript.echo Trim(Mid(RS(1),InStr(mydate," ")))

_____________________________

Author of ManagePC - http://managepc.net
AD Query Template - http://www.visualbasicscript.com/m_40609/tm.htm
Consolidated Scripting Framework - http://www.visualbasicscript.com/m_59109/tm.htm

(in reply to vbs2day)

Post #: 10

RE: ADO RECORDSETS - 3/8/2006 8:30:27 AM

vbs2day

Posts: 13
Score: 0
Joined: 2/15/2006
Status: offline

Ok--

I see how the function now works. Where do I put into the code. Do I put function in the string line and If so do I need to add another element to account for both Date & Time?

vbs2day

(in reply to ginolard)