CSV with Null Export Issue

[Bry12345]

I am trying to export a CSV file with data from a table. The purpose of this export is to produce a file in a format for uploading to a mainframe. Scattered throughout the table are fields whose values are NULL. The problem that I am having is that Access only delimits NULL fields in the .csv with commas, and only uses the double quotes as a text delimiter. For the mainframe, I need ALL fields to be enclosed in double quotes, even if null.

For example,

I get: "a","b",,"d",,"f",,
I need: "a","b","","d","","f","",

Any suggestions are appreciated.

- - - -

Bryan

 

[AlexCuse]

change your export source from table to query, and force the double quotes with IIfs.

For each field name select:

fieldnamex: IIf(fieldname is null, """", fieldname)

This will mess with your fieldnames though, as you will need to call it fieldnamex...

HTH,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[Bry12345]

Thanks. I'm actually exporting through a query, so this should work. However, I getting an 'operator missing' error in the SQL statement.

From a test database, checking for nulls in field 'three':

SELECT tblMain.one, tblMain.two, tblMain.three: IIF(three is null,"""",three)
FROM tblMain;

returns the 'operator missing' error.

TFTH!

- - - -

Bryan

 

[Bry12345]

Problem solved. AlexCuse put me on the right track. I didn't exactly use his solution, as I encountered an IIF syntax issue that I couldn't resolve quickly. (Another one of those 'emergency' projects!)

I forced the " " in the export by preceeding it with action queries:

- - -
Dim strSQL As String

'sample query
strSQL = "UPDATE tblMain " & _
"SET tblMain.one = ' ' " & _
"WHERE tblMain.one Is Null "

DoCmd.RunSQL strSQL
- - -

This replaced the nulls with spaces, which then exported surrounded by double quotes.

Worked like a charm.

Thanks, AlexCuse

- - - -

Bryan

 

[PHV]

For me, a simpler way is to use the Nz function in your exported query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886