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

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_5Rambabu_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

  • BertVerbBertVerb Member Posts: 24
    Do you have set the FieldDelimiter to " in the xml port? That should work.
  • JJMcJJMc Member Posts: 52
    Hi.

    Why don't you use CSV Buffer table? It's way easier.
  • Rambabu_Billuri_5Rambabu_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,
Sign In or Register to comment.