Solved

TransferText - need headers on my data columns

Posted on 2001-06-18
6
521 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 2

Expert Comment

by:Charityg
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

771 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

10 Experts available now in Live!

Get 1:1 Help Now