Export Data with the Command Line Tool

Contents

Overview

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

This tutorial uses Kintone Apps that are available in the Kintone Marketplace (External link) .

Initial Setup

This section introduces the needed Kintone Apps and required settings. The Customer Database App and the Expense Report App will be created.

Create a Customer Database App

  1. First, create a new Kintone App. From the Kintone Marketplace, click Marketing, and choose Customer Database.

  2. Add at least two records of data.

Create an Expense Report App

  1. Create another Kintone App. From Kintone Marketplace, scroll down and choose Expense Report.

  2. Add at least two sample records with more than one expense details.

Generate an API Token

Follow the steps in the Generating API Tokens (External link) article to generate an API token for both apps. Make sure to check the View Records option, and take note of the API token, as it is needed in the later steps.

STEP 1. Export Record Data to the Console

Use the Customer Database App for step 1 to step 4.
To display records, use the record export command.
In addition, specify the Kintone domain, App ID and API Token via the --base-url, --app and --api-token options.

1
2
cli-kintone.exe record export --base-url KINTONE_URL --app APP_ID ^
  --api-token API_TOKEN
  • --base-url: The subdomain to retrieve records from
  • --app: The App ID to retrieve records from
  • --api-token: API token copied from Initial Setup

When the command is run, the record information will be output in a CSV format. The headers of the CSV will be the field codes specified within the App.

Example

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

# Resulting Output
"Record_number","company_name","company_website","representative","contact_name","department","job_title","email","telephone_number","Single_line_text_1","address","notes","Author","Created_datetime","Last_user","Updated_datetime"
"1","Kintone","https://kintone.dev/en/","jessica","Jessica","Marketing","Manager","jessica@kintone.com","08055550000","Sales","San Francisco, 44 Montgomery St 22nd Floor, United States",,"jessica","2024-01-09T06:32:00Z","jessica","2024-01-09T06:32:00Z"
"2","Kintone","https://kintone.dev/en/","albert","Albert","Marketing","Assistant","albert@kintone.com","08011112222","Sales","San Francisco, 44 Montgomery St 22nd Floor, United States",,"jessica","2024-01-10T08:16:00Z","jessica","2024-01-10T08:16:00Z"

STEP 2. Export to a CSV File

To export to a CSV file, use the redirect function (>) to pipe the output into a file. For example, the following command exports the data to "records.csv".

Example

1
2
3
# Outputting to a file named "records.csv"
cli-kintone.exe record export --base-url https://example.kintone.com --app 123 ^
  --api-token BNk5wfOufWtPSTc6miMBTEa0SEm5ZokIcJWSSYXL > records.csv
information

When the output directory is not specified, the file will be exported to the directory where the cli-kintone.exe file is located.

Specify the character encoding

By default, string data is exported in UTF-8. To export to a file with other character encoding, use the --encoding option. For example, the following command exports the data with Shift-JIS encoding.

Example

1
2
cli-kintone.exe record export --base-url https://example.kintone.com --app 123 ^
  --api-token BNk5wfOufWtPSTc6miMBTEa0SEm5ZokIcJWSSYXL --encoding sjis > records_sjis.csv

STEP 3. Specify the Fields to Export

To specify the fields to export, use the --fields option and specify the field codes. For example, the following command exports data only from the Company Name, Representative, and Email fields.

1
2
3
4
5
6
7
cli-kintone.exe record export --base-url https://example.kintone.com --app 123 ^
  --api-token BNk5wfOufWtPSTc6miMBTEa0SEm5ZokIcJWSSYXL --fields "company_name,representative,email"

# Resulting Output
"company_name","representative","email"
"Kintone","jessica","jessica@kintone.com"
"Kintone","albert","albert@kintone.com"

STEP 4. Export Data with Queries and Sorting

The --condition option and the --order-by option can be used to specify queries and the sort order.
cli-kintone queries require using escape characters. For example, when specifying the value Marketing for the department field, set the query as: --condition department=\"Marketing\". The escape character \ is used to escape the ".
For more information on queries, refer to the following article: How to Write cli-kintone Queries

1
2
3
cli-kintone.exe record export --base-url KINTONE_URL --app APP_ID ^
  --api-token API_TOKEN ^
  --condition "QUERY_STRING" --order-by "ORDER_STRING"
  • --condition option: a query for filtering data.
  • --order-by option: a query for ordering data.

Example

1
2
3
4
5
6
7
8
cli-kintone.exe record export --base-url https://example.kintone.com --app 123 ^
  --api-token BNk5wfOufWtPSTc6miMBTEa0SEm5ZokIcJWSSYXL ^
  --condition "department = \"Marketing\"" --order-by "Record_number asc"

# Resulting Output
"Record_number","company_name","company_website","representative","contact_name","department","job_title","email","telephone_number","Single_line_text_1","address","notes","Author","Created_datetime","Last_user","Updated_datetime"
"1","Kintone","https://kintone.dev/en/","jessica","Jessica","Marketing","Manager","jessica@kintone.com","08055550000","Sales","San Francisco, 44 Montgomery St 22nd Floor, United States",,"jessica","2024-01-09T06:32:00Z","jessica","2024-01-09T06:32:00Z"
"2","Kintone","https://kintone.dev/en/","albert","Albert","Marketing","Assistant","albert@kintone.com","08011112222","Sales","San Francisco, 44 Montgomery St 22nd Floor, United States",,"jessica","2024-01-10T08:16:00Z","jessica","2024-01-10T08:16:00Z"

STEP 5. Export Table Data

Use the Expense Report App for this section.

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. In the file, each record is composed of a line with the * symbol in the first column and its subsequent lines without the * symbol.

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
cli-kintone.exe record export --base-url https://example.kintone.com --app 124 ^
  --api-token CNk3wfOufWtASDc6miMBTEa2SEm3ZokIcJPOSKJD

# Resulting Output
*,"Record_number","report_date","expense_code","start_date","end_date","business_purpose","employee","employee_id_number","job_title","department","approver","Itemized_Expenses","expense_date","description","category","amount","Total_Expenses","attachments","comments","Author","Created_datetime","Last_user","Updated_datetime"
*,"1","2024-01-11","12345",,,"Business trip","jessica","111","Manager","Marketing","jessica","664","2024-01-10","Train","Transport","50","125",,,"jessica","2024-01-11T05:18:00Z","jessica","2024-01-11T05:18:00Z"
,"1","2024-01-11","12345",,,"Business trip","jessica","111","Manager","Marketing","jessica","665","2024-01-10","Lunch","Food","25","125",,,"jessica","2024-01-11T05:18:00Z","jessica","2024-01-11T05:18:00Z"
,"1","2024-01-11","12345",,,"Business trip","jessica","111","Manager","Marketing","jessica","666","2024-01-10",,"Conference Fee","50","125",,,"jessica","2024-01-11T05:18:00Z","jessica","2024-01-11T05:18:00Z"
*,"2","2024-01-11","6789",,,"Kintone Conference","albert","789","Assistant","Marketing","jessica","670","2024-01-08","Airplane","Transport","200","250",,,"albert","2024-01-11T05:41:00Z","albert","2024-01-11T05:41:00Z"
,"2","2024-01-11","6789",,,"Kintone Conference","albert","789","Assistant","Marketing","jessica","671","2024-01-08","Dinner","Food","50","250",,,"albert","2024-01-11T05:41:00Z","albert","2024-01-11T05:41:00Z"

Use the below command in the command line to export the table data to a CSV file:

1
2
cli-kintone.exe record export --base-url https://example.kintone.com --app 124 ^
  --api-token CNk3wfOufWtASDc6miMBTEa2SEm3ZokIcJPOSKJD > records.csv

Below is how the exported result in the CSV file would look like in Excel:

The first row of the exported CSV file has an * symbol in the first column followed by a field code. The * symbol informs the user that table data exists in this exported CSV file.

Row 2 and 5 include an * symbol in the first column. This indicates that these rows, and rows below it that don't contain an * symbol, are data of 1 record of the App. The absence of the * symbol in some rows after the first indicates that they represent data from the second or later row inside the table of the record.