Export Record Data with the Command Line Tool

Contents

Overview

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

For this tutorial, a Kintone App with record data is needed. Feel free to use any created App. This tutorial, uses the Customer Database App that is available in the Kintone Marketplace (External link) .

After adding the Customer Database App to the Kintone environment, add some record data to the App for cli-kintone to export. Also set field codes for the fields in the App. Exported data refer to fields by their field code rather than their field name.

Screenshot: The final layout of the example customer database Kintone App.

Next, use cli-kintone to export record data from the Kintone App. If cli-kintone is not already set up, refer to Set Up the Command Line Tool to set up the command line tool first.

The cli-kintone tutorials introduced here will be demoed on a Windows environment.

Use Username and Password to Display Data

In the following example, the domain name example.kintone.com, App ID 777, and login name Kelvin are used:

1
2
> cli-kintone.exe --export -a 777 -d example.kintone.com -u Kelvin
Password:

The user will be prompted to enter a password. After entering the password, all of the field data of the records in the App will be displayed in the command line in a CSV format as shown below. When entering the password, the input will not be displayed on the command line for security reasons.

1
2
3
4
5
6
"$id","$revision","business","contact_name","job_title","representative","notes","department","company_name","company_website","address","email","telephone_number"
"5","1","Collaborative Software & Services Leader","Mike","Director","Krispy","Sample","Marketing","Sample Corporation","https://www.kintone.com/","560 Mission Street, Ste 1335 San Francisco, CA 94105","Heather@sample.com","1(415)692-6546"
"4","1","Collaborative Software & Services Leader","Kevin","Director","Krispy","Sample","Engineering","Cybozu, Inc.","https://www.kintone.com/","560 Mission Street, Ste 1335 San Francisco, CA 94105","Kevin@sample.com","1(415)692-6546"
"3","1","Collaborative Software & Services Leader","Emily","Director","Krispy","Sample","Promotion","Sample Ltd.","https://www.kintone.com/","560 Mission Street, Ste 1335 San Francisco, CA 94105","Emily@sample.com","1(415)692-6546"
"2","1","Collaborative Software & Services Leader","Heather","Director","Krispy","Sample","Marketing","Test Corporation","https://www.kintone.com/","560 Mission Street, Ste 1335 San Francisco, CA 94105","Heather@sample.com","1(415)692-6546"
"1","1","Collaborative Software & Services Leader","John","Director","Krispy","Sample","Marketing","kintone Corporation","https://www.kintone.com/","560 Mission Street, Ste 1335 San Francisco, CA 94105","john@kintonesample.com","1(415)692-6546"

Notice that the field code is displayed on the first line of the CSV. The field name is not displayed.

Specify -o json at the end of the command to display the result in JSON format instead of the CSV format. JSON format data may be easier to use when using Kintone data with other services.

If at any time cli-kintone does not finish its process, the shortcut CTRL+C can be used to cancel the current process that is running.

Use an API Token to Display Data

It is possible to input and output data without entering a password by using an API token. Using an API token has the following benefits:

  • The authentication is unaffected by a password change of a user (unlike password authentication)
  • The scope of the API token is limited to one App
  • The scope of the actions the API token can take can be easily controlled

Refer to API Tokens for information on how to generate an API token for the current Kintone App. The permissions of the API Token need to be configured based on which actions will be taken with cli-kintone. All actions made with API token authentication are logged as actions run by the user name Administrator. Set the API token in the option with the -t option:

1
> cli-kintone.exe --export -a APPID -d FQDN -t APITOKEN

Export to a CSV File in UTF-16

To show that data can be exported in different encodings, this section will export data to a file instead of being displayed on the command line. API Token authentication will be used.

Kintone outputs string data in UTF-8 by default. To output data in other character codes, use the -e option. In this example, data will be exported to the file sample.csv in UTF-16 format.

If the directory to output the CSV file to is not specified, the file will be saved in the same directory as the executable file of the Command Line Tool. Due to this, an “Access denied” error may occur if cli-kintone is running directly from the C drive due to system administrator permissions. In this case, make sure to specify the path of the directory to output the file to.

To save the file in the directory where the executable file is located:

1
> cli-kintone.exe --export -a APPID -d FQDN -t APITOKEN -e utf-16 > sample.csv

To save the file by specifying a directory (saving on the desktop in this example):

1
> cli-kintone.exe --export -a APPID -d FQDN -t APITOKEN -e UTF-16 > C:\Users\Desktop\sample.csv

Check the directory to see if the files were exported.

Note that if the same action is performed using User authentication like in the earlier section , the password prompt will not come up. A CSV file is created though, with the string “Password” inside. This is because the redirection operator > (External link) is being used, that writes the command output to a file instead of the command line. Therefore, the string “Password” is exported into the file.

To export data into a file with User authentication, use the -p option together with the -u option to specify the password.

Specify the Fields to Export

The –c option makes it possible to specify the fields to export. In this example, $id (the record number), company name, and contact name are exported. Make sure to specify the field codes, and not the field names.

1
> cli-kintone.exe --export -a APPID -d FQDN -u LOGINNAME -c "$id,company_name,contact_name"

The results are as follows:

1
2
3
4
5
6
"$id","company_name","contact_name"
"5","Sample Corporation","Mike"
"4","Cybozu, Inc.","Kevin"
"3","Sample Ltd.","Emily"
"2","Test Corporation","Heather"
"1","kintone Corporation","John"

Specify Filter Conditions and Sort Order

The filter conditions and sort order can be specified by using the -q option. Refer to the Query string article for how to write conditional expressions. In this example, records with department name “Marketing” are exported in the ascending order of record numbers along with the $id (record id), company name, contact name and department. Escape sequences may need to be used in the query.

1
> cli-kintone.exe --export -a APPID -d FQDN -u LOGINNAME -c "$id,company_name,contact_name,department" -q "department = \"Marketing\" order by $id asc"

The results are as follows:

1
2
3
4
"$id","company_name","contact_name","department"
"1","kintone Corporation","John","Marketing"
"2","Test Corporation","Heather","Marketing"
"5","Sample Corporation","Mike","Marketing"

Export Table Data

The format in which records with tables are exported may be a little confusing at first. The following example uses the Expense Report App.

Screenshot: The final layout of the example expense report Kintone App

Add 3 records to the Expense Report App, and add a few lines to the Expense Details table of each record.

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

1
> cli-kintone.exe --export -a APPID -d FQDN -t APITOKEN -e UTF-16 > C:\Users\Desktop\sample.csv

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

1
2
3
4
5
6
7
*,"$id","$revision","report_date","start_date","Itemized_Expenses","expense_date","description","category","amount","employee","employee_id_number","department","comments","approver","end_date","Total_Expenses","attachments","expense_code","job_title","business_purpose"
*,"3","3","2017-05-08","2017-05-01","816","2017-05-18","Train","Transport","200","Krispy","1345","Marketing","Sample","Administrator","2017-05-05","200","receipt.png","982141","Marketing Director","Marketing Conference"
*,"2","11","2017-05-08","2017-05-01","713","2017-05-01","Flight","Transport","1400","Administrator","1300","Sales","Sample2","Krispy","2017-05-05","2050","","982141","Sales Director","Marketing Conference"
,"2","11","2017-05-08","2017-05-01","714","2017-05-02","Conference","Conference Fee","600","Administrator","1300","Sales","Sample2","Krispy","2017-05-05","2050","","982141","Sales Director","Marketing Conference"
,"2","11","2017-05-08","2017-05-01","813","2017-05-01","Books","Books & Education","50","Administrator","1300","Sales","Sample2","Krispy","2017-05-05","2050","","982141","Sales Director","Marketing Conference"
*,"1","4","2017-05-08","2017-05-01","597","2017-05-01","Flight","Transport","1200","Krispy","1345","Marketing","Sample","Administrator","2017-05-05","1800","","982141","Marketing Director","Marketing Conference"
,"1","4","2017-05-08","2017-05-01","598","2017-05-02","Conference","Conference Fee","600","Krispy","1345","Marketing","Sample","Administrator","2017-05-05","1800","","982141","Marketing Director","Marketing Conference"

Try opening the CSV file in Excel for a better look:

Screenshot: A CSV file opened in Excel, containing the exported data from the Kintone App.

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

Row 2, 3 and 6 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 of the rows after the first indicates that they represent data of the second or later row inside the table of the record.

In the above CSV data, see that three records were exported in total. Records with record ID of 1 and 2 include several lines of table data - record ID 1 has two lines of table data, and record ID 2 has three lines of table data. Record ID 3 also includes one line of table data.

Although the rows with the same record ID seem to have similar data inside, notice that the fields inside tables contain different values.

The following image shows what the table data looks like in each record of the App.

Screenshot: Examples of how the table data looks like in each record of the App.

tips
Note

Note: Try using the different options available on cli-kintone to see the various ways to export data.