DOCUMENT:Q277725  31-OCT-2000  [foxpro]
TITLE   :HOWTO: Copy Fields Containing NULL to Text File
PRODUCT :Microsoft FoxPro
PROD/VER::5.0,5.0a,6.0
OPER/SYS:
KEYWORDS:kbvfp500 kbvfp500a kbvfp600 kbXBase kbGrpDSFox kbDSupport kbCodeSnippet

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, versions 5.0, 5.0a, 6.0 
-------------------------------------------------------------------------------

SUMMARY
=======

The COPY TO command copies NULL fields as if they were empty. The reason for
this is that there is no way in ASCII to properly represent NULL. It is possible
that you will want to completely blank out fields (for example, "1,,3" instead
of "1,0,3") to represent nulls. If so, this code helps you do that.

MORE INFORMATION
================

This code represents one possible way to copy NULL fields to a text database.
Your method will depend on what the recipient is expecting; the code as written
here may not be suitable for your particular case.

The following code creates a test table.

   CREATE TABLE tblExport ;
      (cName C(10) NULL, ;
       nAge N(10, 2) NULL, ;
       lMarried L NULL, ;
       dAnniver D NULL, ;
       tBirth T NULL)
       
   INSERT INTO tblExport VALUES ("Record 1", 1, ;
      .T.,    {^2011/01/01}, .NULL.)
      
   INSERT INTO tblExport VALUES ("Record 2", 2, ;
      .F.,    .NULL.,        {^2002/02/02 02:02:02})
      
   INSERT INTO tblExport VALUES ("Record 3", 3, ;
      .NULL., {^2033/03/03}, {^2003/03/03 03:03:03})
      
   INSERT INTO tblExport VALUES ("Record 4", .NULL., ;
      .T.,    {^2044/04/04}, {^2004/04/04 04:04:04})
      
   INSERT INTO tblExport VALUES (.NULL., 5, ;
      .T.,    {^2055/05/05}, {^2005/05/05 05:05:05})

   *This is the actual conversion code.

   SET TEXTMERGE TO export.txt NOSHOW
   SET TEXTMERGE ON
    
   SCAN
      lcRecord = ''
      lnFieldCount = AFIELDS(laFields)
      llFirstField = .T.
      
      FOR i = 1 TO lnFieldCount
         lcFieldName = laFields(i, 1)
         lcFieldType = laFields(i, 2)
         lnFieldSize = laFields(i, 3)
         lnFieldDec  = laFields(i, 4)
         
         DO CASE
            CASE lcFieldType = "C"
               lcValue = '"' + RTRIM(EVAL(lcFieldName)) + '"'
            CASE lcFieldType = "N"
               lcValue = ;
                  LTRIM(STR(EVAL(lcFieldName), lnFieldSize, lnFieldDec))
            CASE lcFieldType = "L"
               IF ISNULL(EVAL(lcFieldName))
                  lcValue = .NULL.
               ELSE
                  lcValue = IIF(EVAL(lcFieldName), ".T.", ".F.")
               ENDif
            CASE lcFieldType = "D"
               lcValue = DTOC(EVAL(lcFieldName))
            CASE lcFieldType = "T"
               lcValue = TTOC(EVAL(lcFieldName))
         ENDcase
         lcValue = NVL(lcValue, '')
         
         IF EMPTY(lcRecord) AND llFirstField
            lcRecord = lcValue
         ELSE
            lcRecord = lcRecord + "," + lcValue
         ENDif
         
         llFirstField = .F.
      ENDfor

      *!* The following line does the actual output to file.
      \\<<lcRecord + CHR(13) + CHR(10)>>
   ENDscan

   SET TEXTMERGE TO

REFERENCES
==========

For additional information about COPY TO and NULL fields, click the article
number below to view the article in the Microsoft Knowledge Base:

   Q161054 FIX: COPY TO ... TYPE SDF Incorrectly Copies Null Fields

(c) Microsoft Corporation 2000, All Rights Reserved. Contributions by Garrett
Fitzgerald, Microsoft Corporation.


Additional query words:

======================================================================
Keywords          : kbvfp500 kbvfp500a kbvfp600 kbXBase kbGrpDSFox kbDSupport kbCodeSnippet 
Technology        : kbVFPsearch kbAudDeveloper kbVFP500 kbVFP600 kbVFP500a
Version           : :5.0,5.0a,6.0
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2000.