• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1248
  • Last Modified:

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
0
RichNH
Asked:
RichNH
  • 3
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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)PresidentCommented:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now