Automatically Import Kintone Data into SQL Account Through CData Arc

SQL Account is an accounting software provided by Apscom Solutions Pte Ltd. in Singapore for the Singaporean market, with over 270,000 companies using it, and it also holds a significant share in Malaysia.

In this article, we use CData Arc to import customer data managed by Kintone, which also has a presence in Malaysia, into SQL Account.

• Apscom Solutions Pte Ltd.
• SQL Account

Scenario summary

The automatic data import feature called "Auto Import," provided by SQL Account, is only supported in the network server edition. It is not available in the standalone edition, which is provided as a free trial.

For that reason, for this scenario, we will create import data from Kintone using CData Arc and output it to the "Auto Import" folder. The scope of this task extends to manually handling the portion labeled "Auto Import" on the far right in the diagram.

How to import data in SQL Account

We selected the import feature in XML format out of the following four patterns provided by SQL Account.

Note: An export feature for data is also provided, it seems to be unsupported for integration with external systems as it is intended for SQL Account backup purposes. See the manual of SQL Account for more details.

1. SDK Live

This is the "Bridge" direct live linking between External Program with SQL Accounting.

2. SQL Acc XLS n MDB Import 

This Is an External Shareware Program which able to import below data to SQL Accounting using Excel or CSV or TXT File.

*Microsoft Access Database Engine 2010 (AccessDatabaseEngine.exe - 32 bits) is required as a system requirement, so it has been excluded from the candidates for this time.

 3. SQL XML Import - XML File

This Is External Program which able to import master file and transactions to SQLAccounting in XML format.

*Auto Import function supported (only for Network Server Edition)

4. SQL Text Import - CSV/TXT File

This Is External Shareware Program which able to import master file and transactions to SQLAccounting.

*Auto Import function not supported 

Installing your SQL account

Note: SQL Account is supported only in Windows environment.

Once you apply for a free trial, you'll receive an email with the software download link. Execute the installer from the email and proceed with the installation. During the process, you'll be asked if you want to use sample data for testing purposes. Select "YES" for validation purposes.

You can also watch the installation process through a video. 

Once the installation is complete, an SQL Account icon will be added to our desktop. You can then launch the application to begin using it.

Let's check the sample data provided:

Install the data import tool "SQL Financial Accounting Text & XML Import V5"

Download the software labeled "SQL Import V5" under the "SQL Import Program Download" section and install it in the same environment as SQL Account. Once the installation is complete, an icon will be added on your desktop.

Let's launch the application and give it a try.

Manual import with XML files

First, refer to the following manual to manually import data and confirm the basic operation methods and sample data formats:

• SQL XML import manual
• Sample data

Overview of manual import

The data import process consists of the following two steps:

1. In the manual's "01. Click Folder Icon button to select the zip file which content of XML file," select the sample data "Example-XML.zip."
2. After importing the data, launch SQL Financial Account and open the module where the imported data should be located. Confirm that the data has been imported correctly.

Note: Support for auto import

If you want to automate the import process, enable the Auto Import feature following the instructions in the manual, and periodically execute the "SQL Financial Accounting Text & XML Import V5" program using Windows' built-in scheduling functionality.

Create a processing flow in CData Arc

Here is an overview of the flow to be created on the CData Arc side:

1. Prepare the CData Arc environment
2. Retrieve customer information from Kintone app created for this purpose using CData Arc
   *Note: A Kintone app has been prepared based on the sample data "AR_Customer.300-A0002.xml" to match the data structure of SQL Account. Please customize this as needed.
3. Create XML in a format importable to SQL Account
4. Compile the created XML data into a single ZIP file
5. Output the ZIP file to the Auto Import folder

1. Installation of CData Arc

CData Arc is available for a free trial, install it in a Windows environment with SQL Account installed.

2. Retrieve customer information from CData Arc Kintone app

The data structure of the app to be retrieved this time is as follows:

• App Name: kintone_import (corresponding to ROWDATA in the sample data)
• Subtable Name: kintone_import_table (corresponding to sdsBranch in the sample data)

Connect to Kintone with the CData Arc Kintone connector:

Connection to Kintone will be established using the Kintone connector provided as standard by CData Arc. 

The customer information app created on Kintone for this task has a parent-child relationship where the branch information in the subtable is associated with the basic information in the main table. You'll utilize the "Child Tables" feature available in CData Arc's data source connectors.

3. Create XML in a format that can be imported into SQL Account

The specifications for XML files importable to SQL Accounting are as follows:

3-1. For each record in the parent table, consolidate into one XML file

Subtable data can be registered as multiple child elements. 

3-2. Preparing XML File

Next, we create a schema file, such as AR_Customer.schema.xml, based on the sample data. Then, use an XML mapper to map the elements of the Kintone data with the XML file. 

Sample of the prepared schema file (AR_Customer.schema.xml):

...

The XML format contains information in attributes rather than values, presenting a unique structure. However, CData Arc handles it seamlessly.

Regarding the handling of multiple records in the subtable obtained using the "Child Tables" feature, in the XMLMap, since the terminal element (ROWsdsBranch) itself cannot be designated as a repeating (Mapping Loop) point, we have provided a virtual loop point and mapped it to the subtable side using a foreach loop.

3-3. Naming restriction

XML file names must be created according to the specified naming conventions, with slightly different rules for file names for each module. Details can be confirmed in the manual and sample data.

Example naming convention: BizObject.DocNo.CompanyCode.xml for AR, AP, SL & PH

We can refer to the manual and sample data for more information. Here is an example using sample records:

For AR_Customer.300-A0002.xml:

• BizObject = AR_Customer (Fixed as AR_Customer for data from Maintain Customer)
• DocNo = Omitted (There is no DocNo field in the Maintain Customer data)
• CompanyCode = 300-A0002 (Corresponding to the CODE field inside the ROW tag in the sample record)

To include the CompanyCode in the file name, we retrieve the value using XMLMap and place it into the message header. Then, we use a Script connector to rename the file and pass the information to subsequent connectors.

In addition to static names, the local file scheme property can accommodate dynamic date values such as filename.yyyyymmdd.

4. Consolidate all XML files into a single ZIP file

For the process of consolidating multiple files into a ZIP file, we utilize the automation feature of the BatchCreate connector to gather the data from XML files at regular intervals and pass them on to the ZIP connector.

For validation purposes, we have set the automatic execution interval to 1 minute, while leaving everything else as default. 

Upon passing the batch data to the ZIP connector, we successfully consolidated multiple XML files into a single ZIP file.

Since the name of the ZIP file is arbitrary, we have retained the format generated by the BatchCreate connector, which is in the form of "Batch-20240319101340204.zip".

5. Output ZIP file to folder for auto import

The ZIP connector is just connected, and the settings are left at default.

Finally, specify the path of the folder where you want to output the files using the File connector.

Path: C:\eStream\Utilities\SQLAccTxtImp\Pending

Note that when actually automating the import process, you'll need to follow the procedure outlined in this document. First, configure file upload settings in the network server version of SQL Account, then specify the schedule for execution using Windows Task Scheduler.

With the completion of the necessary processes for data integration from the Kintone app to SQL Account, you can now proceed with manual import using SQL Financial Accounting Text & XML Import V5.

You can confirm the import was successful on the SQL Account side as well!

CData Arc excels in creating XML files and consolidating them into ZIP files according to the format specified by SQL Account. This process, which may appear complex at first glance, was implemented simply and efficiently.

Furthermore, while we installed all the software in the same environment for this demonstration, CData Arc also supports FTP/SFTP integration. This means it can accommodate scenarios where core system servers are distributed across different locations.

In this integration, we leverage the capabilities of CData Arc to perform the following processes: 

• Simple configuration to adapt to subtable structures
• Flexible handling of cases where values are assigned to XML attributes
• Consolidation of multiple files into a single ZIP file
• Compatibility with on-premises environments
• Handling of processes that separate files for each record to be registered

About CData Arc

CData Arc is a data integration tool that enables file transfer (MFT) and SaaS data integration, including Kintone, without the need for coding. It allows for the seamless connection of various data sources, including various file formats, databases, SaaS APIs, and data stored on-premises or in the cloud, using a no-code approach.