Export Table Data

RobertMoRobertMo Member Posts: 484
edited 2004-05-28 in NAV Tips & Tricks
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)
           ®obi           
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

Comments

  • RobertMoRobertMo Member Posts: 484
    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.
      }
    }
    
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • RobertMoRobertMo Member Posts: 484
    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
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Sign In or Register to comment.