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

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
PreviewOutput.png
ActualOutput.png
LVL 1
RichNHAsked:
Who is Participating?
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
A null is "value unknown", so it doesn't know what to do with it.

Base your export on a query and do:

MyCol:NZ([<fieldName>],"")

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

Jim.
0

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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Jim.
0
RichNHAuthor Commented:
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.
0
RichNHAuthor Commented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Jim.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.