Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TransferText - need headers on my data columns

Posted on 2001-06-18
6
Medium Priority
?
587 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 400 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

604 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