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

Answers

• Options

  BertVerb Member Posts: 24

  2022-02-16

  Do you have set the FieldDelimiter to " in the xml port? That should work.

  0
• Options

  JJMc Member Posts: 59

  2022-02-18

  Hi.
  
  Why don't you use CSV Buffer table? It's way easier.

  0
• Options

  Rambabu_Billuri_5 Member Posts: 6

  2022-02-18 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,

  0