Solved

TransferText - need headers on my data columns

Posted on 2001-06-18
6
568 Views
Last Modified: 2012-06-27
Hi All,

I have written a function that exports the contents  of my table "items" into a text file, all is good there  except that my application requires certain words on the top line of the data it is importing. (i.e.

SPECNAME DATABSR DATABSR2 AMOUNT1
wert 2341 1234 1234.4323
wert 2341 1234 1234.4323
wert 2341 1234 1234.4323
wert 2341 1234 1234.4323
wert 2341 1234 1234.4323
( where the top values are only printed one time in the file )

Here is the code that prints the data currently,  without the "headers" (is that the right term?)

Public Sub exporty()
DoCmd.TransferText acExportDelim, "Item Export Specification", "Item", "C:/quick/QB_Export.txt", False
End Sub

Also, I should have mentioned I am exporting a file for import into Quickbooks, if anyone has experience with that I would greatly appreciate it.

DaveMon
0
Comment
Question by:DaveMon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6201656
Hi DaveMon,

Use:
DoCmd.TransferText acExportDelim, "Item Export Specification", "Item", "C:/quick/QB_Export.txt", True

to export the field names (as headers) as the first row of your text file.

hope this helps,

Nosterdamus
0
 
LVL 2

Author Comment

by:DaveMon
ID: 6205044
I will take that into account Nos, so can you tell me if there is a way to put text other than the names of the fields at the top of the text file?

As it looks now,  I am going to have to export the table to another table with specially named fields just so I can have the field names exported correctly.

In VB I could just do something like,

Open  "myfile.txt"  for input as #1
Print "Thing1" "Thing2" Thing3" "Thing4"
Do while not EOF()
(put all my  other  stuff here)

Close File

I have tried stuff like this and failed,  is  it  possible  in Access?


Dave
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6205921
Hi Dave,

THe problem with the TransferText method (action) and/or with the OutputTo method (action) is that they do not have an append option...

Creating a new table to export is one method.

Another method that I can think of (allthough it seems to me to complex) is to create a Header.TXT file with the relevant field names, export the data to another TXT file, and then perform an append, such as:

'AppendFiles will append HeaderFile (first) and OutputFile (second)
'and will create an appended OutputFile of the two
Public Sub AppendFiles(HeaderFile As String, OutputFile As String)
    Const TEMP_FILE_NAME = "Temp.TXT"
    Dim MyChars
    Open HeaderFile For Input As #1
    Open OutputFile For Input As #2
    Open TEMP_FILE_NAME For Output As #3
   
    While Not EOF(1)
        Input #1, MyChars
        Print #3, MyChars
    Wend
    Close #1
    While Not EOF(2)
        Input #2, MyChars
        Print #3, MyChars
    Wend
    Close #2
    Close #3
    Kill OutputFile
    Name TEMP_FILE_NAME As OutputFile
End Sub


Sub test()
    'To call the Sub, use:
    AppendFiles "Header.txt", "MyFile.txt"
End Sub

Hope this helps,

Nosterdamus
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 2

Expert Comment

by:Charityg
ID: 6208368
Or you can create a query that will rename the fields, then you only have one table and a query that will always export any updates to the original table.

In the query design, select the table to add. Double-click the table's border to select all fields and drag to the grid. Then click in each of the fields and enter the name you desire followed by a colon (:)

The grid will look something like this.

MyName1:Field1 | MyName2:Field2 | MyName3:Field3
TableName      | TableName      | TableName

If you view the results of the query, you should see your names in the column heading.

Then export the query instead of the table.

Hope this helps
0
 
LVL 2

Accepted Solution

by:
jack49a earned 100 total points
ID: 6214324
You could export without the headers, close the file, and then call a sub which will read the file and add the headers you want.  You'll have to play with the first Print line to get the spacing you need if your file is space or tab delimited.  If comma delimited is an option that might be a bit easier to deal with (just add commas to the first Print line).

Jack

Function AddHeaders()
 Dim strEachLine As String
 Dim strFileIn As String
 Dim strFileOut As String
strFileIn = "D:\access\myinfile.txt" 'previously exported
strFileOut = "D:\access\myoutfile.txt"

Open strFileIn For Input As #1
Open strFileOut For Output As #2

Print #2, "SPECNAME DATABSR DATABSR2 AMOUNT1"

Do Until EOF(1)
 Line Input #1, strEachLine
 Print #2, strEachLine
Loop



MsgBox "Finished " & strFileOut
Close #1
Close #2

End Function
0
 
LVL 2

Author Comment

by:DaveMon
ID: 6247296
Thanks All,
Sorry  for the  delay,,  I  am  accepting jacks  answer  and  posting  50  for nosterdamus in the access area....thanks again


Dave
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

695 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