You’re offline. This is a read only version of the page.
670
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
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.
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.
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.
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?
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.
Missed assign image. Assign here
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,134
Super User 2024 Season 2
#2
Martin Dráb
229,928
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (