SELECT INTO to text file

[RB Smissaert]

Not really an Access question, but I reckon somebody here will know.

As I can now (thanks to Dirk Goldgar) put data directly from Interbase into
an Access table,
without using a RecordSet, I wonder if the same can be done, but now putting
the data in a
text file. I would guess it can be done, but haven't found the right syntax
yet, although I can
do it from text file to text file.

I take it that there will be some performance gain compared to doing:
Data to RecordSet,
RecordSet.GetString
Writing the string to the text file

Although even this is pretty fast:

Sub RecordSetStringToText(rs As ADODB.Recordset, _
strFile As String, _
Optional strColDelim As String = ",", _
Optional strRowDelim As String = vbCrLf, _
Optional lRows As Long = -1, _
Optional strFields As String = "", _
Optional bFieldsFromRS As Boolean)

Dim arr
Dim i As Long

If bFieldsFromRS Then
'get field row from the recordset
'--------------------------------
arr = fieldArrayFromRS(rs)
strFields = arr(0)
If UBound(arr) > 0 Then
For i = 1 To UBound(arr)
strFields = strFields & "," & arr(i)
Next
End If
strFields = strFields & vbCrLf
End If

If lRows = -1 Then
StringToTextFile strFile, _
strFields & _
rs.GetString(2, , strColDelim, strRowDelim)
Else
StringToTextFile strFile, _
strFields & _
rs.GetString(2, lRows, strColDelim, strRowDelim)
End If

'needed as GetString will move the cursor to the end
'---------------------------------------------------
rs.MoveFirst

End Sub

Function fieldArrayFromRS(rs As ADODB.Recordset) As Variant

'gets the field names from an ADO recordset
'and puts them in a one dimensional 0-based array
'------------------------------------------------

Dim objField As ADODB.Field
Dim tempArray()
Dim n As Byte

ReDim tempArray(0 To rs.Fields.count - 1)

For Each objField In rs.Fields
tempArray(n) = objField.Name
n = n + 1
Next

fieldArrayFromRS = tempArray

End Function


Thanks for any advice.

RBS

 

[strive4peace]

here is a general procedure that may be useful to you for
some ideas...

this code creates a TAB delimited file unless otherwise
specified ...

'~~~~~~~~~~~~~~~~~~~~
Sub ExportDelimitedText( _
pRecordsetName As String, _
pFilename As String, _
Optional pBooIncludeFieldnames As Boolean, _
Optional pBooDelimitFields As Boolean, _
Optional pFieldDeli As String)

'written by Crystal
'strive4peace2007 at yahoo dot com

'NEEDS reference to Microsoft DAO Library

'PARAMETERS
'pRecordsetName --> name of query or table;
' or SQL statement
'pFilename -- name of file to create
'pBooIncludeFieldnames -- optional
' -- TRUE if you want fieldnames at top
'pBooDelimitFields -- optional
'-- TRUE for delimiter, FALSE for none
'pFieldDeli -- optional
'-- string to use as delimiter,
' TAB will be used if nothing specified

'BASIC USEAGE
' ExportDelimitedText _
"QueryName", _
"c:\path\filename.csv"

'set up error handler
On Error GoTo ExportDelimitedText_error

Dim mPathAndFile As String, mFileNumber As Integer
Dim r As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String

booDelimitFields = Nz(pBooDelimitFields, False)
booIncludeFieldnames = Nz(pBooIncludeFieldnames, False)

'make the delimiter a TAB character unless specified
If Nz(pFieldDeli, "") = "" Then
mFieldDeli = Chr(9)
Else
mFieldDeli = pFieldDeli
End If

'if there is no path specfied,
'put file in current directory
If InStr(pFilename, "\") = 0 Then
mPathAndFile = CurrentProject.Path
Else
mPathAndFile = ""
End If

mPathAndFile = mPathAndFile & "\" & pFilename

'if there is no extension specified, add TXT
If InStr(pFilename, ".") = 0 Then
mPathAndFile = mPathAndFile & ".txt"
End If

'get a handle
mFileNumber = FreeFile

'close file handle if it is open
'ignore any error from trying to close it if it is not
On Error Resume Next
Close #mFileNumber
On Error GoTo ExportDelimitedText_error

'delete the output file if already exists
If Dir(mPathAndFile) <> "" Then
Kill mPathAndFile
DoEvents
End If

'open file for output
Open mPathAndFile For Output As #mFileNumber

'open the recordset
Set r = CurrentDb.OpenRecordset(pRecordsetName)

'write fieldnames if specified
If booIncludeFieldnames Then
mOutputString = ""
For mFieldNum = 0 To r.Fields.Count - 1
If booDelimitFields Then
mOutputString = mOutputString & """" _
& r.Fields(mFieldNum) & """" & mFieldDeli
Else
mOutputString = mOutputString _
& r.Fields(mFieldNum).Name & mFieldDeli
End If
Next mFieldNum

'remove last delimiter
if pBooDelimitFields then
mOutputString = Left(mOutputString, _
Len(mOutputString) - Len(mFieldDeli))

end if

'write a line to the file
Print #mFileNumber, mOutputString
End If

'loop through all records
Do While Not r.EOF()

'tell OS (Operating System) to pay attention
DoEvents
mOutputString = ""
For mFieldNum = 0 To r.Fields.Count - 1
If booDelimitFields Then
Select Case r.Fields(mFieldNum).Type
'string
Case 10, 12
mOutputString = mOutputString & """" _
& r.Fields(mFieldNum) & """" _
& mFieldDeli
'date
Case 8
mOutputString = mOutputString & "#" _
& r.Fields(mFieldNum)
& "#" & mFieldDeli
'number
Case Else
mOutputString = mOutputString _
& r.Fields(mFieldNum) & mFieldDeli
End Select
Else
mOutputString = mOutputString _
& r.Fields(mFieldNum) & mFieldDeli

End If

Next mFieldNum

'remove last TAB
if booDelimitFields then mOutputString = _
Left(mOutputString, Len(mOutputString) _
- Len(mFieldDeli))

'write a line to the file
Print #mFileNumber, mOutputString

'move to next record
r.MoveNext
Loop

'close the file
Close #mFileNumber

'close the recordset
r.Close

'release object variables
Set r = Nothing

MsgBox "Done Creating " & mPathAndFile, , "Done"


Exit Sub

'ERROR HANDLER
ExportDelimitedText_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " ExportDelimitedText"
'press F8 to step through code and correct problem
Stop
Resume
End Sub

'~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[bart.smissaert]

Hi strive4peace,

Thanks for the reply, but is your code any faster than the one I
posted?
I was looking for a solution without creating the recordset first.

RBS

 

[Pieter Wijnen]

You can Use the DoCmd.TransferText Command to do it
Just Save the spec to reuse
(do a manual export once to create the spec by using File/Export)

HTH

Pieter

 

[RB Smissaert]

What I am asking for wouldn't involve Access.
It would be running a SQL string on an Interbase database with the output
directly to a text file.
See the thread Interbase data to Access of 19 May.
This will do this, but with the output to Access, now I need it to be to
text.
I would think it can be done without creating a RecordSet first and that
could be faster.

RBS

 

[Pieter Wijnen]

I think you need to use a recordset
consider using ADO stream object for output

see
http://www.w3schools.com/ado/ado_ref_stream.asp

for further info on ADO stream objects

HTH

Pieter

 

[RB Smissaert]

I am not sure I do have to use a Recordset, but if I do then would using an
ADO stream be faster than using GetString and writing the string to text?

RBS

 

[Pieter Wijnen]

I think so (you can build the whole thing into a variable - if it will in
memory & write it in one go)
at least the code will be shorter & thus more readable
haven't used streams much I must confess (at least not extensivly/lately)

Pieter

Anybody want to comment on this thread - feel free

 

[Pieter Wijnen]

I learn something new every day...

Pieter

See:

http://groups.google.com/group/microsoft.public.excel.programming/msg/0acd8f0907abd2c5

Is this approach faster? You'll have to do the metrics <g>.

Jamie.

 

[RB Smissaert]

Thanks, I thought it would be something like that, but the quoted thread is
a bit sparse. How is the query actually executed?
Would you have any more complete code sample?

RBS

 

[Pieter Wijnen]

In Access

Dim Db AS DAO.Database

Set Db = Access.CurrentDb
Db.Execute "DROP TABLE [Text;Database=C:\My Folder\;].[MyFile#txt]"
Db.Execute "SELECT " & _
"fname, minit, lname " & _
"INTO [Text;Database=C:\My Folder\;].MyFile#txt " & _
"FROM " & _
"[ODBC;Driver={SQL
Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee"

Rewriting to ADO I leave as an exercise

Pieter

 

[bart.smissaert]

Thanks, will give that a try after work.
Maybe I just forgot the final ;

RBS

 

[bart.smissaert]

C:\doesnotexist.xls

Do I indeed have to put a non-existent workbook here?


RBS

 

[bart.smissaert]

Right, I see now what you mean.
I need to connect to an Interbase database and output to text.
I have the connection string for the connection to Interbase.
I think I have all the needed information now and I think I can work it
out.
It is just that all this non-standard ADO data work isn't that well
documented.

RBS

 

[RB Smissaert]

Sorry, I still don't get this.
What should I put at:
"Data Source=C:\doesnotexist.xls"

My datasource is an Interbase .gdb file, but putting that
in place of C:\doesnotexist.xls doesn't work:
External table is not in expected format.

Not sure now I can use the text driver here.

RBS

Thanks, I thought it would be something like that, but the quoted thread
is
a bit sparse. How is the query actually executed?
Would you have any more complete code sample?

You don't need me to tell you how to create an ADO connection to a
non-existant Excel workbook and execute the SQL, do you <g>?

Sub JustFourLines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;" & _
"Data Source=C:\doesnotexist.xls"
con.Execute _
"SELECT fname, minit, lname" & _
" INTO [Text;Database=C:\My Folder\;].MyFile#txt FROM
[ODBC;Driver={SQL" & _
" Server};SERVER=MYSERVER;DATABASE=pubs;UID=***;Pwd=***;].employee;
"
End Sub

Jamie.

 

[Pieter Wijnen]

Forever in your debt (I hate csv, headaches galore conserning inches ")
thx

Pieter

 

[Pieter Wijnen]

I'm just curious why you used ODBC for the ADO connection to SQLServer <g>

Pieter

 

[Pieter Wijnen]

oops, you're in for some serious spam now

Pieter

 

[bart.smissaert]

Thanks for that extra explanation.
Do you reckon now that I could do the SELECT INTO construction with
my Interbase database with output to a textfile?
Have been fiddling with several constructions, but sofar no success.

RBS

 

[bart.smissaert]

Thanks for that extra explanation.
Do you reckon now that I could do the SELECT INTO construction with
my Interbase database with output to a textfile?
Have been fiddling with several constructions, but sofar no success.

RBS