Import Data with the Command Line Tool

Contents

Overview

This article introduces how to import data from Kintone using cli-kintone (External link) .

The Kintone App used in this article is based on the Export Data with the Command Line Tool article. It is recommended to read that article before this current article.

Initial Setup

This section introduces the required Kintone Apps and settings.

Prepare Sample Apps

Use the Customer Database App and the Expense Report App made in the Export Data with the Command Line Tool article.

Set the API Token

Go to the App Settings, click API Token, and update the existing API Token created in the Export Data with the Command Line Tool - Generate API Token article. Make sure to check the View Records, Add Records, and Edit Records option, and take note of the API token, as it is needed in the later steps.

STEP 1. Add Records

Use the Customer Database App for step 1 to step 2.

Prepare Data as CSV

Prepare record data to be added to the Customer Database App as a CSV file. It is recommended to edit the exported file from the Customer Database App. Refer to the Export Data with the Command Line Tool article to export the file.

Example

1
2
3
"company_name","company_website","representative","department","job_title","email","telephone_number","Single_line_text_1","address","notes"
"Volkman LLC","https://volkmanllc.com","Ira",,"Manager","ira@volkman.com","11122223333",,"San Francisco, xxxxxxx, United States",
"Walker-Jenkins","https://walkerjenkins.com","Clerissa",,"Manager","clerissa@walkerjenkins.com","44455556666",,"California, xxxxxxx, United States",

The first row of the CSV file are headers. Each header should correlate to the field code of the value to be updated. The second and subsequent rows contain the data to be added. When adding records, make sure to fill the required fields. Delete the Record_number, Author, Created_datetime, Last_user, Updated_datetime, and contact_name columns since they cannot be added. Also keep the department field empty since it will be updated in the Update Records section described below.

Add Records

To add record data, use the record import command. In addition, specify the Kintone domain, App ID and API Token via the --base-url, --app and --api-token options, and the CSV file via the --file-path option.

  • --base-url: The Kintone subdomain name (ex: https://example.kintone.com (External link) )
  • --app: App ID (ex: 123)
  • --api-token: API token copied from Initial Setup
  • --file-path: The file path of the CSV file with information to add or update records.
1
2
3
cli-kintone.exe record import --base-url KINTONE_URL --app APP_ID ^
  --api-token API_TOKEN ^
  --file-path FILE_PATH

When the command is run, the number of records added will be output to the command prompt.

Example

1
2
3
4
5
6
cli-kintone.exe record import --base-url https://example.kintone.com --app 123 ^
  --api-token BNk5wfOufWtPSTc6miMBTEa0SEm5ZokIcJWSSYXL ^
  --file-path ./example.csv

[2024-01-24T07:53:15.584Z] INFO: Starting to import records...
[2024-01-24T07:53:16.144Z] INFO: Imported 2 records successfully

The result of this sample is as shown below.

STEP 2. Update Records

Prepare Data as CSV

Prepare record data to be updated as a CSV file. In this example, the department field will be updated.

example

1
2
3
"Record_number","department"
"3","Marketing"
"4","IT"

Update Records

To update record data, use the record import command. By passing the --update-key option with a valid field code, records can be updated.

  • --update-key: The field code of the field used as the key for updating.

Valid fields for the --update-key option are as follows:

  • Record Number
  • The following fields with "Prohibit duplicate values" enabled
    • Text
    • Number

The field specified in the --update-key option and values that match the columns in the CSV file will be updated. Non-matching values will be added as new records.

1
2
3
4
cli-kintone.exe record import --base-url KINTONE_URL --app APP_ID ^
  --api-token API_TOKEN ^
  --file-path FILE_PATH ^
  --update-key FIELD_CODE

When the command is run, the total number of added and updated records will be output in the command prompt.

Example

1
2
3
4
5
6
7
8
cli-kintone.exe record import --base-url https://example.kintone.com --app 123 ^
  --api-token BNk5wfOufWtPSTc6miMBTEa0SEm5ZokIcJWSSYXL ^
  --file-path ./example.csv ^
  --update-key "Record_number"

[2024-01-24T09:08:45.240Z] INFO: Preparing to import records...
[2024-01-24T09:08:46.364Z] INFO: Starting to import records...
[2024-01-24T09:08:46.942Z] INFO: Imported 2 records successfully

The result of this sample is as shown below.

information

When importing data with the Attachments field, specify the --attachments-dir option.
For more information, refer to the Download and Upload Attachments with the Command Line Tool article.

STEP 3. Import Table Data

Use the Expense Report App app for this section.

Prepare Data as CSV

Prepare record data to be added to the Expense Report App as a CSV file. It is recommended to edit the exported file from the Expense Report App. Use the --fields option and specify the field codes to Record_number, business_purpose, and Itemized_Expenses.

Example

1
2
cli-kintone.exe record export --base-url https://example.kintone.com --app 124 ^
  --api-token CNk3wfOufWtASDc6miMBTEa2SEm3ZokIcJPOSKJD --fields "Record_number,business_purpose,Itemized_Expenses" > table.csv

As stated in the Additional Notes on Exported Files (External link) article of the Kintone Help, table data from each record will be exported into multiple rows. Refer to the Export Table Data article for more details.

Edit the exported file as shown below:

  • Change the amount field of the record with Record_number 1 to any amount
  • Add 1 row to the record with Record_number 2
  • Add a new record with 2 rows of table data
1
2
3
4
5
6
7
8
9
*,"Record_number","business_purpose","Itemized_Expenses","expense_date","description","category","amount"
*,"1","Business trip","664","2024-01-10","Train","Transport","20"
,"1","Business trip","665","2024-01-10","Lunch","Food","10"
,"1","Business trip","666","2024-01-10",,"Conference Fee","100"
*,"2","Kintone Conference","670","2024-01-08","Airplane","Transport","200"
,"2","Kintone Conference","671","2024-01-08","Dinner","Food","50"
,"2","Kintone Conference","","2024-01-08","Hotel","Accommodation","200"
*,"","AI Conference","","2024-01-25","Train","Transport","100"
,"","AI Conference","","2024-01-25","Dinner","Food","25"

Rows with no Record_number will be added as new records.

Add and update records

Specify the update-key option as Record_number and update the records using the command below. When the command is run, the total number of added and updated records will be output in the command prompt.

Example

1
2
3
4
5
6
7
cli-kintone.exe record import --base-url https://example.kintone.com --app 124 ^
  --api-token CNk3wfOufWtASDc6miMBTEa2SEm3ZokIcJPOSKJD --file-path table.csv --update-key "Record_number"


[2024-01-25T05:24:58.971Z] INFO: Preparing to import records...
[2024-01-25T05:25:00.092Z] INFO: Starting to import records...
[2024-01-25T05:25:01.463Z] INFO: Imported 3 records successfully

Notes when updating table data of existing records

  • Row data that are not included in the CSV file will be deleted. If there are rows that do not need to be updated, keep them in the CSV file so that they will not be deleted.

  • When there are no updates to some fields in the table, do one of the following. If not, the values not listed in the CSV file will be empty.

    • Enter data for all fields in the table.
    • Specify the ID of the row. IDs of the rows can be found in the column with the field code of the Table. In this sample App, Itemized_Expenses is the ID. For example, when updating the amount field but not the others, use the following CSV:
    1
    2
    3
    4
    5
    
    *,"Record_number","Itemized_Expenses","amount"
    *,"1","664","20"
    ,"1","665","10"
    ,"1","666","150"