Microsoft KB Archive/277725

= How To Copy Fields Containing NULL to Text File =

Article ID: 277725

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q277725



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, &quot;1,,3&quot; instead of &quot;1,0,3&quot;) 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 (&quot;Record 1&quot;, 1, ;  .T.,    {^2011/01/01}, .NULL.) INSERT INTO tblExport VALUES (&quot;Record 2&quot;, 2, ;  .F.,    .NULL.,        {^2002/02/02 02:02:02}) INSERT INTO tblExport VALUES (&quot;Record 3&quot;, 3, ;  .NULL., {^2033/03/03}, {^2003/03/03 03:03:03}) INSERT INTO tblExport VALUES (&quot;Record 4&quot;, .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 = &quot;C&quot; lcValue = '&quot;' + RTRIM(EVAL(lcFieldName)) + '&quot;' CASE lcFieldType = &quot;N&quot; lcValue = ; LTRIM(STR(EVAL(lcFieldName), lnFieldSize, lnFieldDec)) CASE lcFieldType = &quot;L&quot; IF ISNULL(EVAL(lcFieldName)) lcValue = .NULL. ELSE lcValue = IIF(EVAL(lcFieldName), &quot;.T.&quot;, &quot;.F.&quot;) ENDif CASE lcFieldType = &quot;D&quot; lcValue = DTOC(EVAL(lcFieldName)) CASE lcFieldType = &quot;T&quot; lcValue = TTOC(EVAL(lcFieldName)) ENDcase lcValue = NVL(lcValue, '') IF EMPTY(lcRecord) AND llFirstField lcRecord = lcValue ELSE lcRecord = lcRecord + &quot;,&quot; + lcValue ENDif llFirstField = .F.  ENDfor

*!* The following line does the actual output to file. \\<> ENDscan

SET TEXTMERGE TO

