Creating an ASCII file from a recordset 3

[cyoung]

I have figured out how to create a text file (tab delimmited) from a recordset by using the FileSystemObject and the Write option.

What I need to be able to do is to also include the field names as the first line for a header.

Is there a way to do this?

Thanks!

 

[Kavius]

Yes, but I how are you doing the data?

Depending on that, the way I suggest may not be helpful.

 

[Swi]

Before you start exporting the recordset loop through your recordset's field names and export them:

Ex. -

For i = 0 To rs.Fields.Count - 1
MsgBox rs.Fields(i).Name
Next

Swi

 

[cyoung]

I am opening a recordset, and then writing the output text file as follows:

Dim fso1 as New scripting.FileSystemObject
Dim outfile as scripting.TextStream
Set outfile = fso1.CreateTextFile("c:\testout.txt", True)
outfile.Write (myRS.GetString(adClipString, , vbTab, vbCrLf, "" ))
outfile,Close

myRS is the recordset result of my query

 

[Kavius]

Then Swi has it right.

Just loop through the field names and dump them to the file stream first. Add a carriage return and dump the recordset.

While Swi's way will work exactly the same, I have a slightly different syntax I prefer, so I'll throw it out there.

dim f as field
for each f in myRS.fields
  msgbox f.name
next

Exactly the same, I just like the way it reads better.

 

[cyoung]

Kavius and Swi,

Thanks for the help. It works perfectly. Stars to both!

I made this into a module so that it can be called from different forms without any user input needed.

Just for future referecnce in case anybody else has this question, I am going to post my final code.

Sub ExportToText(RSSQL As String, OutputName As String)
'Will export a recordset to a text file including header
'info. The delimmiter is set to a tab. EOL is a CR/LF
'
Dim myConn As New ADODB.Connection
Dim myRS As New ADODB.Recordset
Dim fso1 As New scripting.FileSystemObject
Dim outfile As scripting.TextStream
Dim headerinfo As String, f As Field
myConn.Open "DSN=test"
myRS.Source = RSSQL
Set myRS.ActiveConnection = myConn
myRS.Open
For Each f In myRS.Fields
headerinfo = headerinfo & f.Name & vbTab
Next
headerinfo = headerinfo & vbCrLf
Set outfile = fso1.CreateTextFile(OutputName, True)
outfile.Write headerinfo
outfile.Write (myRS.GetString(adClipString, , vbTab, vbCrLf, "&quot)
outfile.Close
myRS.Close
myConn.Close
End Sub

 

[Kavius]

you may want to strip the last tab off of your header line:
[tt]
For Each f In myRS.Fields
headerinfo = headerinfo & f.Name & vbTab
Next
headerinfo = left(headerinfo,len(headerinfo)-len(vbtab))
[/tt]
(I may as well do some work for that star...)

 

[cyoung]

Kavius,

Again, I must say thanks

I had just noticed that little problem and was starting on the solution

 

[Glasgow]

Just an aside - for a SQL server database, you could use a shell command to fire the bcp utility and achieve much the same thing - effectively in one line of code. The same file can then be used by BULK INSERT, thus creating an import/export capability.

 

[Kavius]

I had no idea that little toy existed. Thank-you.

Here is an link to msdn info on it for anyone looking:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp

 

[Kavius]

I don't know what you are using this for but apparently I am bored:

dim uLine as string
dim ch as string

uLine = headerinfo
for i = 1 to len(uLine)
  ch = mid(uLine,i,1)
  if(ch <> vbTab and ch <> &quot;~&quot;)then
    uline = replace(uLine,ch,&quot;~&quot;)
  end if
next

Not the most efficient, but if this file is to be human readable an underline is sometimes nice...

Sorry, bordom moment...

 

[cyoung]

The bcp utility looks nice, but I am using mySQL as the database for this little application.

The text file that I am creating is for another software package that is rather limited as to the type of file that it is able to use, thus the need to create the tab-delimmited file.

I will keep the underline function in my bag-o-tricks if I ever need to create a text-based report