How can I validate Export a CSV File from my business app?

Exporting a CSV file and performing posting validations before using Microsoft Dynamics 365 Business Central involves several steps.

Here is a general guide on how you can achieve this:

Step 1: Prepare Your Data

Ensure that your data is structured correctly and meets the requirements of Business Central implementation Services. This may include organizing columns, cleaning data, and ensuring that the data types match the expected format.

Step 2: Export Data to CSV

Use your preferred method or tool to export the data to a CSV file. You can use software like Microsoft Excel, Google Sheets, or any other data processing tool. Ensure that the CSV file follows the required format for Business Central.

Step 3: Set Up Posting Validations

Define posting validations based on your business rules. These validations may include checking for mandatory fields, data types, and any other business-specific criteria that need to be met before data is posted to Business Central.

Step 4: Use Data Validation Tools

Before importing data into Business Central, consider using data validation tools or scripts to check the CSV file for errors. This can help identify issues in advance and prevent incorrect data from being posted.

Step 5: Import Data into Business Central

Navigate to the appropriate module in Business Central where you want to import the data. Use the Data Migration Wizard or the RapidStart Services to import the data from the CSV file.

Step 6: Validate Data During Import

Configure the import settings to enable data validation during the import process. This ensures that data is validated against the defined rules before being posted in Business Central.

Step 7: Review and Correct Errors

After the import, review the import log or any error messages generated during the process. Correct any issues identified during the validation process.

Step 8: Post Data in Business Central

Once the data has passed the validation process, proceed with posting the data in Business Central. This step is typically the final confirmation that the data is accurate and meets the required criteria.

Step 9: Monitor and Review

Regularly monitor and review data in Business Central to ensure ongoing accuracy. Implement proper controls and checks to maintain data integrity.

Important Considerations:

• Always perform these steps in a test environment before applying them to a production environment.
• Back up your data before performing any import or validation processes.
• Depending on the complexity of your business rules, you may need to involve your IT department or a Business Central consultant to assist with the setup and validation processes.
• Remember that the exact steps and tools may vary based on your specific version of Business Central and any customizations you have in place. Always refer to the official documentation and seek assistance from your IT team or a Business Central expert if needed.

This is a very important article that helps a lot to developers when the user wants data in a CSV file.

To achieve this CSV export, we will create a function “Export CSV ()” and this function will export the data in CSV and download it.

We will take the example of the Payment Journal.

In Payment Journal, we will create an action called “Export CSV File” and will call the function “Export CSV ()” here.

Code:

In Payment Journal Page Extension, added action “Export CSV File”:

 

actions
{
addafter(Reconcile)
{
action(ExportCSVFile)
{
Caption = 'Export CSV File';
ToolTip = 'Specifies Export CSV';
Image = Export;
ApplicationArea = all;
trigger OnAction()
begin
ExportCSV();
end;
} } }

 

Function added – “ExportCSV”

local procedure ExportCSV()
var
GenJnlLine: Record "Gen. Journal Line";
TempBlob: Codeunit "Temp Blob";
InS: InStream;
OutS: OutStream;
FileName, AmountText : Text;
TxtBuilder: TextBuilder;
FileNameLbl: Label 'TestCSV_%1.csv', Comment = '%1 = Todays Date';
HeaderLbl: Label 'Vendor No,External Document No,Document Date,Amount,Document No.';
MonthDayYearFormatLbl: Label '<Month,2><Day,2><Year4>';
AmountPrecisionLbl: Label '<Precision,2:2><Sign><Integer Thousand><Decimals>';
begin
FileName := StrSubstNo(FileNameLbl, Format(Today, 0, MonthDayYearFormatLbl));
TxtBuilder.AppendLine(HeaderLbl);
GenJnlLine.SetRange("Journal Template Name", Rec."Journal Template Name");
GenJnlLine.SetRange("Journal Batch Name", Rec."Journal Batch Name");
if GenJnlLine.FindSet() then begin
repeat
AmountText := Format(GenJnlLine.Amount, 0, AmountPrecisionLbl);
AmountText := DelChr(AmountText, '=', ',');
TxtBuilder.AppendLine(
GenJnlLine."Account No." + ',' +
GenJnlLine."External Document No." + ',' +
FORMAT(GenJnlLine."Document Date") + ',' +
AmountText + ',' +
GenJnlLine."Document No.");
until GenJnlLine.Next() = 0;
TempBlob.CreateOutStream(OutS);
OutS.WriteText(TxtBuilder.ToText());
TempBlob.CreateInStream(InS);
DownloadFromStream(InS, '', '', '', FileName);
end;
end;

 

Payment Journal – Action – Export CSV

CSV File generated:

Now what if we have errors in these journal entries? We do not capture that, and we export the file. Here we exported the file without checking any validation. Users might get errors while trying to post the entries.

That is why we need to check posting validations before we export the file so that the exported file comes out with genuine and correct data.

We will add another function called “CheckPostingValidation()” and we will call it before “ExportCSV()”

action(ExportCSVFile)
{
Caption = 'Export CSV File';
ToolTip = 'Specifies Export CSV';
Promoted = true;
PromotedIsBig = true;
Image = Export;
ApplicationArea = all;
trigger OnAction()
begin
CheckPostingValidations();
ExportCSV();
end;
}

local procedure CheckPostingValidations()
var
GenJnlLine: Record "Gen. Journal Line";
begin
GenJnlLine.Copy(Rec);
PreviewMode := true;
Code(GenJnlLine);
end;

In CheckPostingValidations, we will call Code Functions where we are checking the Posting Codeunit – “Gen. Jnl. -Post Batch”

Here, we are trying to check if any error exists so we need to set the Preview Mode as true so that we can check posting validations and the entries do not get posted.

Now, there’s a catch!

When we call Codeunit “Gen. Jnl. -Post Batch” and set the preview mode as true it will return the errors, we have the entries. However, if we do not have any errors in the journal entries it will still throw an error with “Preview mode.” So, the function we have for Export CSV won’t run because there will be an error thrown. So, we need to handle that.

Codeunit – 13 “Gen. Jnl.-Post Batch”

Procedure Code -> ProcessLines

ProcessLines ->

We can do some Error Handling here.

We can capture the error message and if it is not in “Preview mode.”, we can throw an error otherwise we will skip the error and export the correct file.

local procedure Code(var GenJnlLine: Record "Gen. Journal Line")
var
GenJnlPostBatch: Codeunit "Gen. Jnl.-Post Batch";
ErrorMessage: Text;
PreviewModeLbl: Label 'Preview mode.';
begin
GenJnlPostBatch.SetPreviewMode(PreviewMode);
if not GenJnlPostBatch.Run(GenJnlLine) then begin
ErrorMessage := GetLastErrorText();
if ErrorMessage <> PreviewModeLbl then
Error(ErrorMessage);
end;
end;

After Publishing the new changes:

I removed the Bal. Acc No so that I get the error Document is out of balance.

Error:

Now I add back Bal. Acc No.

Now I should be able to export the file without any error:

The file was downloaded without any error.

We could successfully check the posting validations and we could handle the error as well.