Add Records with the Command Line Tool

Contents

Overview

This article introduces how to use the Kintone Command Line Tool (cli-kintone) to add record data into a Kintone App.

This tutorial uses the Customer Database available in the Kintone Marketplace (External link).

Add Records

STEP 1: Prepare a CSV file

Prepare a CSV file that will be used to add record data to the Kintone App. It is not necessary to list every field in the App in the CSV file - the fields can be limited to those to add data to, as shown below:

1
2
3
4
"company_name","contact_name"
"Sample Ltd.","Emily"
"Cybozu Inc.","Kevin"
"Test Corporation","Heather"

Make sure to write the field code in the first row, and not the field name. Save the data as customers.csv.

STEP 2: Add records by specifying the CSV file

In addition to the authentication option, use the -f option to specify the file to import.

If customers.csv is saved in the same location as the cli-kintone executable file:

1
> cli-kintone.exe --import -a APPID -d FQDN -t APITOKEN -f customers.csv

If the file path of customers.csv is specified:

1
> cli-kintone.exe --import -a APPID -d FQDN -t APITOKEN -f c:\Users\Desktop\customers.csv

Screenshot: All 3 records in the CSV file have been added into the Kintone App.

If the file path of customers.csv is specified and imported from a specified line:

1
> cli-kintone.exe --import -a APPID -d FQDN -t APITOKEN -f c:\Users\Desktop\customers.csv -l 3

Screenshot: Due to specifying the import depth, 2 out of 3 records have been added in the Kintone app.

Add Records After Deleting Records

Add records after deleting all records

Use the -f and -D options to delete all records before adding new records. If no query is specified, all records in the App will be deleted before records are added.

1
> cli-kintone.exe --import ‐a APPID ‐d FQDN ‐t APITOKEN -D -f customers.csv

Add records after deleting filtered records

Specified records can be selected to be deleted before adding new records. To specify those records, filter them using the -q option. Refer to the Query string article for information on how to write queries. In the example below, the 5 latest records that have “Krispy” as the contact name are deleted before the new records are added:

1
> cli-kintone.exe --import ‐a APPID ‐d FQDN ‐t APITOKEN -q "contact_name like \"Krispy\" limit 5 offset 0" -D -f customers.csv

Add Records with Table Data

In this example, the Expense Report App available in the Kintone Marketplace (External link) is used.

STEP 1: Create a CSV file containing table data

To add records that include table data, add an * symbol in the first column of the row that contains the field codes. Also add the * symbol to each row that contains the first table data row of tables in each record. To avoid making mistakes, use a CSV file exported by cli-kintone as reference.

1
2
3
4
5
6
7
*,"expense_code","business_purpose","employee","expense_date","description","category","amount"
*,"11110","Test","Krispy","2017-06-30","Train","Transport","200"
*,"11111","Marketing Conference","Administrator","2017-07-01","Flight","Transport","1400"
,"11111","Marketing Conference","Administrator","2017-07-02","Conference","Conference Fee","600"
,"11111","Marketing Conference","Administrator","2017-07-01","Books","Books & Education","50"
*,"11112","Meeting","Krispy","2017-08-01","Flight","Transport","1200"
,"11112","Meeting","Krispy","2017-08-02","Conference","Conference Fee","600"

Save the contents above as tabledata.csv.

STEP 2: Add records by specifying the CSV file

Use the -f option to specify the CSV file to add records with table data.

1
> cli-kintone.exe --import ‐a APPID ‐d FQDN ‐t APITOKEN -f subtable.csv

The contents of the CSV file looks like the below when opened in Excel.

Screenshot: A correctly formatted CSV file opened in Excel.

These are the screenshots of the three records that have data added to them. The data in the colored rectangles above match the data in the colored rectangles below.

Screenshot: The three records that have been added to the Kintone app via the above CSV example.

CSV data for Check box and Multi-choice Fields

Check box fields and Multi-choice fields are fields that can hold multiple values. A carriage return must be used to separate the multiple values for these fields. If the data is viewed in Excel, the multiple values can be seen on several lines inside one cell. When editing on excel, use the Alt + Enter shortcut in a cell for a carriage return.

The same goes for the tables. If the table contains these fields, separate the multiple values within these fields with carriage returns.

Example CSV data

Screenshot: Two records in the example Kintone app.

The two records in the above image can be represented with the following CSV:

1
2
3
4
5
6
"Check_box","Multi_choice"
"check1
check2","sample1
sample2
sample3"
"check1","sample2"

In Excel, this looks like the below:

Screenshot: A correctly formatted CSV file opened in Excel.

Example CSV data for tables

Screenshot: Two records in a Kintone app with multiple check boxes and drop down menus.

The two records in the above images can be represented with the following CSV:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
*,Check_box,Multi_choice
*,"check1
check2","sample1
sample2
sample3"
,check2,sample2
,check1,"sample2
sample3"
,"check1
check2","sample1
sample3"
*,check1,"sample2
sample3"

In Excel, this looks like the below:

Screenshot: A correctly formatted CSV file opened in Excel.

Debugging Errors

If any errors occur, check the logs that are outputted into the console.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
> cli-kintone --import -a APPID -d FQDN -t APITOKEN -f sample.csv
[2018-01-01 10:00:00]: Start from lines: 1 - 100 => SUCCESS
[2018-01-01 10:00:00]: Start from lines: 101 - 200 => SUCCESS
[2018-01-01 10:00:00]: Start from lines: 201 - 300 => ERROR OCCURRED
ID:  yi8P6luHfvjQRJkDqWsb
Code:  CB_VA01
Status:  400 Bad Request
Message:  Missing or invalid input.
Errors detail:
  'records[0].Radio_Button.value': The value, "1", is not in options.

[2018-01-01 10:00:00]: PROCESS STOPPED!

INSERT ERROR.
For error details, please read the details above.
Lines 201 to 300 of the imported file contain errors. Please fix the errors on the file, and re-import it with the flag "-l 201"

The problem can also be identified by checking the requests on the Audit Log (External link) in the System Administration page.

Click the gear icon on the upper right menu of Kintone, and go to User & System Administration -> Audit Logs -> View & Download.

From the Audit Log list, it is possible to see what record operations were performed. Their success/failure as well as the details of each operation can be seen by clicking the i mark on the left side of the table.

Looking at this information allows the user to see if requests from cli-kintone are successfully reaching their Kintone environment. Use this information to find causes of any particular issues and debug them.

Screenshot: The Kintone Audit Log being accessed through the settings page.