Solved

access 2007 - export table as pipe delimited text file.

Posted on 2012-04-05
6
1,554 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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