Solved

access 2007 - export table as pipe delimited text file.

Posted on 2012-04-05
6
1,536 Views
Last Modified: 2013-01-14
I am pulling an sql table into access, so user can change some of the data. Would like the user to be able to export the file as pipe delimited text file. If we just export as text, it has the pipe delimiter, but it also adds lines between each record. How do we make it quit doing that?
0
Comment
Question by:qbjgqbjg
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37812580
I can't think of any reason why you would export extra blank records.

Are you quite sure that your table does not have blank records in it?  And that the pipe symbol is not part of any data value?
0
 
LVL 9

Expert Comment

by:damerval
ID: 37812595
What is the problem with allowing it to add line feeds between records? It is part of the CSV format definition, which is the basis for the Access 2007 delimited text export engine. It might be easier just to strip the line feeds from the file after export.
I'll research the question while you give us a little more detail about what you're trying to do and why.

HTH

Philippe
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37813047
<If we just export as text, it has the pipe delimiter, but it also adds lines between each record. >
This is probably because you are using "OutPutTo" to do the Export...

Try using TransferText instead...
DoCmd.TransferText , "YourExportSpec", "YourTableName", "C:\YourFolder\YourFile.txt"
(Here obviously you will have to do the Export once and create/save your import Spec)
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37813142
Or do the export via custom code...

Dim rst As DAO.Recordset
Set rst = CurrentDb().OpenRecordset("SELECT Field1, Field2 FROM TourTable") 'Continue the Field sequence for all fields
    rst.MoveFirst

    Open "c:\YourFolder\YourFile.txt" For Output As #1
        Do Until rst.EOF
            Print #1, rst!Field1 & "!" & rst!Field2   'Continue this sequence for all fileds
            rst.MoveNext
        Loop
    Close #1
   
    MsgBox "Done"

rst.Close
Set rst = Nothing
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37813151
Ooops!

In my post you will have to change the delimiter from "!" to "|"

;-)

JeffCoachman
0
 

Author Closing Comment

by:qbjgqbjg
ID: 37815903
Thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

914 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

22 Experts available now in Live!

Get 1:1 Help Now