Do we have a solution to read the CSV file through XMLport even if the data is having comma(,) ?

Rambabu_Billuri_5
Rambabu_Billuri_5 Member Posts: 6
in NAV Tips & Tricks
Hi,

I am facing issues while reading the CSV file through XMLport.

Example: I am reading the Vendor's data from CSV file, "Address" field has value 10 North, Lake Avenue as there is a comma(,) after 10 North, CSV file treating it as a field separator and the Address field just holds the value till comma and the remaining value Lake Avenue is moving to map with the next column and the remaining also moving a step ahead.

Unfortunately, the client is unable to provide the TXT file instead of CSV. He can provide the double quotes at both ends of the data if the data contains a comma(,). So he will provide the address as "10 North, Lake Avenue". I have tried with the double quotes as well but getting the same issue.

Quick responses should be appreciated.

Thanks in advance.

Best Answer

  • Rambabu_Billuri_5
    Rambabu_Billuri_5 Member Posts: 6
    Answer ✓
    Thanks for your quick response @BertVerb. I already tried setting FieldDelimiter to " in XMLport, but it is not working.

    Yes @JJMc, you are correct. "CSV Buffer" table is an easier way but I thought to use XMLport as it is giving high performance compared to "CSV Buffer".

    Even with "CSV Buffer" also the data from CSV file is coming in the same format.

    So I have implemented a business logic to handle that issue. You can add your thoughts on the below solution.
    local procedure ImportFromStream(Var FileInstream: InStream; FileName: text): Boolean
    var
        VendorBuffer: Record "Payee Buffer VEL";
        TempCSVBuffer: Record "CSV Buffer" temporary;
        // Payeesxmlport: XmlPort "Payees Import VEL";
        FName: text;
        CurrFieldValue: Text[250];
        StringValue: Text[250];
        FieldNo: Integer;
        PrevLineNo: Integer;
        DataComplete: Boolean;
        CommaInData: Boolean;
        MoveData: Boolean;
    begin
        Clear(FName);
        FName := FileName;
        Commit();
        Clear(PrevLineNo);
        TempCSVBuffer.LoadDataFromStream(FileInstream, ',');
        if TempCSVBuffer.FindSet() then
            repeat
                if not (TempCSVBuffer."Line No." = 1) then begin
                    CurrFieldValue := TempCSVBuffer.Value;
                    if CurrFieldValue.EndsWith('"') then
                        DataComplete := true;

                    if CurrFieldValue.StartsWith('"') then begin
                        StringValue := TempCSVBuffer.Value;
                        CommaInData := true;
                    end
                    else
                        if StringValue <> '' then
                            StringValue += ',' + TempCSVBuffer.Value
                        else
                            StringValue += TempCSVBuffer.Value;

                    if CommaInData and DataComplete then
                        StringValue := DelChr(StringValue, '<>', '"');

                    if not CommaInData then
                        MoveData := true
                    else
                        if DataComplete then
                            MoveData := true;

                    if MoveData then begin
                        if PrevLineNo <> TempCSVBuffer."Line No." then begin
                            PrevLineNo := TempCSVBuffer."Line No.";
                            FieldNo := 0;
                            Clear(VendorBuffer);
                        end;
                        FieldNo += 1;
                        MoveDataToBufferTable(VendorBuffer, StringValue, FieldNo);
                        StringValue := '';
                        CommaInData := false;
                        DataComplete := false;
                        MoveData := false;
                    end;
                end;
            until TempCSVBuffer.Next() = 0;
        // Payeesxmlport.SetSource(FileInstream);
        // Importsuccess := Payeesxmlport.Import();
    end;

    procedure MoveDataToBufferTable(var VendorBuffer: Record "Payee Buffer VEL"; StringValue: Text[250]; FieldNo: Integer)
    begin
        if FieldNo = 1 then
            VendorBuffer.Init();
        case FieldNo of
            1:
                VendorBuffer.Validate("No.", StringValue);
            2:
                VendorBuffer.Validate(Name, StringValue);
            3:
                VendorBuffer.Validate(Address, StringValue);
            4:
                VendorBuffer.Validate("Address 2", StringValue);
            5:
                VendorBuffer.Validate(City, StringValue);
            6:
                VendorBuffer.Validate(Contact, StringValue);
        end;
        if not VendorBuffer.Insert() then VendorBuffer.Modify();
    end;

    Thanks,

Answers

  • BertVerb
    BertVerb Member Posts: 24
    Do you have set the FieldDelimiter to " in the xml port? That should work.
  • JJMc
    JJMc Member Posts: 63
    Hi.

    Why don't you use CSV Buffer table? It's way easier.
  • Rambabu_Billuri_5
    Rambabu_Billuri_5 Member Posts: 6
    Answer ✓
    Thanks for your quick response @BertVerb. I already tried setting FieldDelimiter to " in XMLport, but it is not working.

    Yes @JJMc, you are correct. "CSV Buffer" table is an easier way but I thought to use XMLport as it is giving high performance compared to "CSV Buffer".

    Even with "CSV Buffer" also the data from CSV file is coming in the same format.

    So I have implemented a business logic to handle that issue. You can add your thoughts on the below solution.
    local procedure ImportFromStream(Var FileInstream: InStream; FileName: text): Boolean
    var
        VendorBuffer: Record "Payee Buffer VEL";
        TempCSVBuffer: Record "CSV Buffer" temporary;
        // Payeesxmlport: XmlPort "Payees Import VEL";
        FName: text;
        CurrFieldValue: Text[250];
        StringValue: Text[250];
        FieldNo: Integer;
        PrevLineNo: Integer;
        DataComplete: Boolean;
        CommaInData: Boolean;
        MoveData: Boolean;
    begin
        Clear(FName);
        FName := FileName;
        Commit();
        Clear(PrevLineNo);
        TempCSVBuffer.LoadDataFromStream(FileInstream, ',');
        if TempCSVBuffer.FindSet() then
            repeat
                if not (TempCSVBuffer."Line No." = 1) then begin
                    CurrFieldValue := TempCSVBuffer.Value;
                    if CurrFieldValue.EndsWith('"') then
                        DataComplete := true;

                    if CurrFieldValue.StartsWith('"') then begin
                        StringValue := TempCSVBuffer.Value;
                        CommaInData := true;
                    end
                    else
                        if StringValue <> '' then
                            StringValue += ',' + TempCSVBuffer.Value
                        else
                            StringValue += TempCSVBuffer.Value;

                    if CommaInData and DataComplete then
                        StringValue := DelChr(StringValue, '<>', '"');

                    if not CommaInData then
                        MoveData := true
                    else
                        if DataComplete then
                            MoveData := true;

                    if MoveData then begin
                        if PrevLineNo <> TempCSVBuffer."Line No." then begin
                            PrevLineNo := TempCSVBuffer."Line No.";
                            FieldNo := 0;
                            Clear(VendorBuffer);
                        end;
                        FieldNo += 1;
                        MoveDataToBufferTable(VendorBuffer, StringValue, FieldNo);
                        StringValue := '';
                        CommaInData := false;
                        DataComplete := false;
                        MoveData := false;
                    end;
                end;
            until TempCSVBuffer.Next() = 0;
        // Payeesxmlport.SetSource(FileInstream);
        // Importsuccess := Payeesxmlport.Import();
    end;

    procedure MoveDataToBufferTable(var VendorBuffer: Record "Payee Buffer VEL"; StringValue: Text[250]; FieldNo: Integer)
    begin
        if FieldNo = 1 then
            VendorBuffer.Init();
        case FieldNo of
            1:
                VendorBuffer.Validate("No.", StringValue);
            2:
                VendorBuffer.Validate(Name, StringValue);
            3:
                VendorBuffer.Validate(Address, StringValue);
            4:
                VendorBuffer.Validate("Address 2", StringValue);
            5:
                VendorBuffer.Validate(City, StringValue);
            6:
                VendorBuffer.Validate(Contact, StringValue);
        end;
        if not VendorBuffer.Insert() then VendorBuffer.Modify();
    end;

    Thanks,

Categories