by Akatsuka Seiji | April 12, 2024

Automatically Import Kintone Data into SQL Account Through CData Arc

Using CData Arc to integrate data 
from Kintone to SQL Account 
sam 
kintone 
arc 
Account

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.

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.

Converting Kintone data to an XML file and 
Output it in ZIP format to import automatically. 
saLN 
SQL Financial 
Accounting 
Text & XML 
Import V5 
SQL Account 
kintone 
API 
File 
Auto Import 
CData Arc 
Transform 
XML(ZIP) 
Load

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.

O'E p 」 一 」 工 gZEEE 9'O'Eh-IL! 
Ode 
52n9 u 一 2 一 
~ 山 一 ewno 「 
anle-A)P04SU!e-4U!e-LAl 
~ 山 009 use:) 
no Vu 一 2 一 
MOP 凹 00 1S9 、 LSS 凵 0 S asel-pund saps Ellddns 」 2E0 n 凵 19 Mall', p 山 
aseqo•nd 
B!lddns 
00 S 
SOPS 
9Z/EO/YZOZ 20 61-1 一 亡 0M 
冖 
98X 06L'6gS'OZOZ'S 」 aft

Let's check the sample data provided:

Customer Invoice Entry 
NO. 
Currency 
IV-00013 
Ⅳ -00003 
IV-00014 
Ⅳ -00021 
CS -00007 
Ⅳ -00002 
CS -00008 
IV-00032 
11.00 
Ⅳ -00034 
IV-00035 
IV-OOOOI 
CS -00010 
IV-00004 
Ⅳ -00006 
cs-oooog 
Ⅳ -00005 
IV-00058 
CS -00001 
IV-00007 
Ⅳ -00012 
CS-00002 
CS-00003 
Ⅳ -00008 
Ⅳ -00010 
IV-OOOII 
28 
2019 / 0 1 
2D19 / 0 3 
2019 / 0 4 
2019 / 0 5 
2019 / 01 / 12 
2D19 / 01 / 13 
2019 / 01 / 17 
2D19 / 01 / 19 
2019 / 01 / 20 
2D19 / 01 / 20 
2019 / 01 / 20 
2019 / 01 / 20 
2019 / 02 / 08 
2019 / 02 / 11 
2019 / 02 / 16 
2019 / 02 / 20 
2019 / 02 / 21 
2019 / 03 / 18 
2019 / 04 / 20 
2019 / 05 / 14 
2019 / 09 / 12 
2019 / 10 / 25 
2019 / 12 / 03 
2019 / 12 / 09 
2D19 / 12 / 17 
2019 / 12 / 17 
2019 / 12 / 18 
2D19 / 12 / 22 
ALPHA &BETA COMPUTER 
ABESTTELECOMMIJNICATION PTE LTD 
CASH SALES 
A'BESTTELECOMMI 」 NICATION PTE LTD 
ALPHA &3ETA COMPUTER 
ALPHA &BETA COMPUTER 
FAIJNG TECK WAI 
A'BESTTELECOMMI 」 NICATION PTE LTD 
FAIJNG TECK WAI 
ALPHA & BETA COMPIJTER 
FAIJNG TECK WAI 
AE ENTERPRISE SON 
FAIJNG TECK WAI 
1 丅 f SECURITY SON 
ALPHA &3ETA COMPUTER 
ALPHA &3ETA COMPUTER 
AE ENTERPRISE SON 
AE ENTERPRISE SON *D 
AE ENTERPRISE SON 3HD 
STARTRADING SON 
CASH SALES 
AE ENTERPRISE SON *D 
A'BEST TELECOMMUNICATION PTE LTD 
1 丅 f SECURITY SON 
ALPHA &BETA COMPIJTER 
AE ENTERPRISE SON *D 
ALPHA &3ETA COMPUTER 
ALPHA & BETA COMPIJTER 
D00 」 ment NO 
100.00 
333 00 
900.00 
1 , 000 
7 , 500.00 
100.00 
2 , 000 00 
16 , 000.00 
1 , 595.00 
9 , 380 00 
500.00 
3 , 98 00 
1 , 000 00 
1 , 500 • 
5 , 900.00 
1 , 595.00 
50.00 
90 , 353 50 
Outstanding 
100.00 
333 00 
5 , 225.00 
900.00 
100 , 00 
1 , 000 00 
7 , 500.00 
5.00 
11.00 
100.00 
2 , 000 00 
1 , 595.00 
4 , 33D 00 
360.00 
500.00 
2 , 988 00 
1 , 000.00 
5 , 900.00 
500.00 
2.50 
1 , 595.00 
50.00 
61 , 851.50 
Fast Entry 
| save , 
Can 匚 
Refresh 
payment History for Invoice No: 4 
Description 
<NO da to display>

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.

SQL Accounting Text & XML Import - Testing Company [Remark: 2020] 
Eile GL Customer Supplier Sales Purchase Stock 1001s aport XML 
Wi n dow 
Help 
Is an application which importing Transaction and Master file 
from Text or CSV or XML to SQL Financial Accounting. 
WARNING 
Please Backup Before Run 
this Application 
• 2024/03/26

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:

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.

Document Type 
Customer Credit Note 
Maintain Customer 
Customer Debit Note 
Customer Invoice 
Customer Payment 
GL Cash Book - Payment V 
Sales Credit Note 
Cash Sales 
Sales Invoice 
Stock Adiustment 
Maintain Stock Item 
Fast XML Import - Testing Compam,' [Remark: 2020] 
File TO open 
esktop\SQL Accounting\ExampIe-XMC zip 
SL CS 
SL CS 
SL CS 
SL CS 
SL CS 
AR DN 
SL CN 
AR CN 
ST AJ 
Details 
Date 
AR 
BizObiect 
Customer 
Status 
Action 
Action 
14 2014/08/10 
1 5 2015/08/05 
15 2018/08/18 
15 2017/04/25 
15 2017/04/25 
15 2017/05/18 
172018/01 
1B 2015/12/14 
18 2013/10/22 
20 2015/12/01 
21 
201 4/1 7 
21 
201 
21 
2017/04/25 
21 
2017/04/25 
21 
2017/04/25 
21 
2017/04/25 
21 
2017/04/25 
43 2013/10/22 
47 
Count = 21 
C: xtImp\T emp\SL_ 
C: xtImp\T emp\SL_ 
CSFOS-DocDisc300-C0001 
C: xtImp\T emp\SL_ 
C: xtImp\T emp\SL_ 
CSFOS2300-A0001xmI 
C: xtImp\T emp\SL_ 
C: xtImp\T emp\ST_ 
C: xtImp\T I Il xml 
30040002 
cs 
POS2 
POS-DocDisc 
DN-OOOIS 
CN-OOI 41 1 
POSI-PMI 
POSI-PM2 
POS2-PM1 
POS2-PM2 
POS2-PM3 
vcpv-00050

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

ælcltcl arc 
Connectors 
Q Search 
E Core 
RSS 
EDI 
DASHBOARD 
Flows 
FLOWS 
PROFILES 
o 
o 
o 
o 
o 
REPORTS 
ACTIVITY 
API 
o 
Workspace: 
sql_accounting 
o 
API 
Batch Create 
Batch Merge 
Batch Split 
Branch 
CData 
Copy 
CSV Map 
Email Receive 
Email Send 
Excel 
Flat File 
JSON 
Notify 
MET 
Database 
Other 
Sample Flows 
Kintonel 
Kintone 
e 
e 
example_XMLMap_AR_Custo... 
XML Map 
e 
e 
example_Script_Rename 
Script 
e 
e 
BatchCreate1 
Batch Create 
e 
e 
example_ZlP 
ZIP 
e 
e 
Filel

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:

•kintone 
kintone_import 
kintone_img-ort 
300-A0004 
CREDITTERM 
30 Days 
STATEMENTTYPE 
Demo 
CONTROLACCOUNT 
300-00 
ACENT 
CREDITUMIT 
CURRENCYCODE 
uSD 
. — 300.40004 
COMPANYNAME 
dlZNATURE 
OVERDUEUMIT 
OUTSTANDING 
ALLOWEXCEEDCREDITLIWT 
ADDPDCTOCRUMIT 
COMPANYNAME2 
STATUS 
REGISTERNO 
-ADDRESSI 
COMPANYCATECORY 
CSTNO 
PRICETAG 
REMARK 
NOTE 
ROWDATA 
DTLKEY 
CREATIONDATE 
20141 125 
TAXEXEMPTNO 
ATTACHMENTS 
_CODE 
300-A0004 
300-AC004 
AGINCON 
TAXEXPDATE 
_BRANCHNAME 
BILLING 
BILLING2 
ADDRESS2 
-ADDRESS3 
_ADORESS4 
MR ALF 
MR ALF 
_PHONEI 
03-0000-0000 
03-0000-0000

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.

ac.lata arc 
Connectors 
Q Search 
Core 
O 
API 
Batch Create 
O 
Batch Merge 
Batch split 
coata 
Copy 
csv 
CSV Map 
Email Receive 
Email Se 
Excel 
Flat File 
Form 
O JSON 
Notify 
DASHBOARD 
Flows 
FLOWS 
PROFILES 
o 
o 
REPORTS 
ACTIVITY 
Kintonel 
Kintone 
API 
Settings 
Automation 
Advanced 
Input 
example_XMLMap_AR_Custo... 
XML Map 
e 
e 
example_Script_Rename 
Script 
e 
e 
BatchCreate1 
Batch Create 
e 
Configuration 
Connector Id: 
Kintonel 
Connector Type: 
Kintone 
The Kintone connector allows you to integrate Kintone into your data flo 
Connector Description: 
Connection: 
Kintone 
Test Connection 
Action 
Action: 
Select Configuration 
Tables 
kintone_import 
+ Add 
Columns 
Column Name 
Recordld

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. 

 aclc1tC1 arc 
Connectors 
Q Search 
B Core 
DASHBOARD 
FLOWS 
PROFILES 
o 
o 
o 
REPORTS 
ACTIVITY 
Kintonel 
Kintone 
API 
Settings 
Automation 
Advanced 
Input 
0) 
API 
Batch Create 
Batch Merge 
Batch Split 
Branch 
CData 
CSV Map 
Email Receive 
Email Se 
Excel 
Flat File 
Form 
JSON 
Notify 
example_XMLMap_AR_Custo... 
XML Map 
Script 
BatchCreate1 
Batch Create 
Connector Settings 
Connector Id: 
example_XMLMap_AR_Customer3 
Connector Type: 
XML Map 
The XMLMap connector provides a way to transform XML data from one 
Connector Description: 
Source File: 
connector.//Kintone1 :mapping.xml 
Schema file 
Destination File: 
AR Customer.schema.xml 
Mapping Editor 
Kintone side 
Source + Expand 
TAX 
TAXEXEMPTNO 
TAXEXPDATE 
— Collapse 
y pe datetime

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.

cc-laten arc 
Connectors 
Q Search 
3 core 
DASHBOARD 
Flows 
FLOWS 
Script 
Script Name 
CompanyCode 
valid script 
1 :set at t .header :CompanyCode

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".

aclc1tC1 arc 
Connectors 
Q Search 
Core 
DASHBOARD 
Flows 
FLOWS 
PROFILES 
o 
o 
o 
REPORTS 
ACTIVITY 
Kintonel 
Kintone 
e 
e 
API 
Settings 
Automation 
Input 
Output 
o 
API 
Batch Create 
Batch Merge 
Batch Split 
Branch 
CData 
Copy 
CSV Map 
Email Receive 
Email Sen 
Excel 
File 
Form 
JSON 
Notify 
example_XMLMap_AR_Custo„. 
XML Map 
e 
e 
Script 
e 
BatchCreate1 
Batch Create 
Automation Settings 
Enable automation 
o 
C) Send 
O Scheduled Send 
Send Interval: 
Minute 
Minutes: 
Performance 
Max Workers: 
Max Files:

5. Output ZIP file to folder for auto import

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

cclclta arc 
Connectors 
Q Search 
B Core 
DASHBOARD 
FLOWS 
PROFILES 
o 
o 
o 
REPORTS 
ACTIVITY 
Kintonel 
Kintone 
e 
e 
API 
Settings 
Automation 
Advanced 
Input 
0) 
API 
Batch Create 
Batch Merge 
Batch Split 
Branch 
CData 
Copy 
CSV 
CSV Map 
Email Receive 
Email Se 
Flat File 
Form 
Notify 
Configuration 
Connector Id: 
example_ZIP 
Connector Type: 
Zip 
This connector will pack file in zip format. 
example_XMLMap_AR_Custo... 
XML Map 
e 
e 
Script 
e 
e 
BatchCreate1 
Batch Create 
e 
Connector Description: 
Operation: 
O Compress 
Archive Options 
Archive Format: 
Zip 
Compression Method: 
Deflate 
Compression Level: 
4 
Encryption 
o 
Decompress

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

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

ac.Jatc1 arc 
Connectors 
Q Search 
Core 
Excel 
\ Form 
DASHBOARD 
Flows 
FLOWS 
PROFILES 
o 
o 
o 
REPORTS 
ACTIVITY 
Kintonel 
Kintone 
API 
Settings 
Automation 
Advanced 
Input 
e 
API 
Batch Create 
Batch Merge 
Batch Split 
Branch 
coata 
Copy 
CSV Map 
Email Receive 
Emall 
File 
Flat File 
ISON 
Notify 
example_XMLMap_AR_Custo„. 
XML Map 
e 
e 
example_Script_Rename 
Script 
e 
e 
BatchCreate1 
Batch Create 
e 
Connector Settings 
Connector Id: 
Filel 
Connector Type: 
File 
The File connector allows for the transfer of files to and from a specified I 
Connector Description: 
Path: 
Receive 
File Mask: 
Delete Files: 
C) Delete files (after received) 
Authentication 
Usemame:

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!

SQL Account Enterprise Edition - Testing Company [2020] 
Eile Edit Mew GL Customer Supplier Sales eurchase Stock Production SST/GST Inquiry 1001s Window 
Maintain Customer 
- Maintain Customer - 
compamy: 300-40004 
code: 300-40004 
Help 
General 
Credit Control Note 
BILLING2 
2 br anches 
Area: 
Currency USD 
Tax 
Branch Name: 
Address: 
Coordinate: 
Atten ton : 
Credit Terms: 
BILLING 
MR ALF 
mail@test.com 
Directon 
Statement: Open Item 
Price Tag : 
Fax:

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.

Try CData Arc

Get a free trial of CData Arc and experience enterprise-grade B2B integration capabilities for yourself. 

Get a Free Trial