How to Export and Import Journal Entry using XML via DI-API

By admin Last updated Feb 15, 2022

Introduction

Goals

Export a journal entry record in XML format thru DI-API
Format and simplify the XML file to make it valid format for DI-API
Import back the formatted XML file thru DI-API

Please note the codes here are simplified for better understanding especially for beginners.

Versions

SAP Business One 8.82 (8.82.072) PL: 09
SAPbobsCOM88.dll (8.82.072)
VB.NET 2013

Entire Source Code

For those who just wants the code and go, here is all you need. For your convenience, I commented almost every single lines.

Public Class Form1

‘=======================

‘ Export XML

‘=======================

Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click

‘// Company object is the primary DI API object

‘// that represents a single SAP B1 company database.

‘// This object enables you to connect to the company database

‘// and to create business objects to use with the company database.

Dim vCmp As New SAPbobsCOM.Company

‘// Set Company object properties

vCmp.UseTrusted = True

vCmp.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008

vCmp.Server = “xxxxxx”

vCmp.LicenseServer = “xxxxxx:30000″

vCmp.CompanyDB = “xxxxxx“

vCmp.UserName = “xxxxxx“

vCmp.Password = “xxxxxx“

‘// Sets a valid value of BoXmlExportTypes that specifies the types

‘// for exporting data from the database to XML format.

vCmp.XmlExportType = SAPbobsCOM.BoXmlExportTypes.xet_ExportImportMode

Try

‘// Connects to the SAP Business One company database.

vCmp.Connect()

‘// Call JournalEntries obj

Dim oJE As SAPbobsCOM.JournalEntries

‘// BoObjectTypes Enumeration specifies the object types.

oJE = vCmp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries)

‘// GetByKey method retrieves and sets the values

‘// of the object’s properties by the object’s absolute key

‘// from the Company database.

If oJE.GetByKey(116541) Then

oJE.SaveXML(“C: emp.xml”)

MsgBox(“OK!”)

End If

Catch ex As Exception

MsgBox(ex.Message)

Finally

vCmp.Disconnect()

vCmp = Nothing

End Try

End Sub

‘=======================

‘ Import XML

‘=======================

Private Sub btnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click

‘// Company object is the primary DI API object

‘// that represents a single SAP B1 company database.

‘// This object enables you to connect to the company database

‘// and to create business objects to use with the company database.

Dim vCmp As New SAPbobsCOM.Company

‘// Set Company object properties

vCmp.UseTrusted = True

vCmp.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008

vCmp.Server = “xxxxxx“

vCmp.LicenseServer = “xxxxxx:30000″

vCmp.CompanyDB = “xxxxxx“

vCmp.UserName = “xxxxxx“

vCmp.Password = “xxxxxx“

Try

‘// Connects to the SAP Business One company database.

vCmp.Connect()

‘// Call JournalEntries obj

Dim oJE As SAPbobsCOM.JournalEntries

‘// BoObjectTypes Enumeration specifies the object types.

oJE = vCmp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries)

‘// Browses XML formatted data and enables to update the data.

‘

‘// Parameters

‘// XmlFileStr: Specifies the XML file name or the XML content string

‘// depending on the value of the XMLAsString property.

‘// Index : Specifies the number of the object that you want

‘// to read from the XML data (starts from 0, default 0).

oJE.Browser.ReadXml(“C: emp.xml”, 0)

‘// The Add function adds a record to the object table

‘// in SAP B1 company database.

‘// If there is no errors while importing, the Add function returns 0

If oJE.Add() = 0 Then MsgBox(“OK!”)

Catch ex As Exception

MsgBox(ex.Message)

Finally

‘// Disconnects an active connection with the company database.

vCmp.Disconnect()

vCmp = Nothing

End Try

End Sub

End Class

Step-By-Step Guide

Add Reference

First of all, add SAP Business One DI API from the Reference Manager.

Set a Form

From the Form1, add two buttons named btnExport and btnImport. Double click the each button, so you can create events when btnExport and btnImport are pressed.

The code would looks like this:

Public Class Form1

Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click

End Sub

Private Sub btnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click

End Sub

End Class

Coding to Export XML

Company Object

The code below is the standard way to connect SAP B1 server. The example code below is for SQL Server using Windows Authentication. If your SQL Server is not using Windows Authentication, you need to “UseTrusted” property to “False”, and add “DbPassword” and “DbUserName” property.

Write the codes in “btnExport_Click” method:

‘// Company object is the primary DI API object

‘// that represents a single SAP B1 company database.

‘// This object enables you to connect to the company database

‘// and to create business objects to use with the company database.

Dim vCmp As New SAPbobsCOM.Company

‘// Set Company object properties

vCmp.UseTrusted = True

vCmp.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008

vCmp.Server = “xxxxxx”

vCmp.LicenseServer = “xxxxxx:30000″

vCmp.CompanyDB = “xxxxxx“

vCmp.UserName = “xxxxxx“

vCmp.Password = “xxxxxx“

XML Export Type

Within the property “XmlExportType“, you can select the XML exporting type. Choose “xet_ExportImportMode” in this example.

Write the code below after the previous codes:

‘// Sets a valid value of BoXmlExportTypes that specifies the types

‘// for exporting data from the database to XML format.

vCmp.XmlExportType = SAPbobsCOM.BoXmlExportTypes.xet_ExportImportMode

FYI: There are other types according to the SAP Business One SDK help file…

Member	Description
xet_AllNodes	Export to XML all fields (both read only and read/write fields) from the database. (XML files exported using this type cannot be read by the ReadXml method.)
xet_ValidNodesOnly	Export to XML only valid fields that support XML import (read/write fields only) from the database. (XML files exported using this type cannot be read by the ReadXml method.)
xet_NodesAsProperties	Export to XML all fields as properties from the database. (XML files exported using this type cannot be read by the ReadXml method.)
xet_ExportImportMode	Export to XML only valid fields that support XML import and export (read/write fields only that do not contain null values) from the database. (XML files exported using this type can be read by the ReadXml method.)

Connecting to the Company and Export XML

Next step will be that opening connection to the company database, export XML, return if there is errors, and close the connection. The steps would be:

Set Try-Catch-Finally (or Using) flow
Open connection to the company
Choose the journal entry number (found at “TransId” field at the OJDT table) that you want to export, using GetByKey method. In this example, I chose the number 116541
Export an XML file using “SaveXML” method
Return errors if any
Close the connection

Write the code below after the previous codes:

Try

‘// Connects to the SAP Business One company database.

vCmp.Connect()

‘// Call JournalEntries obj

Dim oJE As SAPbobsCOM.JournalEntries

‘// BoObjectTypes Enumeration specifies the object types.

oJE = vCmp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries)

‘// GetByKey method retrieves and sets the values

‘// of the object’s properties by the object’s absolute key

‘// from the Company database.

If oJE.GetByKey(116541) Then

oJE.SaveXML(“C: emp.xml”)

MsgBox(“OK!”)

End If

Catch ex As Exception

MsgBox(ex.Message)

Finally

vCmp.Disconnect()

vCmp = Nothing

End Try

Test the Code

Run the code and see if XML file is generated. In this example, you should see the file under the C drive (C: emp.xml)

Formatting XML File

Unfortunately, you cannot just importing the same XML file that you just exported. You have to eliminate unnecessary data. In this example, I will leave only mandatory data.

Make it for human

The exported XML looks so ugly as you see the screenshot. To format the XML file readable for human, you can do so from Visual Studio: Go to Edit –> Advanced –> Format Document.

If for some reasons, you cannot use Visual Studio, there are unbelievably nice people are providing you free online services, such as:

https://www.freeformatter.com/xml-formatter.html
https://www.webtoolkitonline.com/xml-formatter.html
https://xmltoolbox.appspot.com

Before: This does not look like for human…

How to Export and Import Journal Entry using XML via DI-API

After: Now the XML looks tidy!

How to Export and Import Journal Entry using XML via DI-API

Simplify the XML file

First of all, DI-API does not like the first part of XML, so you can delete this line.

<?xml version=“1.0“ encoding=“UTF-16“?>

The XML file is consist of two parts– header and lines. The header starts from tag and lines start from tag.

In header part, leave only date tags (ReferenceDate, TaxDate, and DueDate) and delete everything else. In this example, I willleave just tag to identify what was imported to check later. So the header would look like this.

<row>

<Memo>Imporrrrrt Test ???? </Memo>