Tech Tip: Exporting a table to a CSV file

PRODUCT: 4D | VERSION: 13.0 | PLATFORM: Mac & Win

Published On: September 4, 2012

The command EXPORT DATA doesn't offer CSV as a possible file extension to export data to. It is easy eough to create your own CSV file using other 4D Commands. The following is a helper method that is used to export an entire table to a .csv file.

This helper method will skip over any BLOB and PICTURE fields.

C_POINTER($fieldPtr;$tablePtr;$1)
C_TEXT($docPath;$writeToDoc;$2)
C_TIME($fileRef)
C_INTEGER($numFields;$tableNm;$fieldType)

$tablePtr:=$1
$docPath:=$2

$tableNm:=Table($tablePtr)
$numFields:=Get last field number($tablePtr)

$fileRef:=Create document($docPath)

$writeToDoc:=""

ARRAY BOOLEAN($include;$numFields)

For ($i;1;$numFields)
   GET FIELD PROPERTIES($tableNm;$i;$fieldType)
   If ($fieldType=Is BLOB) | (($fieldType=Is Picture)
      $include{$i}:=False
   Else
      $include{$i}:=True
      $writeToDoc:=$writeToDoc+Field name($tableNm;$i)+","
   End if
End for
$writeToDoc:=Substring($writeToDoc;1;Length($writeToDoc)-1)
$writeToDoc:=$writeToDoc+Char(Carriage return)+Char(Line feed)
SEND PACKET($fileRef;$writeToDoc)
$writeToDoc:=""

ALL RECORDS($tablePtr->)

While (Not(End selection($tablePtr->)))
   For ($i;1;$numFields)
      $fieldPtr:=Field($tableNm;$i)
      If($include{$i})
      $writeToDoc:=$writeToDoc+String($fieldPtr->)+","
      End if
   End for
   $writeToDoc:=Substring($writeToDoc;1;Length($writeToDoc)-1)
   $writeToDoc:=$writeToDoc+Char(Carriage return)+Char(Line feed)
   SEND PACKET($fileRef;$writeToDoc)
   $writeToDoc:=""
   NEXT RECORD($tablePtr->)
End while

CLOSE DOCUMENT($fileRef)

To use this method pass a pointer to a table as the first parameter and the path to write the file to as the second parameter.

$table:=->[Cities]
$docPath:=Get 4D folder(Database Folder)+"export.csv"
CSV Export($table;$docPath)