Export Table Data

RobertMo

Do you want to quickly export all the data form a specific table (also from system tables) ? Here is a function ExportTableData
Parameters:

• liTableID (Integer): ID of Table to export Data
• lbWithFlowFields (Boolean): Calculate and export also FlowFields
• lbWriteFieldID (Boolean): Write Header Row with Field IDs (Field No.)
• lbWriteFieldName (Boolean): Write Header Row with Field Names (Field Name)
• lbWriteFieldType (Boolean): Write Header Row with Field Types (Data Type)

RobertMo

Save as txt and import...

OBJECT Codeunit 77777 ExportTableData
{
  OBJECT-PROPERTIES
  {
    Date=08.05.04;
    Time=12:00:00;
    Version List=ROBI;
  }
  PROPERTIES
  {
    OnRun=BEGIN
          END;

  }
  CODE
  {

    PROCEDURE ExportTableData@1000000009(liTableID@1000000000 : Integer;lbWithFlowFields@1000000014 : Boolean;lbWriteFieldID@1000000013 : Boolean;lbWriteFieldName@1000000018 : Boolean;lbWriteFieldType@1000000019 : Boolean);
    VAR
      lrrRecRef@1000000002 : RecordRef;
      lfrFieldRef@1000000003 : FieldRef;
      lcuComDlg@1000000022 : Codeunit 412;
      d@1000000007 : Dialog;
      lfFile@1000000020 : File;
      lch9@1000000001 : Char;
      ltTable@1000000009 : Text[250];
      ltSuggestFileName@1000000008 : Text[250];
      ltFileName@1000000024 : Text[250];
      ltTextValue@1000000010 : Text[250];
      ltFieldClass@1000000021 : Text[30];
      liIndex@1000000004 : Integer;
      liStep@1000000006 : Integer;
      liProgress@1000000015 : Integer;
      liRecsExported@1000000005 : Integer;
      liRecCount@1000000017 : Integer;
      liFieldCount@1000000016 : Integer;
      ltc852@1000000012 : TextConst 'ENU=čćđšžČĆĐŠŽ;SLV=čđšžČĆĐŠŽ^[@Ţ…';
      ltc1250@1000000011 : TextConst 'ENU=ź†Đ秬ŹŃć¦;SLV=źĐ秬ŹŃ榬榺ş';
    BEGIN
      // ExportTableData
      //    Parameters: liTableID:        ID of Table to export Data
      //                lbWithFlowFields: Calculate and export also FlowFields (calculating may be slow)
      //                lbWriteFieldID:   Write Header Row with Field IDs (Field No.)
      //                lbWriteFieldName: Write Header Row with Field Names (Field Name)
      //                lbWriteFieldType: Write Header Row with Field Types (Data Type)

      // constants
      lch9 := 9;

      // open table
      lrrRecRef.OPEN(liTableID);
      ltTable := STRSUBSTNO('%1 (%2)', lrrRecRef.NAME, liTableID);
      liRecCount := lrrRecRef.COUNT;
      liFieldCount := lrrRecRef.FIELDCOUNT;
      liStep := ROUND(liRecCount / 100, 1);
      IF liStep = 0 THEN liStep := 1;

      // get filename
      ltSuggestFileName := 'TableData - ' + ltTable + '.txt';
      ltFileName :=lcuComDlg.OpenFile('Export TableData to...', ltSuggestFileName, 1, '', 1);
      IF ltFileName = ltSuggestFileName THEN ERROR('Export TableData was canceled.');
      lfFile.WRITEMODE(TRUE);
      lfFile.TEXTMODE(TRUE);
      lfFile.CREATE(ltFileName);

      // show dialog
      d.OPEN('Exporting table data\\'+
             'File   #1#############################\'+
             'Table  #2#############################\'+
             'Records#3#######      Fields #4#######\'+
             'Parameters:\'+
             '     With Flow Fields #6#####\'+
             '     Write Field ID   #7#####\'+
             '     Write Field Name #8#####\'+
             '     Write Field Type #9#####\\'+
             '@5@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@',

             ltFileName, ltTable, liRecCount, liFieldCount, liProgress,
             lbWithFlowFields, lbWriteFieldID, lbWriteFieldName, lbWriteFieldType);

      // write field ID
      IF lbWriteFieldID THEN BEGIN
        FOR liIndex := 1 TO liFieldCount DO BEGIN
          lfrFieldRef := lrrRecRef.FIELDINDEX(liIndex);
          ltFieldClass := FORMAT(lfrFieldRef.CLASS);
          IF (ltFieldClass = 'Normal') OR ((ltFieldClass = 'FlowField') AND lbWithFlowFields) THEN BEGIN
            IF NOT (FORMAT(lfrFieldRef.TYPE) IN ['Binary', 'BLOB']) THEN BEGIN
              IF liIndex > 1 THEN BEGIN
                lfFile.WRITE(lch9);
                lfFile.SEEK(lfFile.POS - 2);
              END;
              lfFile.WRITE(lfrFieldRef.NUMBER);
              lfFile.SEEK(lfFile.POS - 2);
            END;
          END;
        END;
        lfFile.WRITE('');
      END;

      // write field name
      IF lbWriteFieldName THEN BEGIN
        FOR liIndex := 1 TO liFieldCount DO BEGIN
          lfrFieldRef := lrrRecRef.FIELDINDEX(liIndex);
          ltFieldClass := FORMAT(lfrFieldRef.CLASS);
          IF (ltFieldClass = 'Normal') OR ((ltFieldClass = 'FlowField') AND lbWithFlowFields) THEN BEGIN
            IF NOT (FORMAT(lfrFieldRef.TYPE) IN ['Binary', 'BLOB']) THEN BEGIN
              IF liIndex > 1 THEN BEGIN
                lfFile.WRITE(lch9);
                lfFile.SEEK(lfFile.POS - 2);
              END;
              lfFile.WRITE(lfrFieldRef.NAME);
              lfFile.SEEK(lfFile.POS - 2);
            END;
          END;
        END;
        lfFile.WRITE('');
      END;

      // write field type
      IF lbWriteFieldType THEN BEGIN
        FOR liIndex := 1 TO liFieldCount DO BEGIN
          lfrFieldRef := lrrRecRef.FIELDINDEX(liIndex);
          ltFieldClass := FORMAT(lfrFieldRef.CLASS);
          IF (ltFieldClass = 'Normal') OR ((ltFieldClass = 'FlowField') AND lbWithFlowFields) THEN BEGIN
            IF NOT (FORMAT(lfrFieldRef.TYPE) IN ['Binary', 'BLOB']) THEN BEGIN
              IF liIndex > 1 THEN BEGIN
                lfFile.WRITE(lch9);
                lfFile.SEEK(lfFile.POS - 2);
              END;
              lfFile.WRITE(lfrFieldRef.TYPE);
              lfFile.SEEK(lfFile.POS - 2);
            END;
          END;
        END;
        lfFile.WRITE('');
      END;

      // write file data
      IF lrrRecRef.FIND('>') THEN REPEAT
        liRecsExported := liRecsExported + 1;
        IF (liRecsExported MOD liStep) = 0 THEN  BEGIN
          liProgress := ROUND(liRecsExported / liRecCount * 10000, 1);
          d.UPDATE(5, liProgress);
        END;
        // export data
        FOR liIndex := 1 TO liFieldCount DO BEGIN
          lfrFieldRef := lrrRecRef.FIELDINDEX(liIndex);
          ltFieldClass := FORMAT(lfrFieldRef.CLASS);
          IF (ltFieldClass = 'Normal') OR ((ltFieldClass = 'FlowField') AND lbWithFlowFields) THEN BEGIN
            IF NOT (FORMAT(lfrFieldRef.TYPE) IN ['Binary', 'BLOB']) THEN BEGIN
              IF ltFieldClass = 'FlowField' THEN lfrFieldRef.CALCFIELD;
              ltTextValue := FORMAT(lfrFieldRef.VALUE());
              ltTextValue := CONVERTSTR(ltTextValue, ltc1250, ltc852);
              IF liIndex > 1 THEN BEGIN
                lfFile.WRITE(lch9);
                lfFile.SEEK(lfFile.POS - 2);
              END;
              lfFile.WRITE(ltTextValue);
              lfFile.SEEK(lfFile.POS - 2);
            END;
          END;
        END;
        lfFile.WRITE('');
      UNTIL lrrRecRef.NEXT = 0;
      lrrRecRef.CLOSE;
      d.CLOSE;
      MESSAGE('Exporting TableData for table %1 completed sucessfully.\', ltTable);
      lfFile.CLOSE;
    END;

    BEGIN
    END.
  }
}

RobertMo

Now everything is packed into 2 forms ready to use. And even some more functionality (selecting fields and filters)

http://www.mibuso.com/dlinfo.asp?FileID=350