Solved

TransferText - need headers on my data columns

Posted on 2001-06-18
6
554 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export to Excel 7 33
Compile Error - Argument not optional - MS Access - VBA - Adobe Acrobat 2 26
ADODB problem 20 37
Access 2016 - combo box 3 15
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

839 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