MS Access export of pipe delimited text file dropping double quotes on null columns

Posted on 2014-08-12
Last Modified: 2014-08-12
I have a table in an Access 2010 DB which I am trying to export into a pipe delimited (|) file with text columns having double quotes around them.  When a text column is NULL the double quotes are not being generated in the exported file.   The PREVIEW shows them in the output but the final output file doesn't have them in columns which are NULL.   Anyone know how to force Access to include the double quotes in the actual output?

I have attached 2 screen shots of a portion of the actual output in a text editor and the previewed output in the Export wizard.  All columns shown are text columns.

Thanks,  Rich
Question by:RichNH
    LVL 56

    Accepted Solution

    A null is "value unknown", so it doesn't know what to do with it.

    Base your export on a query and do:


     so you get a zero length string.   You should get the quotes then.

    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    I'd also add in why bother with the quotes?   It's just extra characters anyway and might cause problems if any of the data has a quote character in it.

    That's the whole idea of using a vertical pipe for a delimiter; it's rare to find it in actual data.

    LVL 1

    Author Comment

    Because the client is anal?  He's located in a European country and they apparently have their own ways of doing things.  In any case, the customer is king and this is the way he wants it.  It's they way he set up his system to automatically import data.  I will try your suggestion.
    LVL 1

    Author Closing Comment

    Thanks Jim, there's ~100 columns in this table I'm trying to export so I may very well update all null data to empty strings.  This is a one shot deal and I'm just trying to get through it in as short a time as possible.  You solution worked and illustrated what the problem was.  thanks again.
    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    Well here's a chance to shine (maybe you've already tried), but I'd explain why it's a bad idea.

     Heck if they are going to insist on quotes, they might as well drop the vertical pipe and just use standard CSV.    It's a lot more readable and understood by default by just about anything.

     I'd also mention that it's costing them more to go that extra bit.  That should get them<g>.

     But I understand the customer part and I'd do the same.  If that's what they really want and are willing to pay for it then OK.  I just make sure I make the objections up front.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    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…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    737 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