Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

access 2007 - export table as pipe delimited text file.

Posted on 2012-04-05
6
Medium Priority
?
1,653 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:Philippe 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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.
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.

810 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