Exporting Txt files

[Treason]

I don't mean to cross-post but i posted this in the queries section by accident.

I am trying to export each record from a report into different text files. One txt file for every record. I think a report is better to suite me. Because...

tables or queries by default export "horizontally"
[field1][field2][field3]etc..

Reports can be "taylored" to export "vertically"
[field1]
[field2]
[field3]

So i dont know whats my best route... I really just want 1 text for every record and exported vertically. Any suggestions are greatly appreciated.

 

[Travis]

Reports are not the only way to go. You could use the Open function to create the text files directly. With the report you would still need to run it for each file. Below is sample code:

Public Sub CreateFiles(ByVal sQueryorTableName As String, Optional sPath As String = "")
 On Error GoTo ErrorHandler
    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim intFileNum As Integer
    Dim lngCount As Long
    Dim sPathAndFile As String
    
    Set rst = New ADODB.Recordset
    Set cnn = New ADODB.Connection
    
    Set cnn = CurrentProject.Connection
    
    If sPath = "" Then sPath = CurrentProject.Path & "\TEMP"
    
    'Check to make sure the directory exists _
     If not make it.
    If Dir(sPath, vbDirectory) = "" Then
        MkDir sPath
    End If
    
    rst.Open sQueryorTableName, cnn, adOpenStatic, adLockReadOnly
    
    'Check to see if there are any records
    If rst.RecordCount < 1 Then Exit Sub
    
    rst.MoveFirst
    With rst
        Do While Not .EOF
            intFileNum = FreeFile
            
            'This sets the file name that this record will be saved with. _
             This is using the Absolute position of the Recordset.
            sPathAndFile = sPath & "\" & rst.AbsolutePosition & ".txt"
            
            'If the file exists Delete it.
            If Dir(sPathAndFile) <> "" Then Kill sPathAndFile
            
            'Open the file to have the data added to.
            Open sPathAndFile For Output As intFileNum
            
            'Add data one line at a time.
            Print #intFileNum, rst!Field1
            Print #intFileNum, rst!Field2
            Print #intFileNum, rst!Field3
        
            'Close this file
            Close #intFileNum
            
            'Goto next record
            .MoveNext
        Loop
    End With
    
    'Close the Recordset and Connection Objects
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

Exit Sub
ErrorHandler:
 Set rst = Nothing
 Set cnn = Nothing
 Err.Clear
 
End Sub

 

[DJ44]

Reviving an old thread ...

I have been attempting to develop code to export each individual record of a query to a seperate .txt file. Ideally, the name of the exported text file would be the name of the first field of the query [CulNum].txt

I have thought I had it but never did, and ,tail between my legs, manually copied the query to excel and gerry-rigged individual txt files from there.

I have just discovered this forum (an it is awesome!), searched, and this thread appeared --- giving me new hope!

Could someone (Travis?) please explain how the code could be modified to:

1. Set the txt file to be named after the first field of each record of the query

I think it would be the line :

sPathAndFile = sPath & "\" & rst.Field1 & ".txt"


2. I only require that the record by exported horizontially, so can I just omit the portion:

'Add data one line at a time.
Print #intFileNum, rst!Field1
Print #intFileNum, rst!Field2
Print #intFileNum, rst!Field3

All help is very much appreciated.

DJ

 

[Travis]

This code was already developed to create a single text file per record. It spit out the record with each field being on a seperate line. Just Concatinate the fields that you want on a single "Print" line. Below I show this using a Tab[/] to deliminate between the fields.

Public Sub CreateFiles(ByVal sQueryorTableName As String, Optional sPath As String = "")
 On Error GoTo ErrorHandler
    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim intFileNum As Integer
    Dim lngCount As Long
    Dim sPathAndFile As String
    
    Set rst = New ADODB.Recordset
    Set cnn = New ADODB.Connection
    
    Set cnn = CurrentProject.Connection
    
    If sPath = "" Then sPath = CurrentProject.Path & "\TEMP"
    
    'Check to make sure the directory exists _
     If not make it.
    If Dir(sPath, vbDirectory) = "" Then
        MkDir sPath
    End If
    
    rst.Open sQueryorTableName, cnn, adOpenStatic, adLockReadOnly
    
    'Check to see if there are any records
    If rst.RecordCount < 1 Then Exit Sub
    
    rst.MoveFirst
    With rst
        Do While Not .EOF
            intFileNum = FreeFile
            
            'This sets the file name that this record will be saved with. _
             This is using the Absolute position of the Recordset.
            sPathAndFile = sPath & "\" & rst.Fields("First Field Name")& ".txt"
            
            'If the file exists Delete it.
            If Dir(sPathAndFile) <> "" Then Kill sPathAndFile
            
            'Open the file to have the data added to.
            Open sPathAndFile For Output As intFileNum
            
            'Add Print Record.
            Print #intFileNum, rst!Field1 & vbTab & rst!Field2 & vbTab & rst!Field3
        
            'Close this file
            Close #intFileNum
            
            'Goto next record
            .MoveNext
        Loop
    End With
    
    'Close the Recordset and Connection Objects
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

Exit Sub
ErrorHandler:
 Set rst = Nothing
 Set cnn = Nothing
 Err.Clear
 
End Sub

 

[DJ44]

Thanks for the prompt reply.

I have been testing the code in a couple of databases and, when it is run the directory it created, the first txt file (correctly named) is created, but has no data. No other files are created; no error message appears.

Could this have something to do with the intFileNum variable, as I am now naming the file after the first field which is a string?

DJ

 

[Travis]

No, the intFileNum should not be the issue

Two things to look at:

1. How Many Records are in the RST variable? (rst.RecordCount)

2. What is on the Line that Your Print function is on?

 

[DJ44]

I put a Debug.Print rst.RecordCount
after the line:

If rst.RecordCount < 1 Then Exit Sub

Called the function in the Immediate window and got:

Call CreateFiles ("Query1","")
92
1
F:\database\TEMP\Barker.txt

There are 92 records in the query;

The 1 represent a Debug.Print intFileNum I inserted after intFileNum=FreeFile

The file path and name are correct for the first record.

As for your 2nd point --- I am unsure what you mean. In the code, I have exactly what you had written. Is that what you mean or do I have to find the actual Print funtion in the system (for which I'm afraid I will require additional instructions).

Thanks again.

DJ

 

[Travis]

On my 2nd point I was refering to this line in the code:

'Add Print Record.
Print #intFileNum, rst!Field1 & vbTab & rst!Field2 & vbTab & rst!Field3


Since we know that there are 92 records to be output to 92 files then we need to add a "BreakPoint" on the Do While Not .EOF line. You will need to step through the code [F8 Key] to find where it is failing. Also just another point if the field that you have choosen to be the File name is the same for any of the 92 records then only the last one in the Recordset will have a file at the end of the function as it Kills any file of the same name prior to opening it for output.

 

[DJ44]

Line by line debugging -- what a concept!

I put breakpoints through the loop and everything works as expected until the Print # line.

After that line is executed, the program goes to the error handler and exits the program.

When I pass my mouse over the rst!Field1 in the Print line, I get a pop-up that says:

<Item cannot be found in the collection corresponding to the re...>

I do not understand this since it found the field to name the file a few lines earlier, although the field was named, not positioned ..... what gives?

 

[Travis]

The reason is you have no fields named "Field1", "Field2"

You need to change them to real names or use the Field locations

Change this line

'Add Print Record.
Print #intFileNum, rst!Field1 & vbTab & rst!Field2 & vbTab & rst!Field3


To

'Add Print Record.
Print #intFileNum, rst![First Fields Name] & vbTab & rst![Second Fields Name] & vbTab & rst![Third Fields Name]

 

[DJ44]

Travis -- Got it working. Thanks so much for you help and patience.

Could you explain the difference in using the rst. versus using the rst! .

Is there a difference between () and [] and why, in the line
sPathAndFile = sPath & "\" & rst.Fields("First Field Name")& ".txt"

is there is the .Fields used and the round brackets with the FieldName in quotes?

Also, how would change:

Add Print Record.
Print #intFileNum, rst![First Fields Name] & vbTab & rst![Second Fields Name] & vbTab & rst![Third Fields Name]

to reference the fields positionally as opposed to by name?

Thanks again,. The above questions are for my interest and not meant to pester you.

Cheers! DJ

 

[Travis]

The Period . refers to properties (in this case the Fields property. The ! (also know as Bang) is the Field itself. Both work. It is more a programmers preference. However, there may be documentation to prove that one is faster then the other.