Overview

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

Attention

As of October 31st 2023, support for cli-kintone v0 has ended. For the latest features and security support it is highly recommended to upgrade to cli-kintone v1.

For more information, refer to the End of Support Notice for cli-kintone v0.

For this article, a Kintone App with record data is needed. This article will use the Customer Database App that is available in the Kintone Marketplace , but feel free to use any App.

Update Records by Specifying the Record ID

STEP 1: Prepare CSV data of records containing $id columns

To update records, prepare a CSV that includes an $id column in the first column ($id is a shorthand way of expressing the record number field). Editing an exported CSV file would be the easiest way to prepare this file. Refer to the Export Record Data with the Command Line Tool (v0) article for how to output records to a CSV file. Fields that are not listed in the CSV will not be updated.

Below is an example CSV with data that updates records with ids 4, 5, and 6:

1
2
3
4

"$id","contact_name","company_name","Department"
"6","Emily","Sample Ltd.","Marketing"
"5","Kevin","Cybozu Inc.","Engineering"
"4","Heather","Test Corporation","Promotion"

Save the file as update_customers.csv.

STEP 2: Update the records

The records are updated by using the same command for adding records.

If customers.csv is saved in the same location as the executable file

1

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

When-specifying-the-file-path-of-customers.csv

1

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

Update Records by Specifying the Key Field

STEP 1: Configure settings for the key field

Other fields can be specified to be the key for updating the App's records instead of the record ID. The key field must have the Prohibit Duplicate Values option turned on.

In the App's settings, open the Field Settings for the Company Name field (field code company_name). Check the Prohibit Duplicate Values option.

STEP 2: Create a CSV containing * symbols in the key field name

For this CSV, do not include the $id column. Cli-kintone will determine which field is the key, by looking for field code names in the CSV that start with the * symbol.

The CSV file uses the company_name field code as the key. Include *company_name in the first row of the CSV file. The following is a CSV with updated names and new phone numbers.

1
2
3
4

"contact_name","*company_name","telephone_number"
"Emily","Sample Ltd.","1(234)567-8910"
"Kevin","Cybozu Inc.","2(345)678-9012"
"Heather","Test Corporation","3(456)789-0123"

Save it as update_customers_by_company.csv.

STEP 3: Update the records

Use the same command as before to update the records.

1

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

Update and Add Records at the Same Time

STEP 1: Prepare a CSV file

To update and add records at the same time, specify the record ID in the CSV file. Rows are written differently in the CSV files for records that will be added and records that will be updated.

For records that will be added, write "" in the $id column. For records that will be updated, enter the corresponding record numbers in the $id column. For field values that should not be changed, enter the value it originally has. Otherwise, enter a new value.

Here is an example CSV for adding and updating record data:

1
2
3
4
5

"$id","contact_name","company_name","job_title"
"","Mike","kintone Corp.","Director"
"6","Emily","Sample Ltd.","Manager"
"5","Kevin","Cybozu Inc.","Developer"
"4","Heather","Test Corporation","Chief"

Save it as update_and_post_customers_by_company.csv.

STEP 2: Update and add records

Run the same command used for adding and updating records:

1

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

Update Records after Deleting Records

STEP 1: Prepare a CSV file

Records in an App can be deleted before updating other records.

It is possible to use a CSV file that either updates by the record ID or by the key field. In this example, update_customers.csv that was used earlier in this tutorial will be used again.

STEP 2: Delete records and update other records

Update records after deleting other records by specifying the -D and -f options. In order to exclude records that will be updated from the scope of the deletion, specify a query using the -q option.

The update_customers.csv file only updates records with IDs 4, 5, and 6. The following example deletes the two latest records in the App that don't have these record IDs. Records with IDs 4, 5 and 6 are then updated.

1

> cli-kintone.exe --import -a APPID -d FQDN -t API TOKEN -D -q "$id not in (\"4\", \"5\", \"6\") limit 2 offset 0" -f update_customers.csv

Records can also be added in this process, as long as $id is used as the key field for the CSV.

Update Records with Table Data by Specifying the Record ID

Records that include table data can be updated in the same way by creating CSVs that contain table expressions. The Expense Report app from the Kintone Marketplace is used in the following examples.

STEP 1: Create a CSV File containing $id and table data

Add an "" symbol in the first column of the first row of the CSV. Also add the "" symbol in the first column of all rows containing the first line of table data for each record

The following CSV file represents the case where records with record IDs 1, 2 and 3 contain table data. The data inside expense_date will be updated for all records, and business_purpose will be updated for one record (record 1):

1
2
3
4
5
6
7

*,"$id","expense_code","business_purpose","expense_date","description","category","amount"
*,"3","11110","Test","2017-5-1","Train","Transport","200"
*,"2","11111","Marketing Conference","2017-5-1","Flight","Transport","1400"
,"2","11111","Marketing Conference","2017-5-1","Conference","Conference Fee","600"
,"2","11111","Marketing Conference","2017-5-1","Books","Books & Education","50"
*,"1","11112","Meeting with customer","2017-5-1","Flight","Transport","1200"
,"1","11112","Meeting with customer","2017-5-1","Conference","Conference Fee","600"

Save the file as update_subtable.csv.

STEP 2: Update the record

Update the records with the following command:

1

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

Update records with Table Data by Specifying the Key Field

STEP 1: Configure settings for the key field

Records that contain table data can also be updated by specifying a field in the App to become the key field. This field will also need the Prohibit Duplicate Values option to be turned on. In this example, turn this setting on for the Expense Code field (field code expense_code).

STEP 2: Create a CSV containing * symbols in the key field name

Add the * symbol next to the field code that will become the key. In this example, *expense_code will be used as the key in the CSV, and a line will be added to each table of each record.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

*,"*expense_code","business_purpose","employee","expense_date","description","category","amount"
*,"11110","Test","Krispy","2017-06-30","Train","Transport","200"
,"11110","Test","Krispy","2017-06-30","Train","Transport","80"
*,"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"
,"11111","Marketing Conference","Administrator","2017-07-01","Train","Transport","300"
*,"11112","Meeting","Krispy","2017-08-01","Flight","Transport","1200"
,"11112","Meeting","Krispy","2017-08-02","Conference","Conference Fee","600"
,"11112","Meeting","Krispy","2017-08-02","Books","Books & Education","200"

Save this file as update_subtable_by_title.csv.

STEP 3: Update the record

Run the following command to update the records:

1

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

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

These are the screenshots of the three records that have new rows of data in their tables. The data in the colored rectangles above match the data in the colored rectangles below.

Note

There are many ways to update records with cli-kintone. Data gets harder to prepare when stating which fields to update, and when data includes tables. Editing an exported csv file helps reduce CSV file formatting errors.