Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Payment journal export...
Small and medium business | Business Central, N...
Suggested answer

Payment journal export without double quote text qualifiers

(0) ShareShare
ReportReport
Posted on by Nick Webb 674

I've set-up a Data Exchange Definition to export Payment Journal records to a CSV file for payment upload into the Barclays Bank system. This is exporting OK, but the resultant file has double-quotes round every field as sample below shows, and the Barclays system rejects it.

"1200","Fabrikam, Inc.","GB29NWBK60161331926819","1.23","This is a test mes","99"
"1200","First Up Consultants","1200 100004","4,321.65","This is a longer t","99"

... what I need to achieve is the same but without the double-quote text-qualifiers, unless the field-value contains a comma (as below):-

1200,"Fabrikam, Inc.",GB29NWBK60161331926819,1.23,This is a test mes,99
1200,First Up Consultants,1200 100004,4321.65,This is a longer t,99

NOTE: I need the amount field formatted without the comma (e.g.) "4,321.65" to 4321.65

and the only value that is surrounded by quotes is "Fabrikam, Inc.".

My question is: can this be achieved with standard BC functionality or is a custom export (extension) required?

I've attached the exported Data Exchange Definition.

0882.Imp-_5F00_-Exp-Data-Exch-Def-_2600_-Map.xml

Categories:
Business Central
  • Nick Webb Profile Picture
    Nick Webb 674 on at
    RE: Payment journal export without double quote text qualifiers

    Here's a link to a tutorial video for creating a custom transformation rule.

    https://www.youtube.com/watch?v=L2qt0GKmiGk

    Then all you need is something like:

    PadLength := 8 // for example

    OutputText := InputText.PadLeft(PadLength, '0');

    Hope this helps.

  • Manjul chauhan Profile Picture
    Manjul chauhan 5 on at
    RE: Payment journal export without double quote text qualifiers

    Hi Nick,

    Can you possibly provide me with some information regarding the custom transformation for the leading "0"? I have the similar need to export payments in CSV.

    Thanks in advance.

  • Nick Webb Profile Picture
    Nick Webb 674 on at
    RE: Payment journal export without double quote text qualifiers

    I'm so close. I've started an extension and created a custom transformation rule called PADLEADINGZEROES. That's working fine.

    I also cloned XmlPort 1230 to 50000 and added the line:
    currXMLport.FieldDelimiter(''); as below:-

    trigger OnInitXmlPort()
    begin
       Window.Open(ProgressMsg);
       currXMLport.FieldDelimiter('');
    end;

    ... and assigned 50000 to the 'Reading/Writing XMLport' setting in the Data Exchange Definition.

    When run, this unconditionally removes all double quotes from the output. The problem is, I needed to keep the double quotes only if the value contained a comma, as with my "Fabrikam, Inc." example.

    Can this be achieved?

    If not, I'll have to assign the REMOVECOMMAS transformation rule to all columns. Which is a bit unsatisfactory.

  • Nick Webb Profile Picture
    Nick Webb 674 on at
    RE: Payment journal export without double quote text qualifiers

    First of all, thanks for your help. I've already implemented the REMOVECOMMA Transformation Rule for the Amount field. That's working nicely.

    Regarding the XmlPort. When you say copy the XmlPort, do you mean create an extension containing my own custom XmlPort? Or, can you do that in the Business Central GUI? If yes, how?

    Also, can you pad a field with leading zeroes using a Non-Custom Transformation Rule?

  • Suggested answer
    Bilal Haider Profile Picture
    Bilal Haider 45 on at
    RE: Payment journal export without double quote text qualifiers

    For removal of double quotes;
    I can see that you are using xmlport 1230 as ReadingWritingXMLport and that has no property for field delimeter so by default it is ". You can create a copy of this xmlport and then add field delimeter to None.

  • Suggested answer
    Bilal Haider Profile Picture
    Bilal Haider 45 on at
    RE: Payment journal export without double quote text qualifiers

    Missed assign image. Assign here

    pastedimage1677754823930v1.jpeg

  • Suggested answer
    Bilal Haider Profile Picture
    Bilal Haider 45 on at
    RE: Payment journal export without double quote text qualifiers

    Hi Nick,

    To change the formatting of amount field you can create a new transformation rule.  In that rule you remove ,. And then assign this transformational rule to the line definition for this column.

    pastedimage1677754697731v1.jpeg

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans