How to Export SQL Database Table to Text File

[ISChana]

Dear All

I have SQL Server Database Table say Address. I like to Export this file into Text File through VB Coding i.e. User just enter the name of the text file to be created and press OK

I know its very easy through SQL Servr using Import/Export Method.. But I Don't know how through VB Coding..

One More thing in another file I like to put special charachter say $ in joining every field



Suppose I have table named address, str as :

name char(30), father_name char(30), dob datetime , address char(50)
then result should be in one file

John David 1/1/1978 45 Wilkinson Road, London
and in another file as
John$David$1/1/1978$45 Wilkinson Road, London



Pl. suggest.

regards

Inder

 

[macleod1021]

In both instances, you can just do a loop through an ADO recordset. First thing you need to do is set up an object to handle IOStreams then create your recordset. Once you have those, just pump your recordset into the file through your IOStreams object and you can cat any type of formatting you want into it. It's still early (only one cup of coffee in me atm) but if you need help with the actual code, let me know. After I wake up, I can copy some out for you. I had to do this for a customer a while back. I don't remember why tho

 

[Andrzejek]

What about this?

Dim recOne As ADODB.Recordset
Dim strSQL As String
Dim i As Integer

strSQL = "SELECT * FROM address ORDER BY Whatever"

Set recOne = New ADODB.Recordset
recOne.CursorType = adOpenForwardOnly
recOne.CursorLocation = adUseClient
recOne.LockType = adLockReadOnly
recOne.Open strSQL, Cn

Open "C:\File1.txt" For Output As #1
Open "C:\File2.txt" For Output As #2

'Put TAB between data in file1.txt
For i = 1 To recOne.RecordCount

    Print #1, recOne!Name & vbTab & _
        recOne!father_name & vbTab & _
        recOne!DOB & vbTab & recOne!address
    Print #2, recOne!Name & "$" & _
        recOne!father_name & "$" & _
        recOne!DOB & "$" & recOne!address

    recOne.MoveNext
Next i

Close #1
Close #2
recOne.Close

Would that work?

---- Andy

 

[Golom]

Another Option

Dim rs As ADODB.recordset
Set rs = New ADODB.Recordset
Dim nH1 As Integer
Dim nH2 As Integer

nH1 = FreeFile
Open "Formatted.txt" For OutPut As #nH1

nH2 = FreeFile
Open "OutPut.txt" For OutPut As #nH2

rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.CursorType = adOpenStatic
rs.Open "Select * From Address ", cn

Do Until rs.EOF
   Print #nH1, rs.GetString(adClipString, 1, "   ")
   Print #nH2, rs.GetString(adClipString, 1, "$")
   rs.MoveNext
Loop
Close #nH

 

[ISChana]

I welcome the Suggestions given by Andrzejek and Golom . Both solutions are same ... It solves my purpose but I hv little problem in the First file Two fields are seprated either by tab (Andrzejek Case) or by space (Golom case) . it should not be like this i like to maintain the same length
e.g. name char(20) father_name char(30) address(50)

so it should give exact space ie. 20 char for name(even name is of 3 char rest should be blank) similarly for father name and address fields..
II File is ok (with $ concatenatd)

thanks

Inder

 

[Andrzejek]

I don't want to give you a whole solution, just the clue:

Dim strName As String

strName = "ISChana"

Label1.Caption = strName & Space(20 - Len(strName))

There are some other ways, too.

HTH

---- Andy

 

[Sheco]

You could persist an entire ADO recordset to an xml file.