Solved

access 2007 - export table as pipe delimited text file.

Posted on 2012-04-05
6
1,574 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

861 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