Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SELECT INTO to text file

Discussion in 'Information Technology' started by RB Smissaert, Jul 28, 2006.

Tags:
  1. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: SELECT INTO to text file -- ExportDelimitedText

    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

    *

    RB Smissaert wrote:
    > 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
    >
    >
     
  4. bart.smissaert@gmail.com

    bart.smissaert@gmail.com
    Expand Collapse
    Guest

    Re: SELECT INTO to text file -- ExportDelimitedText

    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

    strive4peace wrote:
    > 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
    >
    > *
    >
    > RB Smissaert wrote:
    > > 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
    > >
    > >
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Re: SELECT INTO to text file -- ExportDelimitedText

    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

    <bart.smissaert@gmail.com> wrote in message
    news:1148290665.327709.55500@g10g2000cwb.googlegroups.com...
    > 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
    >
    > strive4peace wrote:
    >> 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
    >>
    >> *
    >>
    >> RB Smissaert wrote:
    >> > 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
    >> >
    >> >

    >
     
  6. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Re: SELECT INTO to text file -- ExportDelimitedText

    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" <wijnen.nospam.please@online.no> wrote in message
    news:%23WFhnxYfGHA.5088@TK2MSFTNGP02.phx.gbl...
    > 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
    >
    > <bart.smissaert@gmail.com> wrote in message
    > news:1148290665.327709.55500@g10g2000cwb.googlegroups.com...
    >> 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
    >>
    >> strive4peace wrote:
    >>> 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
    >>>
    >>> *
    >>>
    >>> RB Smissaert wrote:
    >>> > 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
    >>> >
    >>> >

    >>

    >
    >
     
  7. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Re: SELECT INTO to text file -- ExportDelimitedText

    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" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:en9IxCZfGHA.4776@TK2MSFTNGP05.phx.gbl...
    > 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" <wijnen.nospam.please@online.no> wrote in message
    > news:%23WFhnxYfGHA.5088@TK2MSFTNGP02.phx.gbl...
    >> 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
    >>
    >> <bart.smissaert@gmail.com> wrote in message
    >> news:1148290665.327709.55500@g10g2000cwb.googlegroups.com...
    >>> 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
    >>>
    >>> strive4peace wrote:
    >>>> 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
    >>>>
    >>>> *
    >>>>
    >>>> RB Smissaert wrote:
    >>>> > 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
    >>>> >
    >>>> >
    >>>

    >>
    >>

    >
     
  8. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    Re: SELECT INTO to text file -- ExportDelimitedText

    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" <wijnen.nospam.please@online.no> wrote in message
    news:ek4vWOZfGHA.764@TK2MSFTNGP03.phx.gbl...
    >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" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:en9IxCZfGHA.4776@TK2MSFTNGP05.phx.gbl...
    >> 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" <wijnen.nospam.please@online.no> wrote in message
    >> news:%23WFhnxYfGHA.5088@TK2MSFTNGP02.phx.gbl...
    >>> 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
    >>>
    >>> <bart.smissaert@gmail.com> wrote in message
    >>> news:1148290665.327709.55500@g10g2000cwb.googlegroups.com...
    >>>> 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
    >>>>
    >>>> strive4peace wrote:
    >>>>> 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
    >>>>>
    >>>>> *
    >>>>>
    >>>>> RB Smissaert wrote:
    >>>>> > 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
    >>>>> >
    >>>>> >
    >>>>
    >>>
    >>>

    >>

    >
    >
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Re: SELECT INTO to text file -- ExportDelimitedText

    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


    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%23NlgfiZfGHA.3364@TK2MSFTNGP05.phx.gbl...
    >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" <wijnen.nospam.please@online.no> wrote in message
    > news:ek4vWOZfGHA.764@TK2MSFTNGP03.phx.gbl...
    >>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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:en9IxCZfGHA.4776@TK2MSFTNGP05.phx.gbl...
    >>> 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" <wijnen.nospam.please@online.no> wrote in message
    >>> news:%23WFhnxYfGHA.5088@TK2MSFTNGP02.phx.gbl...
    >>>> 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
    >>>>
    >>>> <bart.smissaert@gmail.com> wrote in message
    >>>> news:1148290665.327709.55500@g10g2000cwb.googlegroups.com...
    >>>>> 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
    >>>>>
    >>>>> strive4peace wrote:
    >>>>>> 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
    >>>>>>
    >>>>>> *
    >>>>>>
    >>>>>> RB Smissaert wrote:
    >>>>>> > 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
    >>>>>> >
    >>>>>> >
    >>>>>
    >>>>
    >>>>
    >>>

    >>
    >>

    >
     
  10. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    RB Smissaert wrote:
    > 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


    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.

    --
     
  11. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    I learn something new every day...

    Pieter

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1148303422.352820.308750@j55g2000cwa.googlegroups.com...
    >
    > RB Smissaert wrote:
    >> 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

    >
    > 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.
    >
    > --
    >
     
  12. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1148303422.352820.308750@j55g2000cwa.googlegroups.com...
    >
    > RB Smissaert wrote:
    >> 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

    >
    > 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.
    >
    > --
    >
     
  13. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    RB Smissaert wrote:
    > 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.

    --
     
  14. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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





    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:eSm93OafGHA.4276@TK2MSFTNGP03.phx.gbl...
    > 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
    >
    > "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    > news:1148303422.352820.308750@j55g2000cwa.googlegroups.com...
    >>
    >> RB Smissaert wrote:
    >>> 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

    >>
    >> 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.
    >>
    >> --
    >>

    >
     
  15. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

  16. bart.smissaert@gmail.com

    bart.smissaert@gmail.com
    Expand Collapse
    Guest

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

    RBS
     
  17. bart.smissaert@gmail.com

    bart.smissaert@gmail.com
    Expand Collapse
    Guest

    > C:\doesnotexist.xls

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


    RBS
     
  18. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    bart.smissaert@gmail.com wrote:
    > > C:\doesnotexist.xls

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


    How could you do that <g>? My point is, the workbook doesn't have to
    exist.

    You need to establish a Jet connection. I use an Excel workbook as the
    data source because it need not exist. If it doesn't, a Jet connection
    is still established and no workbook is created (unless you do
    something to cause it to e.g. 'CREATE TABLE...'). If it does exist,
    make sure it is not open to avoid the Excel ADO memory leak bug.

    Take a look at the article up thread, it may give you the details your
    require about connection strings, data types, etc.

    HTH,
    Jamie.

    --
     
  19. bart.smissaert@gmail.com

    bart.smissaert@gmail.com
    Expand Collapse
    Guest

    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
     
  20. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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


    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1148305386.339197.292600@j33g2000cwa.googlegroups.com...
    >
    > RB Smissaert wrote:
    >> 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.
    >
    > --
    >
     
  21. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

Share This Page