SQL to text file

[Cloonalt]

Can I open a recordset in VB and send the results to a text file on a network drive?

Thanks.

 

[macleod1021]

Yup!

Guess ya wanna know how too

Open a StreamWriter Object
Loop through the recordset adding the entries to a string object
Write the string to the streamwriter object
close the streamwriter object

 

[Cloonalt]

Is Streamwiter from a third party?

Any more information than that? Any code?

 

[dilettante]

You can use the Recordset's .Save() method to dump the Recordset to disk in ADTG or XML formats, or into an ADO Stream object and call .SaveToFile() on that.

Also consider the Recordset's .GetString() and .GetRows() methods.

 

[macleod1021]

wait...I just realized this is the VB6 forum. I'm gonna have to dig through some of my old code to see how I did it before. If you wanted to upgrade to .Net I could give that code to you from memory . It'll be sometime tomorrow tho. It's late (actually early) and I really need some sleep

 

[cyoung]

Cloonalt,

Check out this thread: 222-645444

 

[CaptainD]

Here is some code I'm using to pull data from an SQL server and places it into a pipe delimited text file for export.

The program loops through a listbox and passes a value to a stored procedure for each selected person. It then appends the data to the text file giving me the final results.

Private Sub cmdExport_Click()
On Error GoTo ErrHandler
Dim rs As ADODB.Recordset
Dim strSql As String
Const SW_SHOWNORMAL = 1

'Clear the text file
Open ExportPath For Output As #1
Print #1, ""
Close #1
If lstPersonnel.SelCount = 0 Then 'Get all records
    iRow = 1
    For i = 0 To lstPersonnel.ListCount - 1

        strSql = "EXEC spCFD_CJAC_Export @dStartDate = '" & dtpStartDate.Value & _
        "', @dEndDate = '" & dtpEndDate.Value & "', @iCityIDNum = " _
        & lstPersonnel.ItemData(i)
        Set rs = GetADORSP(strSql)
    Open ExportPath For Append As #1
    Do While Not rs.eof
        Print #1, rs.GetString(, , "|", vbCrLf, "");
   
    Loop
    Close #1
    Set rs = Nothing
    Next i

 

[CaptainD]

By the way, "ExportPath" is a form level varaiable that holds the path to the file.
This clears the old data from the existing file.

Print #1, ""
Close #1

And the method for retrieving data from the stored procedure is not a recommended method if users have the ability to enter their own "text" type of information since it opens you up to "SQL Injection". In this case, there is no direct user input to what goes to the stored procedure so I used it.

 

[gmmastros]

CaptainD,

I was thinking of the same thing, then I read your post. There's something a little odd about it.

I see that you are looping through the recordset with a GetString call. Since you are not specifying the second parameter NumRows As Long = -1, all of the data will be returned during the first call to GetString. I'm fairly certain that you could remove the while loop and just print the results of the GetString function once to the file.

You could replace:
[tt][blue] Open ExportPath For Append As #1
Do While Not rs.eof
Print #1, rs.GetString(, , "|", vbCrLf, "")

Loop
Close #1
[/blue][/tt]

With:

    Open ExportPath For Append As #1
    Print #1, rs.GetString(, , "|", vbCrLf, "")
    Close #1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Set a reference to the Visual Basic Scripting object. Then:

'assuming an open recordset rs with 3 fields
dim fso as filesystemobject
dim ts as textstream
set fso = new filesystemobject
set ts = fso.opentextfile("somefilename")
with rs
    .movefirst
    do until .eof
        ts.writeline .fields(0) & ", " & .fields(1) & ", " & .fields(2)
        .movenext
    loop
    .close
end with
ts.close

Untested, but should be pretty close to correct if not entirely so.

HTH

Bob

 

[gmmastros]

Bob, I think you'll find that the GetString method is approx. twice as fast as yours. I tested this on a recordset with 3 fields and 5200 records.

Your method was relatively consistent with 0.14 seconds. The equivalent GetStrings method worked in 0.08 seconds.

Your method has the advantage of picking the fields to export and adjusting the order in which fields appear. This gives more control over the exported data, whereas the GetString method will export all the fields in the order in which they were selected. It is not uncommon to return more fields from the database than you actually use, so there are times when your approach would be better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[CaptainD]

gmmastros, I commented out the loop as you suggested and it throws Error "#3021 Either BOF or EOF is True"

With the loop in there, it works great.

 

[JerryKlmns]

Just to add some info here

Using stream object and ADO rs,

.GetString(adClipString, 1, "|", vbCrLf)
works faster while looping and writting every record than

.GetString(adClipString, , "| ", vbCrLf)
reading the whole resultset and writting all of it

For 26 fields of ~60k records

 

[strongm]

I have to say I'm surprised

 

[gmmastros]

Since I'm over budget and behind schedule, I have plenty of time to 'play'.

Here's what I found: Looping through the recordset is faster than grabbing the entire set of data and writing it once. This surprises me too. But I didn't stop there. I kept going.

By getting several records at a time, it was faster than processing each record one by one. I used the (less accurate) Timer function, instead of API calls to get the time. Since the times were measured in seconds, I decided this would be accurate enough. I tested this in the VB IDE and compiled.

When looping through a 40,000 record recordset with 6 fields:

Without Looping: 2.7 seconds
Looping (incrementing by 1): 1.5 seconds
Looping (incrementing by 10): 0.6 seconds
Looping (incrementing by 100): 0.45 seconds
Looping (incrementing by 1000): 0.45 seconds

My code snippet looks like this...

Set oStream = FSO.CreateTextFile("C:\LoopingBy[!]10[/!].txt", True)
sStart = Timer
While Not RS.EOF
    Call oStream.Write(RS.GetString(adClipString, [!]10[/!], "|", vbCrLf))
Wend
oStream.Close
lblLoopBy[!]10[/!].Caption = "Loop By [!]10[/!] Method:  " & Timer - sStart
    
RS.MoveFirst

The only thing different between the 1, 10, 100, and 1000 loops are the numbers in red.

I'm not really sure why the time is different.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[strongm]

Erm - does anyone want to try the ADO stream rather than the FSO stream to see if it is any faster (I currently don't have time to set up a test rig myself)?