Solved

MS Access Output to Text File

Posted on 2011-03-21
11
319 Views
Last Modified: 2012-05-11
I need to export data from a query to a text file.  I need to align the columns in the text file.

Example:

Col1           Col2          Col3         Col4
Test 1        Test 2       Test 3      Test 4

Any ideas?
0
Comment
Question by:CipherIS
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 39

Expert Comment

by:als315
ID: 35180698
You can use Tab delimited columns of "Fixed width" columns.
1. Tab "is adding spaces" to 8 symbols, but sometimes it will not be enough.
2. In "Fixed width" columns you can manually add spaces to the beginning or to the end of text, but you should know maximum allowed length of text in column. (function space(num) is creating num spaces)
0
 
LVL 10

Expert Comment

by:khan_webguru
ID: 35180736
Hello Bro,

I faced the same problem in the past. My problem was that in text file final result was printing as below

 
19/05/2010 15:33:34   Verbose Main    in main method
19/05/2010 15:33:34   Information DoesSomethingInteresting()  the answer to faster than light travel is

Open in new window


for above poor display format my code was like that

 
LogFile.Write(string.Format("{0}\t{1}\t{2}\t{3}", log.Time, log.Level, log.Method, log.Message));

Open in new window


But that was not my desired result because I wanted to display the text nicely formated means left align like in table like its displaying below:

 
19/05/2010 15:33:34   Verbose     Main                        in main method
19/05/2010 15:33:34   Information DoesSomethingInteresting()  the answer to faster than light travel is

Open in new window


To get above mentioned result I changed my code like this that is written below

 
LogFile.Write(string.Format("{0,-10} {1,-11} {2,-30} {3}", ...));

Open in new window


In my case I have just two rows that can by any number.

Hope this will help you to solve your problem.

Regards,

Asif Ahmed Khan
0
 
LVL 19

Expert Comment

by:darbid73
ID: 35180774
First here is some code for simple addition of one line.

Dim S As String

    Open "full_Path_to_File_log.txt" For Append As #1
   
    S = "Enter You text here"
    Print #1, S
    Close #1

Open in new window


With respect to formating - you need to decide.  The idea of a text file is that formatting is limited.  So Tabs or Commas are really the only options.  Formating is what MSWORD does.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35182193
What will you be viewing/using your text files with?
Excel opens .csv files.
One column per comma, one row per carriage return.

Another possibility is to spit it out as html with <table><tr> and <td>

Or are you importing the result into something non-Office?
0
 
LVL 19

Expert Comment

by:darbid73
ID: 35182244
I agree with Nick67, who I feel is following the same idea I have.

Formating is not for a text file.

You need to use some special text file ideas like

HTML
XML
Keys eg ini file
CSV
your own type
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:CipherIS
ID: 35186101
needs to export to RTF or TXT file.  that is the requirement.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35186261
Lots of fun.
OK.
Take your query.
Manually paste it into MS word.
The save the file as .rtf

Is the resulting file acceptable for end use?
If it is, then do you need it coded/automated?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 35204112
Here is the code I ahve but it is not working.  the text file is not being created.  Any ideas?
Dim fns As String
fns = "c:\List.txt"

Dim fso As FileSystemObject
Dim a As Object

Set fso = CreateObject("Scripting.FileSystemObject")
Set a = fso.CreateTextFile(fns, True)
a.WriteLine ("This is a test.")
a.Close

  Open fns For Output As #3
  Write #3, "TEST"
  Close #3

Open in new window

0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 35204380
All right.

If you want to code it, then here's the pseudocode to think about.

open a recordset from your query
create a textstream object

set up an array of integers, one element for each field in the query
walk through each record in the set
compare the length of each field to the value stored in the array
if the field is larger, update the array
After the recordset has been walked through, you know know how big each column needs to be

reset the recordset to the first record
walk through it again
take each field
pad it with spaces equal to the difference between the array value and the field length
write it to a textstream object
next record
at the end of the recordset, close the textstream


Some of the needful code is here, but I am not a good machine to complete it from the pseudocode.
Whack at it.
If you get stuck post your progress
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

Dim x As Integer
Dim wait As Double
Dim BatFile As TextStream
Dim Success As Boolean

'snag a recordset of the query
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from qryMyQuery, dbOpenDynaset, dbSeeChanges)

If rs.RecordCount = 0 Then Exit Function

Set BatFile = fs.CreateTextFile("c:\myFile.txt", True)

BatFile.WriteLine ("something")
BatFile.WriteLine ("some thing else")

rs.MoveFirst
Do Until rs.EOF
    BatFile.WriteLine (rs.fields(x) & "some number of spaces"
    BatFile.WriteLine (vbCrLf)
    rs.MoveNext
Loop


BatFile.Close
Set BatFile = Nothing

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35208602
If you are not running as admin on the box, getting things written to the root of the drive (c:\) can be cut off
If your file is not being created, ensure that you CAN create a file where you would like it.
c:\program files is another area that may be cut off.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35209474
Here's your code to build a column aligned text file.
My table was tblEmployees.
It has a timestamp field that cannot be dealt with nicely, so there is some skipping code.
The code allows for the field names and their length and puts a minimum of 4 spaces between columns
Public Sub BuildaJustifiedFile()
'On Error Resume Next

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

Dim x As Integer 'counter for fields
Dim y As Integer 'counter for spaces
Dim BatFile As TextStream
Dim MyFieldLengths() As Integer ' array to hold column widths
Dim myfield As Field

'snag a recordset of the query
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from tblEmployees", dbOpenDynaset, dbSeeChanges) ' my table, change as needed

If rs.RecordCount = 0 Then Exit Sub
rs.MoveLast
ReDim MyFieldLengths(rs.Fields.count - 1) ' ReDim the array to the number of fields
rs.MoveFirst

'walkthrough the field names and set those as initial widths
x = 0
For Each myfield In rs.Fields
    MyFieldLengths(x) = Len(myfield.Name)
    'MsgBox myfield.Name & " " & Len(myfield.Name)
    x = x + 1
Next myfield

'For x = 0 To UBound(MyFieldLengths)
'    MsgBox rs.Fields(x).Name & " " & MyFieldLengths(x)
'Next x

'walk through the records and find the widest stored value in each
Do Until rs.EOF = True
    x = 0
    For Each myfield In rs.Fields
        If myfield.Name = "SSMA_Timestamp" Then GoTo skip1 'this is a field that VBA code can't touch, you may not need this
        If Len(CStr(Nz(myfield.Value, ""))) > MyFieldLengths(x) Then
            MyFieldLengths(x) = Len(CStr(myfield.Value))
        End If
        x = x + 1
skip1:
    Next myfield
    rs.MoveNext
Loop
        

'start the text file
Set BatFile = fs.CreateTextFile("c:\tempPDF\myFile.txt", True) ' my location, make sure you can create a file wherever you change this to
rs.MoveFirst

'walk through the fields and write each field name
x = 0
For Each myfield In rs.Fields
    If myfield.Name = "SSMA_Timestamp" Then GoTo skip2 'this is a field that VBA code can't touch, you may not need this
    y = 0
    BatFile.Write (Nz(myfield.Name, ""))
    For y = 0 To MyFieldLengths(x) - Len(CStr(Nz(myfield.Name, ""))) + 4 ' pad it out to the max + 4
        BatFile.Write (Chr(32))
    Next y
    x = x + 1
skip2:
Next myfield
BatFile.WriteLine vbCrLf ' new line

'walk through the records and write each field value
Do Until rs.EOF = True
    x = 0
    For Each myfield In rs.Fields
        If myfield.Name = "SSMA_Timestamp" Then GoTo skip3 'this is a field that VBA code can't touch, you may not need this
        y = 0
        'MsgBox Nz(myfield.Value, "")
        BatFile.Write (Nz(myfield.Value, ""))
        For y = 0 To MyFieldLengths(x) - Len(CStr(Nz(myfield.Value, ""))) + 4 ' pad it out to the max + 4
            BatFile.Write (Chr(32))
        Next y
        x = x + 1
skip3:
    Next myfield
    rs.MoveNext
    BatFile.WriteLine vbCrLf
Loop

'finish up
BatFile.Close
Set BatFile = Nothing

End Sub

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now