Table 1234 CSV Buffer

Base Application, System.IO

src/Layers/W1/BaseApp/System/DataExchange/CSVBuffer.Table.al497 lines, Copyright (c) Microsoft Corporation. MIT

namespace System.IO;using System;using System.Utilities;/// <summary>/// Table to store CSV (comma-separated values)./// </summary>table 1234 "CSV Buffer"{    Caption = 'CSV Buffer';    ReplicateData = false;    DataClassification = CustomerContent;    fields    {        field(1; "Line No."; Integer)        {            Caption = 'Line No.';            DataClassification = SystemMetadata;        }        field(2; "Field No."; Integer)        {            Caption = 'Field No.';            DataClassification = SystemMetadata;        }        field(3; Value; Text[250])        {            Caption = 'Value';            DataClassification = SystemMetadata;        }    }    keys    {        key(Key1; "Line No.", "Field No.")        {            Clustered = true;        }    }    fieldgroups    {    }    var        IndexDoesNotExistErr: Label 'The field in line %1 with index %2 does not exist. The data could not be retrieved.', Comment = '%1 = line no, %2 = index of the field';        CSVFile: DotNet File;        StreamReader: DotNet StreamReader;        Separator: Text[1];        CharactersToTrim: Text;    /// <summary>    /// Inserts an entry to the record.    /// </summary>    /// <param name="LineNo">The line number on which to insert the value.</param>    /// <param name="FieldNo">The field number (or position) on which to insert the value.</param>    /// <param name="FieldValue">The value to insert.</param>    procedure InsertEntry(LineNo: Integer; FieldNo: Integer; FieldValue: Text[250])    begin        Rec.Init();        Rec."Line No." := LineNo;        Rec."Field No." := FieldNo;        Rec.Value := FieldValue;        Rec.Insert();    end;    /// <summary>    /// Loads data from a file.    /// </summary>    /// <remark>    /// Reads the content of the file by lines and separate values using <paramref name="CSVFieldSeparator"/>.    /// All the characters in <paramref name="CSVCharactersToTrim"/> will be removed from the beginning and the end of the read values.    /// </remark>    /// <param name="CSVFileName">The name of the file from which to laod data.</param>    /// <param name="CSVFieldSeparator">The separator to use to split the values.</param>    /// <param name="CSVCharactersToTrim">Characters to trim from the beginning and the end of the read values.</param>    [Scope('OnPrem')]    procedure LoadData(CSVFileName: Text; CSVFieldSeparator: Text[1]; CSVCharactersToTrim: Text)    begin        InitializeReader(CSVFileName, CSVFieldSeparator, CSVCharactersToTrim);        ReadLines(0);        StreamReader.Close();    end;    /// <summary>    /// Loads data from a file.    /// </summary>    /// <remark>    /// Reads the content of the file by lines and separate values using <paramref name="CSVFieldSeparator"/>.    /// </remark>    /// <param name="CSVFileName">The name of the file from which to load data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    [Scope('OnPrem')]    procedure LoadData(CSVFileName: Text; CSVFieldSeparator: Text[1])    begin        LoadData(CSVFileName, CSVFieldSeparator, '');    end;    /// <summary>    /// Loads data from a stream.    /// </summary>    /// <remark>    /// Reads the content of the stream by lines and separate values using <paramref name="CSVFieldSeparator"/>.    /// All the characters in <paramref name="CSVCharactersToTrim"/> will be omitted from the read values.    /// </remark>    /// <param name="CSVInStream">The stream from which to laod data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    /// <param name="CSVCharactersToTrim">Characters to trim from the beginning and the end of the read values.</param>    procedure LoadDataFromStream(CSVInStream: InStream; CSVFieldSeparator: Text[1]; CSVCharactersToTrim: Text)    begin        InitializeReaderFromStream(CSVInStream, CSVFieldSeparator, CSVCharactersToTrim);        ReadLines(0);        StreamReader.Close();    end;    /// <summary>    /// Loads data from a stream.    /// </summary>    /// <remark>    /// Reads the content of the stream by lines and separate values using <paramref name="CSVFieldSeparator"/>.    /// </remark>    /// <param name="CSVInStream">The stream from which to laod data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    procedure LoadDataFromStream(CSVInStream: InStream; CSVFieldSeparator: Text[1])    begin        LoadDataFromStream(CSVInStream, CSVFieldSeparator, '');    end;    /// <summary>    /// Saves the data stored in the record to a file.    /// </summary>    /// <param name="CSVFileName">The name of the output file.</param>    /// <param name="CSVFieldSeparator">The character to use as separator.</param>    [Scope('OnPrem')]    procedure SaveData(CSVFileName: Text; CSVFieldSeparator: Text[1])    var        FileManagement: Codeunit "File Management";        FileMode: DotNet FileMode;        StreamWriter: DotNet StreamWriter;    begin        FileManagement.IsAllowedPath(CSVFileName, false);        StreamWriter := StreamWriter.StreamWriter(CSVFile.Open(CSVFileName, FileMode.Create));        WriteToStream(StreamWriter, CSVFieldSeparator);        StreamWriter.Close();    end;    /// <summary>    /// Saves the data stored in the record to a BLOB.    /// </summary>    /// <param name="TempBlob">The BLOB in which to save the data.</param>    /// <param name="CSVFieldSeparator">The character to use as separator.</param>    procedure SaveDataToBlob(var TempBlob: Codeunit "Temp Blob"; CSVFieldSeparator: Text[1])    var        CSVOutStream: OutStream;        StreamWriter: DotNet StreamWriter;    begin        TempBlob.CreateOutStream(CSVOutStream);        StreamWriter := StreamWriter.StreamWriter(CSVOutStream);        WriteToStream(StreamWriter, CSVFieldSeparator);        StreamWriter.Close();    end;    local procedure WriteToStream(var StreamWriter: DotNet StreamWriter; CSVFieldSeparator: Text[1])    var        NumberOfColumns: Integer;    begin        NumberOfColumns := GetNumberOfColumns();        if FindSet() then            repeat                StreamWriter.Write(Value);                if "Field No." < NumberOfColumns then                    StreamWriter.Write(CSVFieldSeparator)                else                    StreamWriter.WriteLine();            until Next() = 0;    end;    /// <summary>    /// Initializes the CSV buffer.    /// </summary>    /// <remarks>    /// No data is inserted into the buffer.    /// </remarks>    /// <param name="CSVFileName">The name of the file from which to read data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    [Scope('OnPrem')]    procedure InitializeReader(CSVFileName: Text; CSVFieldSeparator: Text[1])    begin        InitializeReader(CSVFileName, CSVFieldSeparator, '');    end;    /// <summary>    /// Initializes the CSV buffer.    /// </summary>    /// <remarks>    /// No data is inserted into the buffer.    /// </remarks>    /// <param name="CSVFileName">The name of the file from which to read data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    /// <param name="CSVCharactersToTrim">Characters to trim from the beginning and the end of the read values.</param>    [Scope('OnPrem')]    procedure InitializeReader(CSVFileName: Text; CSVFieldSeparator: Text[1]; CSVCharactersToTrim: Text)    var        FileManagement: Codeunit "File Management";        Encoding: DotNet Encoding;    begin        FileManagement.IsAllowedPath(CSVFileName, false);        StreamReader := StreamReader.StreamReader(CSVFile.OpenRead(CSVFileName), Encoding.GetEncoding(0));        Separator := CSVFieldSeparator;        CharactersToTrim := CSVCharactersToTrim;    end;    /// <summary>    /// Initializes the CSV buffer.    /// </summary>    /// <remarks>    /// No data is inserted into the buffer.    /// </remarks>    /// <param name="CSVFileName">The name of the file from which to read data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    /// <param name="CSVCharactersToTrim">Characters to trim from the beginning and the end of the read values.</param>    /// <param name="Encoding">The character encoding to use.</param>    [Scope('OnPrem')]    procedure InitializeReader(CSVFileName: Text; CSVFieldSeparator: Text[1]; CSVCharactersToTrim: Text; Encoding: DotNet Encoding)    var        FileManagement: Codeunit "File Management";    begin        FileManagement.IsAllowedPath(CSVFileName, false);        StreamReader := StreamReader.StreamReader(CSVFile.OpenRead(CSVFileName), Encoding);        Separator := CSVFieldSeparator;        CharactersToTrim := CSVCharactersToTrim;    end;    /// <summary>    /// Initializes the CSV buffer.    /// </summary>    /// <remarks>    /// No data is inserted into the buffer.    /// </remarks>    /// <param name="CSVInStream">The stream from which to read data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    /// <param name="CSVCharactersToTrim">Characters to trim from the beginning and the end of the read values.</param>    procedure InitializeReaderFromStream(CSVInStream: InStream; CSVFieldSeparator: Text[1]; CSVCharactersToTrim: Text)    begin        StreamReader := StreamReader.StreamReader(CSVInStream);        Separator := CSVFieldSeparator;        CharactersToTrim := CSVCharactersToTrim;    end;    /// <summary>    /// Initializes the CSV buffer.    /// </summary>    /// <remarks>    /// No data is inserted into the buffer.    /// </remarks>    /// <param name="CSVInStream">The stream from which to read data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    procedure InitializeReaderFromStream(CSVInStream: InStream; CSVFieldSeparator: Text[1])    begin        InitializeReaderFromStream(CSVInStream, CSVFieldSeparator, '');    end;    /// <summary>    /// Loads data from a stream.    /// </summary>    /// <param name="CSVInStream">Instream from which to read data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    /// <param name="EncodingCodePage">Encoding page from the file that was loaded in the stream. For more references you can see the Encoding.GetEncoding(EncodingCodePage) in C#.</param>    procedure LoadDataFromStream(CSVInStream: InStream; CSVFieldSeparator: Text[1]; EncodingCodePage: Integer)    begin        LoadDataFromStream(CSVInStream, CSVFieldSeparator, '', EncodingCodePage);    end;    /// <summary>    /// Loads data from a stream.    /// </summary>    /// <param name="CSVInStream">Instream from which to read data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    /// <param name="CSVCharactersToTrim">Characters to trim from the beginning and the end of the read values.</param>    /// <param name="EncodingCodePage">Encoding page from the file that was loaded in the stream. For more references you can see the Encoding.GetEncoding(EncodingCodePage) in C#.</param>    procedure LoadDataFromStream(CSVInStream: InStream; CSVFieldSeparator: Text[1]; CSVCharactersToTrim: Text; EncodingCodePage: Integer)    begin        InitializeReaderFromStream(CSVInStream, CSVFieldSeparator, CSVCharactersToTrim, EncodingCodePage);        ReadLines(0);        StreamReader.Close();    end;    /// <summary>    /// Initializes the CSV buffer.    /// </summary>    /// <param name="CSVInStream">The stream from which to read data.</param>    /// <param name="CSVFieldSeparator">The character to use to split the values.</param>    /// <param name="CSVCharactersToTrim">Characters to trim from the beginning and the end of the read values.</param>    /// <param name="EncodingCodePage">Encoding page from the file that was loaded in the stream. For more references you can see the Encoding.GetEncoding(EncodingCodePage) in C#.</param>    procedure InitializeReaderFromStream(CSVInStream: InStream; CSVFieldSeparator: Text[1]; CSVCharactersToTrim: Text; EncodingCodePage: Integer)    var        Encoding: DotNet Encoding;    begin        StreamReader := StreamReader.StreamReader(CSVInStream, Encoding.GetEncoding(EncodingCodePage));        Separator := CSVFieldSeparator;        CharactersToTrim := CSVCharactersToTrim;    end;    /// <summary>    /// Populated the CSV buffer with entries.    /// </summary>    /// <remarks>    /// The entries are read from the stream with which the CSV buffer was initialized.    /// </remarks>    /// <param name="NumberOfLines">The number of lines to read. If called with 0 or less, the function will read all of the data.</param>    /// <returns>True if there were any read lines; otherwise - false.</returns>    [Scope('OnPrem')]    procedure ReadLines(NumberOfLines: Integer): Boolean    var        String: DotNet String;        CurrentLineNo: Integer;        CurrentFieldNo: Integer;        CurrentIndex: Integer;        NextIndex: Integer;        Length: Integer;        StartQuoteIndex: Integer;        EndQuoteIndex: Integer;        QuoteTok: Label '"', Comment = 'Token for the a quote', Locked = true;    begin        if StreamReader.EndOfStream then            exit(false);        repeat            StartQuoteIndex := -1;            EndQuoteIndex := -1;            String := StreamReader.ReadLine();            CurrentLineNo += 1;            CurrentIndex := 0;            StartQuoteIndex := String.IndexOf(QuoteTok, CurrentIndex);            EndQuoteIndex := String.IndexOf(QuoteTok, StartQuoteIndex + 1);            repeat                CurrentFieldNo += 1;                Rec.Init();                Rec."Line No." := CurrentLineNo;                Rec."Field No." := CurrentFieldNo;                NextIndex := String.IndexOf(Separator, CurrentIndex);                if (EndQuoteIndex >= 0) and (EndQuoteIndex < CurrentIndex) then begin // Re-look for "                    StartQuoteIndex := String.IndexOf(QuoteTok, CurrentIndex);                    EndQuoteIndex := String.IndexOf(QuoteTok, StartQuoteIndex + 1);                end;                if (NextIndex > StartQuoteIndex) and (NextIndex < EndQuoteIndex) then // if seperator is inside opening and closing quote, then treat it as part of the string                    NextIndex := String.IndexOf(Separator, EndQuoteIndex + 1);                if NextIndex = -1 then                    Length := String.Length - CurrentIndex                else                    Length := NextIndex - CurrentIndex;                if Length > 250 then                    Length := 250;                Rec.Value := String.Substring(CurrentIndex, Length);                Rec.Value := DelChr(Rec.Value, '<>', CharactersToTrim);                CurrentIndex := NextIndex + 1;                Rec.Insert();            until NextIndex = -1;            CurrentFieldNo := 0;        until StreamReader.EndOfStream or (CurrentLineNo = NumberOfLines);        exit(true);    end;    /// <summary>    /// Resets the filters on the record.    /// </summary>    procedure ResetFilters()    begin        Rec.SetRange("Line No.");        Rec.SetRange("Field No.");        Rec.SetRange(Value);    end;    /// <summary>    /// Gets a value from the record.    /// </summary>    /// <param name="LineNo">The line number to identify the value.</param>    /// <param name="FieldNo">The field number (or position) to identify the value.</param>    /// <error>The field in line %1 with index %2 does not exist. The data could not be retrieved.</error>    /// <returns>The value stored on line <paramref name="LineNo"/> and field <paramref name="FieldNo"/>.</returns>    procedure GetValue(LineNo: Integer; FieldNo: Integer): Text[250]    var        TempCSVBuffer: Record "CSV Buffer" temporary;    begin        TempCSVBuffer.Copy(Rec, true);        if not TempCSVBuffer.Get(LineNo, FieldNo) then            Error(IndexDoesNotExistErr, LineNo, FieldNo);        exit(TempCSVBuffer.Value);    end;    /// <summary>    /// Gets all the lines that contain a specific value on a specific field/position.    /// </summary>    /// <param name="FilterFieldNo">The field number (or position) of the value.</param>    /// <param name="FilterValue">The value to filter on.</param>    /// <param name="TempResultCSVBuffer">Out parameter to store the result.</param>    procedure GetCSVLinesWhere(FilterFieldNo: Integer; FilterValue: Text; var TempResultCSVBuffer: Record "CSV Buffer" temporary)    var        TempCSVBuffer: Record "CSV Buffer" temporary;    begin        TempResultCSVBuffer.Reset();        TempResultCSVBuffer.DeleteAll();        TempCSVBuffer.Copy(Rec, true);        Rec.SetRange("Field No.", FilterFieldNo);        Rec.SetRange(Value, FilterValue);        if Rec.FindSet() then            repeat                TempCSVBuffer.SetRange("Line No.", "Line No.");                TempCSVBuffer.FindSet();                repeat                    TempResultCSVBuffer := TempCSVBuffer;                    TempResultCSVBuffer.Insert();                until TempCSVBuffer.Next() = 0;            until Rec.Next() = 0;        TempResultCSVBuffer.SetRange("Field No.", 1);    end;    /// <summary>    /// Gets the value on the current line with a specific field number (or position).    /// </summary>    /// <error>The field in line %1 with index %2 does not exist. The data could not be retrieved.</error>    /// <param name="FieldNo">The field number (or posistion) to identify the value.</param>    /// <returns>The value on the current line and field number <paramref name="FieldNo"/></returns>    procedure GetValueOfLineAt(FieldNo: Integer): Text[250]    var        TempCSVBuffer: Record "CSV Buffer" temporary;    begin        TempCSVBuffer.Copy(Rec, true);        if not TempCSVBuffer.Get("Line No.", FieldNo) then            Error(IndexDoesNotExistErr, "Line No.", FieldNo);        exit(TempCSVBuffer.Value);    end;    /// <summary>    /// Gets the value on the current line with a specific field number (or position).    /// </summary>    /// <error>The field in line %1 with index %2 does not exist. The data could not be retrieved.</error>    /// <error>unless AcceptNonExisting is set</error>    /// <param name="FieldNo">The field number (or posistion) to identify the value.</param>    /// <returns>The value on the current line and field number <paramref name="FieldNo"/></returns>    procedure GetValueOfLineAt(FieldNo: Integer; AcceptNonExisting: Boolean): Text[250]    var        TempCSVBuffer: Record "CSV Buffer" temporary;    begin        TempCSVBuffer.Copy(Rec, true);        if not TempCSVBuffer.Get("Line No.", FieldNo) then            if AcceptNonExisting then                exit('')            else                Error(IndexDoesNotExistErr, "Line No.", FieldNo);        exit(TempCSVBuffer.Value);    end;    /// <summary>    /// Gets the number of columns store in the record.    /// </summary>    /// <returns>The number of fields for every line.</returns>    procedure GetNumberOfColumns(): Integer    var        TempCSVBuffer: Record "CSV Buffer" temporary;    begin        TempCSVBuffer.Copy(Rec, true);        TempCSVBuffer.ResetFilters();        TempCSVBuffer.SetRange("Line No.", "Line No.");        if TempCSVBuffer.FindLast() then            exit(TempCSVBuffer."Field No.");        exit(0);    end;    /// <summary>    /// Gets the number of lines stored in the record.    /// </summary>    /// <returns>The number of lines stored in the record.</returns>    procedure GetNumberOfLines(): Integer    begin        if Rec.FindLast() then            exit(Rec."Line No.");        exit(0);    end;}