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):

<?xml version="1.0" standalone="yes"?>
<DATAPACKET Version="2.0">
<METADATA>
<FIELDS>
<FIELD attrname="CODE" fieldtype="string" required="true" WIDTH="10"/>
<FIELD attrname="CONTROLACCOUNT" fieldtype="string" WIDTH="10"/>
...
<FIELD attrname="EMAIL" fieldtype="string" WIDTH="200"/>
</FIELDS>
<PARAMS/>
</FIELD>
</FIELDS>
<PARAMS/>
</METADATA>
<ROWDATA>
<ROW CODE="" CONTROLACCOUNT="" COMPANYNAME="" COMPANYNAME2="" COMPANYCATEGORY="" AREA="" AGENT="" BIZNATURE="" CREDITTERM="" CREDITLIMIT="" OVERDUELIMIT="" STATEMENTTYPE="" CURRENCYCODE="" OUTSTANDING="" ALLOWEXCEEDCREDITLIMIT="" ADDPDCTOCRLIMIT="" AGINGON="" STATUS="" PRICETAG="" CREATIONDATE="" TAX="" TAXEXEMPTNO="" TAXEXPDATE="" REGISTERNO="" GSTNO="" ATTACHMENTS="" REMARK="" NOTE="">
<sdsCreditControl/>
<sdsBranch>
<ROWsdsBranch DTLKEY="" CODE="" BRANCHTYPE="" BRANCHNAME="" ADDRESS1="" ADDRESS2="" ADDRESS3="" ADDRESS4="" ATTENTION="" PHONE1="" PHONE2="" FAX1="" FAX2="" EMAIL=""/>
</sdsBranch>
</ROW>
</ROWDATA>
</DATAPACKET>

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" BatchCreate1 Batch Create val (k intone _ i mport/CODE) ] " DI o API Batch Create Batch Merge Batch Split Branch CData Copy CSV CSV Map Email Receive Email Send Excel File Flat File Form JSON Notify ose o Mapping Editor Source + Expa nd TAX TAXEXEMPTNO TAXEXPDATE — Collapse @t Y pe datetime 0 I umn FRS Code O x Save O

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.

'"lairit3in Customer [Elapsed Time: Status Parent table Action Import Import Import CODE 30040004 30040005 30040006 COMPANYNAME Ok Ok Ok 30040004 30040005 AREA Information Record(s) successfully posted to database AGENT OK

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.