Solved

TransferText - need headers on my data columns

Posted on 2001-06-18
6
536 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
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

895 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

11 Experts available now in Live!

Get 1:1 Help Now