Small and medium business | Business Central, N...

Payment journal export without double quote text qualifiers

(0) Share

Report

Posted on by Nick Webb

891

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:

Dynamics 365 Business Central

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Bilal Haider 412 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Bilal Haider 412 on at

Like (0)

Report

RE: Payment journal export without double quote text qualifiers

Missed assign image. Assign here

Was this reply helpful? Yes No
Suggested answer

Bilal Haider 412 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Nick Webb 891 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Nick Webb 891 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Manjul chauhan 5 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Nick Webb 891 on at

Like (0)

Report

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.

Was this reply helpful? Yes No