Codeunit 609 Data Archive Export to CSV
src/Apps/W1/DataArchive/App/src/DataArchiveExportToCSV.Codeunit.al170 lines, Copyright (c) Microsoft Corporation. MIT
// ------------------------------------------------------------------------------------------------// Copyright (c) Microsoft Corporation. All rights reserved. // Licensed under the MIT License. See License.txt in the project root for license information.// ------------------------------------------------------------------------------------------------/// <summary>/// Exports the contents one or more "Data Archive Table" records and pack them into a zip file for download./// </summary>namespace System.DataAdministration;using System.IO;using System.Utilities;codeunit 609 "Data Archive Export to CSV"{ Access = Internal; TableNo = "Data Archive Table"; trigger OnRun() begin ExportToCSV(Rec); end; var CsvSeparator: Text[1]; ArchiveEmptyErr: Label 'There are no archived records.'; GeneratingFileMsg: Label 'Creating file @1@@@'; DownloadLbl: Label 'Download'; NotAllTablesExportedMsg: Label 'You do not have the Read permission for some of the selected tables. Only the tables you have the Read permission for have been exported.'; NoFilesAddedMsg: Label 'No files were created.'; local procedure ExportToCSV(var DataArchiveTable: Record "Data Archive Table") var DataArchive: Record "Data Archive"; TempBlob: Codeunit "Temp Blob"; DataCompression: Codeunit "Data Compression"; Window: Dialog; InStr: InStream; OutStr: OutStream; FileName: Text; NoOfFilesAdded: Integer; NoOfDataRecords: Integer; NoOfTablesWithNoReadAccess: Integer; T0: Time; begin if DataArchiveTable.GetFilter("Data Archive Entry No.") = '' then DataArchiveTable.SetRange("Data Archive Entry No.", DataArchiveTable."Data Archive Entry No."); DataArchiveTable.SetFilter("No. of Records", '>0'); DataArchiveTable.SetAutoCalcFields("Table Name"); if not DataArchiveTable.FindSet() then Error(ArchiveEmptyErr); Window.Open(GeneratingFileMsg); T0 := Time; if StrPos(Format(1.1), ',') > 0 then CsvSeparator := ';' else CsvSeparator := ','; NoOfDataRecords := DataArchiveTable.Count(); DataCompression.CreateZipArchive(); repeat if DataArchiveTable.HasReadPermission() then begin if DataArchiveTable.Description = '' then DataArchiveTable.Description := DataArchiveTable."Table Name"; DataArchiveTable.Description := DelChr(DataArchiveTable.Description, '=', '/\'); Clear(TempBlob); TempBlob.CreateOutStream(OutStr); WriteToCsvStream(DataArchiveTable, OutStr); TempBlob.CreateInStream(InStr); DataCompression.AddEntry(InStr, DataArchiveTable.Description + '.csv'); NoOfFilesAdded += 1; if Time > T0 + 1000 then begin Window.Update(1, NoOfFilesAdded * 100 div NoOfDataRecords); T0 := Time; end; end else NoOfTablesWithNoReadAccess += 1; until DataArchiveTable.Next() = 0; Window.Close(); if NoOfFilesAdded = 0 then begin Message(NoFilesAddedMsg); exit; end; TempBlob.CreateOutStream(OutStr); DataCompression.SaveZipArchive(OutStr); DataCompression.CloseZipArchive(); TempBlob.CreateInStream(InStr); if NoOfFilesAdded > 1 then begin DataArchive.Get(DataArchiveTable."Data Archive Entry No."); FileName := DataArchive.Description + '.zip'; end else FileName := DataArchiveTable.Description + '.zip'; DownloadFromStream(InStr, DownloadLbl, '', '*.zip', FileName); if NoOfTablesWithNoReadAccess > 0 then Message(NotAllTablesExportedMsg); end; procedure WriteToCsvStream(var DataArchiveTable: Record "Data Archive Table"; var CsvOutStr: Outstream) var TempBlob: Codeunit "Temp Blob"; SchemaJson: JsonArray; TableJson: JsonArray; RecordJson: JsonArray; FieldJson: JsonObject; JsonToken: JsonToken; InStr: InStream; OutStr: OutStream; QuoteChars: array[1000] of Boolean; QuoteTxt: Text[1]; FieldTypeAsText: Text; RecordNo: Integer; FieldIndex: Integer; FieldNo: Integer; Line: TextBuilder; CR: Text[1]; begin CR[1] := 10; // Schema - Headers in the sheet TempBlob.CreateOutStream(OutStr, TextEncoding::UTF8); DataArchiveTable."Table Fields (json)".ExportStream(OutStr); TempBlob.CreateInStream(InStr, TextEncoding::UTF8); SchemaJson.ReadFrom(InStr); for FieldIndex := 0 to SchemaJson.Count() - 1 do begin SchemaJson.Get(FieldIndex, JsonToken); FieldJson := JsonToken.AsObject(); FieldJson.Get('DataType', JsonToken); FieldTypeAsText := JsonToken.AsValue().AsText(); if FieldIndex < ArrayLen(QuoteChars) then QuoteChars[FieldIndex + 1] := FieldTypeAsText in ['Text', 'Code']; FieldJson.Get('FieldName', JsonToken); if FieldIndex = 0 then Line.Append('"' + JsonToken.AsValue().AsText() + '"') else Line.Append(CsvSeparator + '"' + JsonToken.AsValue().AsText() + '"'); end; CsvOutStr.WriteText(Line.ToText() + CR); // Data - Rows in the sheet Clear(TempBlob); TempBlob.CreateOutStream(OutStr, TextEncoding::UTF8); DataArchiveTable."Table Data (json)".ExportStream(OutStr); TempBlob.CreateInStream(InStr, TextEncoding::UTF8); TableJson.ReadFrom(InStr); for RecordNo := 0 to TableJson.Count() - 1 do begin Clear(Line); TableJson.Get(RecordNo, JsonToken); RecordJson := JsonToken.AsArray(); for FieldIndex := 0 to SchemaJson.Count() - 1 do begin SchemaJson.Get(FieldIndex, JsonToken); FieldJson := JsonToken.AsObject(); FieldJson.Get('FieldNumber', JsonToken); FieldNo := JsonToken.AsValue().AsInteger(); RecordJson.Get(FieldIndex, JsonToken); FieldJson := JsonToken.AsObject(); FieldJson.Get(Format(FieldNo), JsonToken); if QuoteChars[FieldIndex + 1] then QuoteTxt := '"' else QuoteTxt := ''; if FieldIndex = 0 then Line.Append(QuoteTxt + JsonToken.AsValue().AsText() + QuoteTxt) else Line.Append(CsvSeparator + QuoteTxt + JsonToken.AsValue().AsText() + QuoteTxt); end; CsvOutStr.WriteText(Line.ToText() + CR); end; end;}