access 2007 - export table as pipe delimited text file.

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?
qbjgqbjgConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
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?
Philippe DamervalSenior Analyst ProgrammerCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
<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)
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jeffrey CoachmanMIS LiasonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Ooops!

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

;-)

JeffCoachman
qbjgqbjgConsultantAuthor Commented:
Thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.